In Exel 2007, how can I compare 15 values on the same row with each other and then enter a 1 for the cell with lowest value, 2 for the cell with second lowest value and so on?

In Excel 2007, how can I compare 15 values on the same row (row 9) with each other and then enter a 1 for the cell with lowest value, 2 for the cell with second lowest value and so on?

The first value to be compared is in cell ak9, the next in cell aq9, next in aw9 and so on (total number of values to be compared are 15).
The result for each compared cell should then be written out in the adjacent cell to the right of the compared cell.

For example:

AK9: Value 14.0 (always about this type of number, one decimal, around 07.00 to 37.00 maximum, no need to specify just to give an idea of type of figures in the cells).

AQ9: 15.3

AW9: 13.2

In above example, "1" should be written out in cell AX9, "2" in cell AL9, and "3" in cell AR9.

Finally, I would like this same VBA subroutine to be run also for rows 12, 15, 26, 31, 36, 41, 46, and 62 (but if more rows are inserted later, the subroutine should automatically be adjusted to reflect this insertion of new rows in the worksheet).

N.B.! For all rows except row 9 and 15, instead of lowest value here it should be highest value assigned a "1", second highest value assigned a "2", and so on.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You can use the RANK function in the result columns.  It tells the position of a value in a range of cells.
hermesalphaAuthor Commented:
What if the referenced cells don't follow each other consecutively (e.g. the first cell is AL9, the second AR9 and so on)? How do I separate them for the reference argument?

And how can I rank in "reverse" order (so the lowest value gets ranked highest)?
hermesalphaAuthor Commented:
Ok, found it now, just add "and" between.

But how to rank in reverse order?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

hermesalphaAuthor Commented:
No, "and" only worked for the first two values, then I don't seem to be able to use "and".
RANK is deprecated and only included for compatibility in version after excel 2007; if you are using 2007 the following still applies but you may not get the result you expect if some of the numbers are the same. Later versions offer rank.eq and to handle these situations.

create a named list for all the values in the sequence by selecting them individually whilst pressing ctrl then clicking formula, define name, and giving it a sensible name eg: 'listnum'.

In the cell where you want the ranking to appear use:

- the ak9 is the number to compare, the listnum is the set of numbers and 0 indicates a descending order (1 for ascending order).
hermesalphaAuthor Commented:
Ok, got it 0 for descending then. But isn't there any way to avoid external lists? And use only the available data from the scattered cells?
the parameter which holds the list is a range and the function only accepts a single range in that position (its called 'ref' in the hint text); the easiest way to  handle non-continuous ranges is with a defined name.

One alternative is to create a continuous range by pasting a link to each of the cells in a separate area of the sheet and then using that. Or you could define VBA function that brings the individual cells together with, for example range.areas  . both of which are more difficult than using the named range

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hermesalphaAuthor Commented:
I definitely would need a VBA for this because I have so many sheets full of data already, it would be a mess to add more on the sheets.
hermesalphaAuthor Commented:
Isn't it possible to use something like "Rank(if AK>0) and/if AQ>0 and/if AW>0" etc.? I think there is a Rank(if) function.
hermesalphaAuthor Commented:
I found these similar questions to mine on other forums: 
(instead of median for multiple ranges, could I replace with rank for multiple ranges using about the same VBA as here?)
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.