?
Solved

Modify Ranking Formula

Posted on 2014-09-08
3
Medium Priority
?
90 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 13

Accepted Solution

by:
Russell Fox earned 2000 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

: 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…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Video by: Zack
Viewers will learn about various customizable options in Excel 2013.
Viewers will learn the basics about Excel 2013’s new Flash Fill feature.

770 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