Q: Ranking formula for a column of numbers

Hi Jim,

The formula is working correctly. The reason that a score of 7 is rated as 8th place is that there are 7 contestants with better scores.

Jerry

Jim,

The formula as written is correct for Numbers.

Here is a snippet from the function reference regarding the rank() function:

The RANK function returns the rank of a number within a range of numbers.

• RANK(num-date-dur, num-date-dur-set, largest-is-high)
  • num-date-dur:  A value. num-date-dur is a number value, a date/time value, or a duration value.
  • num-date-dur-set:  A collection of values. num-date-dur-set is a collection containing number, date, or duration values. All values must be of the same type.
  • largest-is-high:  An optional value specifying whether the smallest or the largest value in the collection is ranked 1.
    • largest is low (0, FALSE, or omitted):  Assign the largest value in the collection the rank 1.
    • largest is high (1, or TRUE):  Assign the smallest value in the collection the rank 1.

Usage Notes

• Values included in the collection that are the same are ranked together, but impact the outcome.
• If the specified value does not match any of the values in the collection, an error is returned.

You can open the function reference by selecting the menu item "View > Show Function Browser"

Jim,

=RANK(V5, $V$4:$V$32, TRUE) is a perfectly good expression in Numbers, but I would never use it because Numbers allows a more simple addressing scheme.

In Numbers you could write:

=RANK(V, V, 1)

The result would be the same.

Since RANK requires a single cell reference for the first parameter, you can simply state the column in which it is found, and the same row is assumed. Since RANK requires a Range of addresses for the second parameter, Numbers allows you to include the entirety of column V by writing V, and of course TRUE and 1 are equivalent.

Jerry

Offhand I can't think of a single-cell-formula method for determining "place" if your desire is to have the lowest score(s) = first place, the next lowest score(s) = 2nd place, and so on but here is a way to do it using two extra columns.

Column C formula =LOOKUP(B,E,F)

Column E formula =SMALL(B,ROW()-1)

F2 = 1

F3 =IF(E3>E2,F2+1,F2)

Fill down from F3

This may be harder to implement if your table has blank rows in it. I think it will still work though.

Hi Jerry,

Sorry for hijacking this thread but I found it while trying to find a solution.

I am trying to create a ranking formula for the following spreadsheet in numbers and trying to make sense of your answer on the thread and adapt it but as my numbers skills are fairly limited i don't quite understand. Could you explain how I can create a ranking formula for the following spreadsheet?

Thanks in advance