Tigerexcel
Active Member
- Joined
- Mar 6, 2020
- Messages
- 493
- Office Version
- 365
- 2019
- Platform
- Windows
I'm after a formula that will return the corresponding name of person who has the maximum age. In this case Fred would go in A11 as he is 65 years of age. My formula albeit clunky does return the highest n values, this is determined by cell B10 and is dynamic, the problem is that I need it to also return the person.
Ultimately I would like to create a chart that only plots the n value, so if n = 5, the graph plots the highest 5 values and no more.
So I guess my question is can my formula be B11 onwards be condensed down (it might not be and that is ok), more importantly I need the name returned in A11 onwards. Hopefully from that, the chart should be ok, my main concern is that it won't pick up extraneous cells eg I ask for top 2 and it gives me the top 2 values as well as some empty cells.
Ultimately I would like to create a chart that only plots the n value, so if n = 5, the graph plots the highest 5 values and no more.
So I guess my question is can my formula be B11 onwards be condensed down (it might not be and that is ok), more importantly I need the name returned in A11 onwards. Hopefully from that, the chart should be ok, my main concern is that it won't pick up extraneous cells eg I ask for top 2 and it gives me the top 2 values as well as some empty cells.
Book10 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Name | Age | Sales | ||
2 | Tom | 33 | 39339 | ||
3 | Fred | 65 | 10413 | ||
4 | Bert | 30 | 65910 | ||
5 | Kate | 29 | 36503 | ||
6 | Sara | 42 | 17112 | ||
7 | Bill | 40 | 37533 | ||
8 | Harry | 59 | 52730 | ||
9 | |||||
10 | Top | 4 | |||
11 | #VALUE! | 65 | |||
12 | #VALUE! | 59 | |||
13 | #VALUE! | 42 | |||
14 | #VALUE! | 40 | |||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A11:A14 | A11 | =IF(B11<>"",INDEX($B$2:$B$8,MATCH(B2,$B$2:$B$8,0),"")) |
B11:B14 | B11 | =IF(ROW()-ROW($11:$11)+1<=$B$10,LARGE($B$2:$B$8,ROW()-ROW($11:$11)+1),"") |