Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display ranked data in order from a range
Hello,
This is a continuation of a recent problem I had. But never mind that for now:) I essentially have a range of data which has been ranked from largest to smallest and according to various criteria. I would now like to return those results which are ranked highest until a certain point - example; my workbook tells me I need to know the top 15 results. I would like to return the 1st, 2nd, 3rd and so on results until the 15th, or whichever number that happens to be. I envision something that can return the number one ranked result as long as it is less than the cell which contains the limit (15), and copying that down with modification so that it brings over the 2nd, 3rd etc. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display ranked data in order from a range
Assuming your list of numbers was in the range A5:A28 (not including any
headings), you could enter this formula in a cell in another column and copy it down 14 times: =OFFSET($A$5,RANK(A5,$A$5:$A$28,0)-1,0) If your range will be more dynamic, you could set it up to look at all of column A, as follows: =OFFSET($A$5,RANK(A5,A:A,0)-1,0) Hope this helps, Hutch "S Davis" wrote: Hello, This is a continuation of a recent problem I had. But never mind that for now:) I essentially have a range of data which has been ranked from largest to smallest and according to various criteria. I would now like to return those results which are ranked highest until a certain point - example; my workbook tells me I need to know the top 15 results. I would like to return the 1st, 2nd, 3rd and so on results until the 15th, or whichever number that happens to be. I envision something that can return the number one ranked result as long as it is less than the cell which contains the limit (15), and copying that down with modification so that it brings over the 2nd, 3rd etc. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can you sum the last 5 columns of data from a range of data | New Users to Excel | |||
Data Validation range | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Pivot Table - max rows allowed in data range | Excel Discussion (Misc queries) | |||
data range | Excel Discussion (Misc queries) |