Solved

Modify Ranking Formula

Posted on 2014-09-08
3
87 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 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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 find and create templates in Excel 2013.
Video by: Zack
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

756 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