Solved

Modify Ranking Formula

Posted on 2014-09-08
3
85 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Viewers will learn the different options available in the Backstage view in Excel 2013.
Viewers will learn the basics about Excel 2013’s new Flash Fill feature.

776 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