Ranking numbers that are a variance of 100%

[Wicanucks]

How can i rank numbers that are a variance of 100% when being over or under
100% has the same score?

Example
-3.09%
6.04%
1.82%
1.46%
-4.14%
-0.73%
0.96%
4.60%

I want to rank the above numbers which are all a variance from 100% but the
closest to 100% are the top ranked numbers. -.73 would be number 1 and 6.04%
would be number 8. 4.6 would be 7th and -4.14 would be 6th. Thanks

 

[T. Valko]

In other words, you want to rank based on absolute values?

With your numbers in the range A1:A8...

Entered in B1 and copied down:

=SUMPRODUCT(--(ABS(A1)>ABS(A$1:A$8)))+1

 

[Gary''s Student]

If your values are in A1 thru A8, then in B1, enter:
=ABS(A1) and copy down

Then sort cols A & B by B. this results in:

-0.73% 0.0073
0.96% 0.0096
1.46% 0.0146
1.82% 0.0182
-3.09% 0.0309
-4.14% 0.0414
4.60% 0.046
6.04% 0.0604

This gives you values in rank order.

 

[Shane Devenshire]

Hi,

If you like Gary's solution, then here is a way to do it without sorting

=SMALL(ABS($A$1:$A$9),ROW(A1))

enter this formula as an array and copy it down. It assumes your numbers
are in A1:A9.