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?

hermesalpha
hermesalpha used Ask the Experts™
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
You can use the RANK function in the result columns.  It tells the position of a value in a range of cells.

Author

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)?

Author

Commented:
Ok, found it now, just add "and" between.

But how to rank in reverse order?
Ensure you’re charging the right price for your IT

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

Author

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 rank.vg 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:
=RANK.EQ(ak9,listnum,0)

- the ak9 is the number to compare, the listnum is the set of numbers and 0 indicates a descending order (1 for ascending order).

Author

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

Author

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.

Author

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.

Author

Commented:
I found these similar questions to mine on other forums:

http://www.ozgrid.com/forum/showthread.php?t=86852 
(instead of median for multiple ranges, could I replace with rank for multiple ranges using about the same VBA as here?)

http://excel.bigresource.com/benifit-of-the-worksheet-function-AREAS--Txl9gxyR.html
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial