Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Ranking Pre-Set Limits
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. -- spkpnight ------------------------------------------------------------------------ spkpnight's Profile: http://www.excelforum.com/member.php...o&userid=31928 View this thread: http://www.excelforum.com/showthread...hreadid=516526 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Ranking Pre-Set Limits
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
unhide menu bar in excel - just disappeared | Setting up and Configuration of Excel | |||
Open Excel 2003 from Windows Explorer | Excel Discussion (Misc queries) | |||
convert pocket excel back to standard excel | Excel Discussion (Misc queries) | |||
Wrap text limits in Excel 2003 cell formatting | Excel Discussion (Misc queries) |