Solved

Modify Ranking Formula

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

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 how to apply various conditional formatting in Excel 2013.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

790 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