Ranking numbers that are a variance of 100% Wicanucks 12/31/2008 8:51:01 PM 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

Re: Ranking numbers that are a variance of 100% "T. Valko" <biffinpitt[ at ]comcast.net> 12/31/2008 9:21:38 PM 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" <Wicanucks[ at ]discussions.microsoft.com> wrote in message news:DC121A88-7251-419E-B297-37C055C6AEB6[ at ]microsoft.com... [Quoted Text] > 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

RE: Ranking numbers that are a variance of 100% Gary''s Student 12/31/2008 9:25:01 PM 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: [Quoted Text] > 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

RE: Ranking numbers that are a variance of 100% Shane Devenshire 12/31/2008 11:51:01 PM 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: [Quoted Text] > 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