Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate variance between numbers? | Excel Worksheet Functions | |||
Editing a column chart for Variance Walk negative numbers | Charts and Charting in Excel | |||
how do i display a variance in all positive numbers? | Excel Worksheet Functions | |||
Pivot Tables - Variance and Variance % | Excel Discussion (Misc queries) | |||
Pivot Tables - Variance and % Variance fields | Excel Discussion (Misc queries) |