How to Average the Five Smallest Non-Zero Values in an Excel Row
Question details
Calculate the average of the five lowest numbers across a row of cells while strictly ignoring any cells that contain the value zero.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing a dataset where the bottom five values need to be averaged, but baseline zeroes must be excluded to prevent them from improperly skewing the statistical result.
- Observed behavior
- Standard AVERAGE and SMALL functions include zero values if not explicitly instructed to ignore them, resulting in an inaccurate lower average.
Verify whether you are using a legacy version of Excel or Microsoft 365, as your software version determines if you should use a traditional array formula or modern dynamic array functions.
Use a Traditional Array Formula (AVERAGE, SMALL, IF)
This method works across almost all versions of Excel by combining AVERAGE, SMALL, and IF to force the calculation to ignore zeros.
By nesting an IF function inside SMALL, you can evaluate the range and only pass numbers that are not equal to zero into your calculation. The array constant {1,2,3,4,5} tells the SMALL function to extract the 1st, 2nd, 3rd, 4th, and 5th lowest values.
Click on the empty cell where you want the calculated average to appear.
Type the formula: =AVERAGE(SMALL(IF(A1:T1<>0,A1:T1),{1,2,3,4,5})) into the formula bar. Replace A1:T1 with your actual row range.
Press Ctrl + Shift + Enter simultaneously. Older versions of Excel require this to evaluate the array properly, which will place curly brackets {} around the formula.
Use LET and FILTER for Microsoft 365
This is a safer, more robust formula exclusively for Microsoft 365 users that intelligently handles rows containing fewer than five non-zero values.
Calculate Complex Arrays Seamlessly in WPS Spreadsheet
If you need a reliable and fast environment for complex statistical calculations, WPS Office Spreadsheets natively handles advanced array formulas like AVERAGE, SMALL, and IF with zero hassle.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
- 2. Input the array formula: Select a cell and type =AVERAGE(SMALL(IF(A1:T1<>0,A1:T1),{1,2,3,4,5}))
- 3. Calculate: Press Ctrl + Shift + Enter to instantly execute the array formula and get your accurate average.
Frequently Asked Questions
Why does my AVERAGE(SMALL(IF...)) formula return a #NUM! error?
A #NUM! error occurs if there are fewer valid, non-zero numbers in your selected range than the array requested (e.g., fewer than 5). To fix this, you can wrap your formula in an IFERROR function, or use the dynamic LET/FILTER formula if you have Microsoft 365.
How do I average the five largest non-zero values instead?
You can achieve this by simply replacing the SMALL function with the LARGE function in your formula. The updated formula would be: =AVERAGE(LARGE(IF(A1:T1<>0,A1:T1),{1,2,3,4,5})).
Can I use this formula on a column instead of a row?
Yes. The formula logic is identical for vertical data. Simply replace the horizontal row range (such as A1:T1) with your vertical column range (such as A1:A20).
What is the AGGREGATE alternative for this problem?
You can use the AGGREGATE function, where function number 15 represents SMALL, and option 6 ignores errors. By forcing zeros to become errors (e.g., dividing the range by the condition A1:T1<>0), AGGREGATE can automatically filter them out before calculating the smallest values.
