This question already has an answer here:
- Excel Large Function with Duplicates 1 answer
So I have a column say A that looks like this:
I 15 0 3 15 M 8 0 8 21 Q 0 5 0 0
I can find the largest value with =MAX(A1:A100) I can find the smallest value with =SMALL(A1:A100,1)
How do i find the one before the largest and the one before that. I can use Large to call the Nth value, but then how do I know how many N values the function counts so I can know what positions it uses refer to the last 3 (largest and the 2 before)?
Ignoring duplicates - in the example the largest is 21, the one before is 15 and before that is 8. If I do =LARGE(A1:A100,3) it will return 15 as there is two of em in the column. And I'm looking for the 8 as the third largest value.
Below you will find the helpful folks of the Internet to have provided a PivotTable, an Array Formula solution, and I was finally able to do a simple formula one.
You can provide vba for the job should you insist we have everything, otherwise there is enough already.