+ Reply to Thread
Results 1 to 10 of 10

Work out 1st, 2nd, 3rd etc from a row of values

  1. #1
    Registered User
    Join Date
    10-14-2009
    Location
    Leeds
    MS-Off Ver
    Excel 2003
    Posts
    16

    Work out 1st, 2nd, 3rd etc from a row of values

    I have a row of values in D3:K3 want to work out which is ranked 1st, 2nd, 3rd etc?

    I can not work out how to use RANK as I am putting 1st place in L3, 2nd place in M3, 3rd place in N3. I have looked into Array formula's but just can't quite make it work.

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Work out 1st, 2nd, 3rd etc from a row of values

    why cant you use rank? you are not very clear or simply use small() or large()?
    Last edited by martindwilson; 06-15-2012 at 04:36 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    10-14-2009
    Location
    Leeds
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Work out 1st, 2nd, 3rd etc from a row of values

    Using RANK will simply rank the particular cell used in the formula from the row of values. However I do not want to select a particular cell to RANK I just want to find out the highest value from the row, then the next highest. Maybe there is another way to use RANK that I do not know of?

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Work out 1st, 2nd, 3rd etc from a row of values

    use large
    =large(D3:K3,1),=large(D3:K3,2),large(D3:K3,3)

  5. #5
    Registered User
    Join Date
    10-14-2009
    Location
    Leeds
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Work out 1st, 2nd, 3rd etc from a row of values

    Thanks perfect.

    Next stage of the problem is once I have the ranked values I want to find out which 'brand' they correspond to - these are in cells D2:K2. Instead of just showing the value worked out from the formula above I need to lookup which 'brand' that is.

    Picture1.jpg

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Work out 1st, 2nd, 3rd etc from a row of values

    ok before we go on you need to say what happens if rank/large are =

  7. #7
    Registered User
    Join Date
    10-14-2009
    Location
    Leeds
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Work out 1st, 2nd, 3rd etc from a row of values

    Hi Martin,
    Thanks for helping again - not sure what you mean in your last post? What further info do you need from me?

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Work out 1st, 2nd, 3rd etc from a row of values

    try like this but i needed a helper sheet
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-14-2009
    Location
    Leeds
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Work out 1st, 2nd, 3rd etc from a row of values

    Ok appreciate your work - this look brilliant.

    Can I ask: the A1 in =IF(COLUMN(A1) - does that need to be fixed or open like you have done? If I drag across and down A1 will soon become A3 and will start looking at other values?

    Thanks.

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Work out 1st, 2nd, 3rd etc from a row of values

    column(a1) just returns a number ie 1, column(b1)=2 but so does column(b12) or column(b2000).... so dragged across it just gets bigger by 1 dragging down has no effect

+ 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