Solved

Modify Ranking Formula

Posted on 2014-09-08
3
84 Views
Last Modified: 2014-09-13
=RANK.AVG(D7,D$7:D$31,1)

I am using the above ranking formula.  My data is shown below.  This returns the highest value as rank 25 and smallest as rank 1. I would like to return the highest value of rank 1 and my smallest value as rank 25.  How should the formula be modified to return this???

Value                    Rank
 $450,766       25      
 $322,606       24      
 $214,725       21      
 $238,334       22      
 $302,320       23      
 $151,416       20      
 $115,541       16      
 $151,007       19      
 $96,550                       11      
 $134,077       18      
 $109,442       14      
 $17,559                       1      
 $118,554       17      
 $110,613       15      
 $104,348       12      
 $77,383                        6      
 $62,749       3      
 $92,103       9      
 $96,527       10      
 $86,255       7      
 $90,501       8      
 $67,824       4      
 $57,892       2      
 $105,880 13      
 $73,820       5
0
Comment
Question by:vpopper
3 Comments
 
LVL 13

Accepted Solution

by:
Russell Fox earned 500 total points
ID: 40310832
=RANK.AVG(D7,D$7:D$31,0)
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40311839
=IF(ROUNDUP(PERCENTRANK.INC($A$1:$A$24,A1,2)/4*100,0)=0,1,ROUNDUP(PERCENTRANK.INC($A$1:$A$24,A1,2)/4*100,0))

Open in new window

 when you data is in column A

see attached file.
C--Users-jamilm-Downloads-Ranking-Formul
0
 

Author Closing Comment

by:vpopper
ID: 40320753
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Scenario: Your operations manager has discovered an anomaly in your security system. The business will start to suffer within 15 minutes if it is a major IT incident. What should she do? We have 6 recommendations for managing major incidents (https:…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn the basics of using filtering and sorting in Excel 2013.
Viewers will learn a basic relationship technique in Power Pivot for Excel 2013.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now