Videos
I want cells C1:C5 to list the top 5 values from A1:A10.
I want cells D1:D5 to list the corresponding top 5 fruit names from B1:B10.
I am pretty handy with Excel, but for the life of me I can not figure this out and don't know the right question to search google.
Thanks
Looking for a way to pull values from a data table into another sheet. I would like to pull the 5 highest values and the 5 lowest values. not having much luck.
Would also like to pull the corresponding data on the same row if possible.
Given a data setup like this:
The formula in cell D2 and copied down is:
=INDEX($B$2:$B$28,MATCH(1,INDEX(($A$2:$A$28=LARGE($A$2:$A$28,ROWS(D$1:D1)))*(COUNTIF(D$1:D1,$B$2:$B$28)=0),),0))
This formula will work even if there are tied OPS scores among players.
There 3 functions you want to look at here:
- LARGE - Returns the k-th largest value in a data set.
- INDEX - Returns a value or the reference to a value from within a table or range.
- MATCH - The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.
I ran a sample in Excel with your OPS values in Column B and Players in Column C, see below:
- In Cells A13 to A17, the values 1 to 5 were inserted to specify the nth highest value.
- In Cell B13, the following formula was added:
=LARGE($B$2:$B$11, A13) - In Cell C13, the following formula was added:
=INDEX($C$2:$C$11,MATCH(B13,$B$2:$B$11,0)) - These formulae get the highest ranking OPS and Player based on the value in A13.
- Simply select and drag to copy these formulae down to the next 4 cells which will reference the corresponding ranking in Column A.
Hi all, sometime back I had seen a YT video that had a neat solution to returning top N rows using the Index function, however, I am not able to find it again. I don't know if I remember it wrong or if that it is actually possible but it's eating my brain up so I thought I'd ask it here.
So the problem is this. Let's say I have data in A1:B10 and I want to return the top 5 values using the Index function.
I can use this formula - =INDEX(A12#,{1;2;3;4;5},0)
But is there a way so I can reference in the formula 1 to 5 directly instead of using semi-colons?
Edit: I am not looking to get top 5 highest values or lowest values. I am just looking to get the top 5 rows in an array. The goals is to for example, wrap it around a filter function that outputs an array to only display Top N results.
Secondly, the solution I remember seeing had an operator that replaced {1;2;3;4;5} in the formula with something shorter and dynamic, so that is specifically what I was aiming for. It is not a problem I am looking to solve currently, just curious as it was something that I hadn't seen before.
In cell C2, enter
=B2>=PERCENTILE($B$2:$B$63,0.95)
you can then copy this to C3:C63.
Column C now shows TRUE only for those rows with a B value in the top 5%.
Additionally you may like to apply a filter.
You can specifie rang of your data and then color it with very little effort.
Here is an example, where you can color top N records:
Hope it helps :)
I assume the top 5 values should go in 5 different cells. In this case why not use the same formula you already had, but look for 2nd, 3rd, 4th and 5th highest?
=INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,1),$V$6:$V$244,0))
=INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,2),$V$6:$V$244,0))
=INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,3),$V$6:$V$244,0))
=INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,4),$V$6:$V$244,0))
=INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,5),$V$6:$V$244,0))
Just enter the below formula, wherever you want the output to be,
=INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,ROW(1:1)),$V$6:$V$244,0))
and drag it to 5 cells for getting the maximum 5 values. If you want the same for maximum 10, drag it for 10 rows. Hope this helps.