Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank formula
I need a formula that will rank a given value against a set of other values.
For example, in column B, the numbers are 100, 90, 80, 70, 60 etc - i.e. sorted in descending order. I enter the number 63 in cell D1. In cell E1 I want a ranking formulat that returns the number 5, as this would be the rank of this number if it were in the array in column B. Can you help? I do know that if I sort column B ascending, then I could just use a vlookup formula, but this is not possible for a number of reasons. Thanks! Daniel |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank formula
try:
=MATCH(D1,B1:B10,-1)+1 Assumes D1 <100 "Daniel Bonallack" wrote: I need a formula that will rank a given value against a set of other values. For example, in column B, the numbers are 100, 90, 80, 70, 60 etc - i.e. sorted in descending order. I enter the number 63 in cell D1. In cell E1 I want a ranking formulat that returns the number 5, as this would be the rank of this number if it were in the array in column B. Can you help? I do know that if I sort column B ascending, then I could just use a vlookup formula, but this is not possible for a number of reasons. Thanks! Daniel |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank formula
Maybe this...
Assuming your lookup array goes from 100 to 0 and the number to rank will not be greater than 100. =MATCH(D1,A1:A11,-1)+(COUNTIF(A1:A11,D1)=0) -- Biff Microsoft Excel MVP "Daniel Bonallack" wrote in message ... I need a formula that will rank a given value against a set of other values. For example, in column B, the numbers are 100, 90, 80, 70, 60 etc - i.e. sorted in descending order. I enter the number 63 in cell D1. In cell E1 I want a ranking formulat that returns the number 5, as this would be the rank of this number if it were in the array in column B. Can you help? I do know that if I sort column B ascending, then I could just use a vlookup formula, but this is not possible for a number of reasons. Thanks! Daniel |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank formula
With the B numbers in B1:B5, I used =MATCH(ROUND(D1,-1),$B$1:$B$5,-1)
This gives 5 when D1=63 and 4 when D1=68 Any modification needed? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Daniel Bonallack" wrote in message ... I need a formula that will rank a given value against a set of other values. For example, in column B, the numbers are 100, 90, 80, 70, 60 etc - i.e. sorted in descending order. I enter the number 63 in cell D1. In cell E1 I want a ranking formulat that returns the number 5, as this would be the rank of this number if it were in the array in column B. Can you help? I do know that if I sort column B ascending, then I could just use a vlookup formula, but this is not possible for a number of reasons. Thanks! Daniel |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank formula
Thanks to all for the answers - these work fine.
"Toppers" wrote: try: =MATCH(D1,B1:B10,-1)+1 Assumes D1 <100 "Daniel Bonallack" wrote: I need a formula that will rank a given value against a set of other values. For example, in column B, the numbers are 100, 90, 80, 70, 60 etc - i.e. sorted in descending order. I enter the number 63 in cell D1. In cell E1 I want a ranking formulat that returns the number 5, as this would be the rank of this number if it were in the array in column B. Can you help? I do know that if I sort column B ascending, then I could just use a vlookup formula, but this is not possible for a number of reasons. Thanks! Daniel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
# rank formula | Excel Discussion (Misc queries) | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
RANK formula | Excel Discussion (Misc queries) | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
Rank Formula | Excel Worksheet Functions |