Solved

ranking a set of values in Excel

Posted on 2014-12-01
6
107 Views
Last Modified: 2014-12-01
I have a spreadsheet where cells, says A1 B1 C1 .....XX1 contains numerical values which are the result of some calculation
I want to have cells A2 B2 C2 ... XX2 containing the ranking (descending values) of corresponding values A1 B1 C1 ... XX1
For example with 5 cells A1 B1 C1 D1 E1 containing  12  23  98  33  10, cells A2 B2 C2 D2 D2 should contain 4  3  1  2  5
Is there a formula to calculate that ?
Thanks
0
Comment
Question by:LeTay
  • 3
  • 3
6 Comments
 

Author Comment

by:LeTay
ID: 40473385
I forgot to write that some of these A1 B1 ... cells are empty and so should not participate to the ranking at all
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40473397
=IFERROR(RANK(A1,$A$1:$A$5),"")
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40473398
If you are using Excel 2003 or earlier:

=IF(A1="","",RANK(A1,$A$1:$A$5))
0
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.

 

Author Comment

by:LeTay
ID: 40473402
Thanks...
I use a french version of Excel
Do you know what the equivalent is for "rank()"
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40473407
RANG
0
 

Author Comment

by:LeTay
ID: 40473410
Found it : rang !
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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 find and create templates in Excel 2013.
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: …

856 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