# Excel 2007 : rank results to also show text

1. ## rank results to also show text

hi all,

so I am building a racing scoring chart, I need to range a set of cells and bring back the order of placings, but I also need to show the following when they appear
• "DNF" needs to read as "DNF"
• "DNS" needs to read as "DNS"
• when it is blank it will error so I have written an error formula to avoid this, I would like to write something else like an IF that vbrings back "" if it errors

The current formula I have works for everything but "DNS"
=IFERROR(IF(J3="DNF",J3,RANK(J3,\$J\$3:\$J\$32,1),"")
If someone could help with this i would be most grateful.

James

2. ## Re: create range to also show text

Perhaps:

``Please Login or Register  to view this content.``

3. ## Re: rank results to also show text

Perhaps try

=IFERROR(RANK(J3,\$J\$3:\$J\$32,1),J3)

4. ## Re: rank results to also show text

@dll, perhaps: T(J3)

5. ## Re: rank results to also show text

Yes, DO, good shout, that'll be better.......

6. ## Re: rank results to also show text

thanks all so I used =IFERROR(IF(OR(J3={"DNF","DNS"}),J3,RANK(J3,\$J\$3:\$J\$32,1)),"") and this worked, but on some of the ranking it misses out on the calculation, just stops at 3rd then leaves others blank? I have been over and check corresponding cells and they are identical to the ones that do rank?
wierd, its a pretty big spreadsheet so it would be hard to download onto here to show you

7. ## Re: rank results to also show text

We don't need the whole file - create another file which contains a relevant rank data set - ie one where application of the above formula to it generates incorrect results.

8. ## Re: rank results to also show text

okay I got rid of alot of the charts etc and slimmed it down to the basic file.
okay so the formula I am having problems with are in rows k,o,s,w on the "drivers" tab.

You can see that cells o26 and o10 amongst others do not want to rank for somne reason?

9. ## Re: rank results to also show text

I've only had a quick glance but shouldn't the rank in O be based on N (you're referencing J)

In short I think the issue stems from your use of absolute references when copying formulae... to correct:

``Please Login or Register  to view this content.``

10. ## Re: rank results to also show text

thanks Donkey OTE legend mate, the T formula worked well, what exactly does T do, just mean total of cell?

11. ## Re: rank results to also show text

see below

Originally Posted by MS Excel Help
T

Returns the text referred to by value.

Syntax
T(value)

Value Required. The value you want to test.

Remarks

If value is or refers to text, T returns value.
If value does not refer to text, T returns "" (empty text).
It is used here to ensure that a precedent Blank is returned as a Null rather than 0

12. ## Re: rank results to also show text

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1