Ranking numbers that are a variance of 100%
 

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

Ranking numbers that are a variance of 100%
 

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

--
Biff
Microsoft Excel MVP


"Wicanucks" wrote in message
...
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

Ranking numbers that are a variance of 100%
 

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.
--
Gary''s Student - gsnu200823


"Wicanucks" wrote:

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

Ranking numbers that are a variance of 100%
 

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Wicanucks" wrote:

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