Rank by Column Apple Numbers

Hi Ken,

looks like you picked the input in the wrong order.

D2 should be RANK(C2,C$2:C$21,1) you have RANK($C$2:$C$21,C2,1)

You wrote " ... and then change to C3, C4, etc. ..." I hope you don't change this manually!

Write the formula for cell D2 and then select this cell and pull it down by picking and dragging the little dot.

Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Ralf

Hi Ken,

Greetings from the other end of the country!

I reconstructed your table , entering only the Diff values in column C and the names in column A.

My formula (shown below the table) is the same as yours, with the exception that I made the table large enough to contain the list, with no extra rows, dropped the .3 from the end of the diff values (it made no difference to the rank, and would only become significant if the participants estimated to the nearest 100m) Rounding the actual distance to the nearest km would also remove the (consistent) .3 from the Diff column, for a cleaner result.

Here's my table, with the formula displayed under the table, and a text version of your formula below that.

The the main issue I see with your formula is the reversal of the first two arguments of the RANK function.

Here's the syntax of that function:

RANK(value, value-set, largest-is-high)

          'value' is the individual value to be ranked. (C2),

                       'value-set' is the set of numbers within which the rank will be determined (C$2:C$21),

                                           'largest-is-high' tells Numbers to assign the 'highest rank number' to the largest value

                                             and the 'lowest rank number' ( 1 ) to the smallest value in the set.

Unless you are filling the formula left or right into new columns, the absolute reference operator ($) before C serves no purpose, and can be omitted. The same operator, before 2 and 21 is necessary given the notation you have used, to keep the range reference to C2:C21 as the formula is filled down.

Your formula revised: RANK(C2,C$2:C$21,1)

Enter as shown in D2, then fill down to the end o the column.

Regards,

Barry

Since you are have the names sorted by difference you can use a much simpler formula:

In D2, filled down the column by dragging the yellow dot.

=ROW()−1

To get the new rankings after you change the value in B1 you can quickly sort again on C:

Since a student's guess could be greater or less than the actual in the end, I used this formula in C2, filled down the column:

=ABS(B2−$B$1)

SG