How to Find the 2nd and 3rd Largest Values and Smallest Non-Zero Value in Excel?
Find 2nd and 3rd Largest Values is a Excel function that retrieves the 2nd and 3rd largest values from a range and the smallest non-zero value.. Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
Struggling to extract specific values from your data? This guide shows you how to find the second and third largest values and the smallest non-zero value in Excel.
The Formula
"Find the 2nd and 3rd largest values in a list, and the smallest non-zero value"
=LARGE(A1:A10, 2) & ", " & LARGE(A1:A10, 3) & ", " & MIN(IF(A1:A10>0, A1:A10))This formula retrieves the 2nd and 3rd largest values from a range and the smallest non-zero value.
Step-by-Step Breakdown
- Use LARGE(A1:A10, 2) to find the 2nd largest value in the range A1:A10.
- Use LARGE(A1:A10, 3) to find the 3rd largest value in the same range.
- Use MIN(IF(A1:A10>0, A1:A10)) to find the smallest value greater than zero.
- Combine the results using concatenation to display them together.
Edge Cases & Warnings
- The range contains duplicate values, affecting the ranking.
- All values in the range are zero or negative, leading to errors.
- The range is empty, resulting in a #NUM! error.
- The range contains non-numeric values, which may cause unexpected results.
Examples
"A1:A10 contains {5, 3, 9, 1, 7, 0, 2}"
7, 5, 1"A1:A10 contains {0, -1, -2, 4, 3}"
4, 3, 3Frequently Asked Questions
What if my range has fewer than three values?
The formula will return an error for the missing ranks.
Can I use this formula with non-contiguous ranges?
No, the formula only works with a single contiguous range.
How do I modify the formula for a different range?
Change 'A1:A10' to your desired range in the formula.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.