Ranking Numbers on how close to zero

[scottymelloty]

i know how to rank a list of numbers but i want it so it ranks it on how close to zero they are

ie

-1.42
0.10
0.58
0.76

i want it so rank 1 is 0.10 , if i just use the normal ranking method it makes -1.42 ranked 1 as that is the first number


many thanks for any helo

[Ron Coderre]

With
A1:A10 containing numeric values and NO blank cells range...

This formula calculates the rank:
(edited to simplify the formula by eliminating the numeric test section)

Please Login or Register  to view this content.

Otherwise, if there ARE blanks (or text cells) they will be ignored if you...
use this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER
(instead of just ENTER)

Please Login or Register  to view this content.

Copy either formula down through B10

Does that help?

[NBVC]

Add a helper column that takes the absolute value of your originals and then rank those..

so if your original data is in A1:A4, then in B1, copied down... =ABS(A1)

then in C1, copied down: =Rank(B1,$B$1:$B$4,1)

[scottymelloty]

thanks for your help , they do work except sometimes there will be a minus number in the list that is closer to zero than some of the other numbers

ie the list of numbers may be

-0.9
0.5
0.78

so in that example -0.9 is the closest to zero , using the formulas above it actually makes that the outsider of the 3 because i think its making it +0.92

altho i could be making a simple error somewhere

[NBVC]

I don't understand how -0.9 is closer to 0 than 0.5 or 0.78 when you say that

If I look at a number line... it is further from 0.

[scottymelloty]

ah yeah sorry my mistake,, was getting confused on my sheet,, think ive sorted it now

many thanks !!!