+ Reply to Thread
Results 1 to 12 of 12

Excel 2007 : rank results to also show text

  1. #1
    Registered User
    Join Date
    12-23-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    14

    Unhappy 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
    Last edited by feno1; 02-09-2011 at 04:22 AM. Reason: modified title slightly

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: create range to also show text

    Perhaps:

    Please Login or Register  to view this content.

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: rank results to also show text

    Perhaps try

    =IFERROR(RANK(J3,$J$3:$J$32,1),J3)
    Audere est facere

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: rank results to also show text

    @dll, perhaps: T(J3)

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: rank results to also show text

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

  6. #6
    Registered User
    Join Date
    12-23-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    14

    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. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #8
    Registered User
    Join Date
    12-23-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    14

    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?
    Attached Files Attached Files
    Last edited by feno1; 02-09-2011 at 03:47 AM.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #10
    Registered User
    Join Date
    12-23-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    14

    Smile 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. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: rank results to also show text

    see below

    Quote 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. #12
    Registered User
    Join Date
    12-23-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: rank results to also show text

    I see excellent, great tip, thanks also DLL for your advice

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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