I need some help with some functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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.
 
Back
Top