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.

[table="width: 20, class: grid"]
[tr]
[td]-12:30[/td]
[td]1[/td]
[/tr]
[tr]
[td]2:34[/td]
[td]3[/td]
[/tr]
[tr]
[td]0:01[/td]
[td]5[/td]
[/tr]
[tr]
[td]-0:04[/td]
[td]4[/td]
[/tr]
[tr]
[td]3:23[/td]
[td]2[/td]
[/tr]
[/table]

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


[table="width: 20, class: grid"]
[tr]
[td]4[/td]
[td]2[/td]
[/tr]
[tr]
[td]9[/td]
[td]4[/td]
[/tr]
[tr]
[td]11[/td]
[td]5[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[/tr]
[tr]
[td]4[/td]
[td]2[/td]
[/tr]
[/table]

 

[James006]

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

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

HTH