Help: Rank 1-100, then "penalize" for over 100 - i.e. a 99 would rank higher than 102

[Maverick01b]

Hello people who are smarter than I

I need a formula to rank a value 1-100, but then penalize any value that is above 100. Is is a "percent to budget" ranking, so going over budget can not help you rank the highest in the company. I would need the ranking to look something like this...

The 102 store would out-rank the 97 store because the 102 store is only 2 points away from the target goal number of 100%, where as the 97 store is 3 points away. Hopefully this makes sense.

Percent - Rank
100 - 1st
97 - 5th
99 - 2nd
101 - 2nd
102 - 4th

Help would be GREATLY appreciated!

-Mav

 

[BrianMH]

I would add a helper column and then rank that.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]=ABS(A1-100)[/TD]
[TD]=RANK(B1,$B$1:$B$5,0)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]99[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]98[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]97[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]107[/TD]
[TD]7[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[Maverick01b]

Thank you for your suggestion! However, this would rank anything above 100 towards the bottom. I need a store at 101 to rank higher than a store at say 95, because even though the 101 store is 'above budget' they are closer to their budget goal than any store with a budget from 1-98. Any ideas on that?

 

[shg]

       --A-- -B-- ---------C----------
   1   Value Rank                     
   2      90   90 B2: =100-ABS(A2-100)
   3      91   91                     
   4      92   92                     
   5      93   93                     
   6      94   94                     
   7      95   95                     
   8      96   96                     
   9      97   97                     
  10      98   98                     
  11      99   99                     
  12     100  100                     
  13     101   99                     
  14     102   98                     
  15     103   97                     
  16     104   96                     
  17     105   95                     
  18     106   94                     
  19     107   93                     
  20     108   92

 

[Maverick01b]

MrExcel MVP for sure - thanks, Shg!!

 

[shg]

You're welcome.