Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need some help with some functions
I have a column of numbers, and what I want to do is return the 7 highest of
the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find it. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need some help with some functions
Just sort the data along with a column of row markers. for example, if your
data is like: 2 0 10 5 3 1 8 0 5 8 9 8 4 5 9 9 8 6 0 8 as you see there are several 8's. Add a column of row markers: 2 1 0 2 10 3 5 4 3 5 1 6 8 7 0 8 5 9 8 10 9 11 8 12 4 13 5 14 9 15 9 16 8 17 6 18 0 19 8 20 finally sort by column A: 10 3 9 11 9 15 9 16 8 7 8 10 8 12 8 17 8 20 6 18 5 4 5 9 5 14 4 13 3 5 2 1 1 6 0 2 0 8 0 19 The top 7 pairs are the highest values and where they appeared in the original list. -- Gary''s Student - gsnu200740 "Cory from Eugene" wrote: I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find it. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need some help with some functions
This might be a bit roundabout, but I can't come up with a clever
single-formula way of doing this, so here goes. Name your number range OriginalList, and make sure it has a column heading. Use Advanced Filter - Copy to Location - Unique Values. Name the resulting range UniqueList. For the first formula, highlight 7 cells in a single column. Copy/ past this into the formula bar: =LARGE(UniqueList,ROW(1:7)) Hold Ctrl+Shift and press Enter. Those are your 7 largest values. Now, next to it, use this formula to find their first occurence in the number range: F1=MATCH(E1, OriginalList, 0) Assuming the Top 7 list begins in E1; copy down to the next 6 cells. On Aug 30, 7:46 pm, Cory from Eugene <Cory from wrote: I have a column of numbers, and what I want to do is return the 7 highest of the numbers. Then, I want to be able to return the row number of each one them. The only problem I have is, if two or more of the high numbers are the same, it will keep returning the same the row number, instead of the next and so on. Any suggestions? Please let me know. lol It's probably an easy answer, I just cant find it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula/functions for average and if functions | Excel Worksheet Functions | |||
XL2003 FILTER FUNCTIONS VS. XL2007 FILTER FUNCTIONS | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Looking for a site with functions that substitute the ATP functions | Excel Worksheet Functions | |||
Nesting functions in the functions dialog box | Excel Worksheet Functions |