Solved

# Modify Ranking Formula

Posted on 2014-09-08
88 Views
=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
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

LVL 13

Accepted Solution

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

LVL 26

Expert Comment

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))
``````
when you data is in column A

see attached file.
0

Author Closing Comment

ID: 40320753
Thanks
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

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 a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.
###### Suggested Courses
Course of the Month8 days, 17 hours left to enroll