award descending points based on time closest to zero either + or -

[redspanna]

is there a formula for this, or even some VBA ?

Column A has a list of times, shown mm:ss, I want to 'award' points in relation to the times being closest to zero.
The points awarded would start with the time closest to zero being either a + OR - time.
The points start at the total number of entries, so in this example there are 5


So the time closest to zero in below being either a + or - is 0.01 so that is awarded 5 points
the next time closest to zero in below being either a + or - is -0.04 so that is awarded 4 points, and so on



So I would like a formula or VBA to plot the awarded points into column B as described above.

-12:30	1
2:34	3
0:01	5
-0:04	4
3:23	2

Thanks in advance you clever lot

 

[James006]

Hello,

You could use the following array formula to rank by descending order ...

=SMALL(ABS($A$2:$A$6),ROW($A$2:$A$6)-ROW($A$2)+1)

Hope this will help

 

[redspanna]

I managed t get the following formula to work (using correct ranges)

=SUMPRODUCT(--(ABS(F$2:F$14)>ABS(F2)))+1


however is it possible to adapt again so that includes 'tied numbers'


for example : numbers in first column and rank in second column and because the number 4 is shown twice in column one the rank skips from 2 to 4 and excludes 3

4	2
9	4
11	5
1	1
4	2

 

[James006]

As far as tie breaking ... see Chip's solution ...

http://www.cpearson.com/excel/Rank.aspx

HTH