Excel beginner largest values smallest value data analysis

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 generates and validates this formula automatically from a plain-English prompt.

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.

Generate This Formula Browse Library

The Formula

Prompt

"Find the 2nd and 3rd largest values in a list, and the smallest non-zero value"

Excel 
=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

  1. Use LARGE(A1:A10, 2) to find the 2nd largest value in the range A1:A10.
  2. Use LARGE(A1:A10, 3) to find the 3rd largest value in the same range.
  3. Use MIN(IF(A1:A10>0, A1:A10)) to find the smallest value greater than zero.
  4. 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

Prompt

"A1:A10 contains {5, 3, 9, 1, 7, 0, 2}"

Excel 
7, 5, 1
Prompt

"A1:A10 contains {0, -1, -2, 4, 3}"

Excel 
4, 3, 3

Frequently 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.

Generate Custom Formula See Examples