Excel Ranking Pre-Set Limits

[spkpnight]

I am trying to rank a column of data (dollar amounts) that will return
the ranks within specific high/low limits. If I my table has 50 items
and I use the RANK feature, the items will be ranked 1 to 50, or 50 to
1, depending on the selection criteria. What I want to do is Rank
these items but with pre-set limits. For instance, the lowest item
would be ranked a -5.00 while the highest number would be ranked 5.00.
No item's rank would fall outside these limits, and all would have a
different rank (4.99, -4.96, 1.26, etc.). How can I accomplish this?
Thanks.

 

[Pete_UK]

As your rank values fall in the range 1 to 50, and you want to
transform this range to -5 to +5 (a range of 10), you need to divide
your rank values by 5 and then subtract 5 from the result. Consequently
the cells that have your rank formula in at the moment can be changed
to:

=(your_rank_formula)/5 -5

You won't get a rank of 4.99, as the increments will be in steps of
0.2. The lowest rank will be -4.8.

Hope this helps.

Pete