'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Calculate Minimum and Average While Ignoring Zeros in Excel
Question details
The user needs to calculate the minimum and average values of a dataset containing zeros, explicitly ignoring the zero values so they do not skew the final calculations.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing daily turnover values where future dates or days with no data are represented by zeros instead of blank cells.
- Observed behavior
- Standard MIN and AVERAGE functions include zeros, resulting in inaccurate minimum and average turnover calculations, and using newer functions sometimes results in a #NAME? error.
Identify the exact cell range containing your dataset and ensure that your zero values are formatted as numbers, not text.
Use AVERAGEIF and MINIFS Functions to Exclude Zeros
This is the most direct and modern method to calculate averages and minimums conditionally in supported versions of Excel.
The AVERAGEIF and MINIFS functions allow you to specify criteria for your calculations. By setting the criteria to "not equal to zero", Excel will completely ignore zero values in the selected range.
Click an empty cell where you want the average to appear. Type `=AVERAGEIF(A1:A7,"<>0")` (replace A1:A7 with your actual data range) and press Enter.
Select another empty cell for the minimum value. Type `=MINIFS(A1:A7,A1:A7,"<>0")` and press Enter.
Use Array Formulas for Older Excel Versions
If your Excel version returns a #NAME? error for MINIFS, you can use a combination of MIN and IF as an array formula instead.
Use WPS Spreadsheet for Advanced Formula Calculations
WPS Spreadsheet fully supports advanced conditional functions like AVERAGEIF and MINIFS, allowing you to exclude zero values from your data analysis effortlessly without compatibility errors.
- 1. Open Your Data: Launch WPS Spreadsheet and open your document containing the turnover data.
- 2. Apply AVERAGEIF: Click on the target cell and type `=AVERAGEIF(range, "<>0")` to find the average excluding zeros, then press Enter.
- 3. Apply MINIFS: In another cell, type `=MINIFS(range, range, "<>0")` to find the minimum excluding zeros, then press Enter.
Frequently Asked Questions
How do I ignore blank cells when calculating the average in Excel?
The standard AVERAGE function automatically ignores blank cells (empty cells). However, it does not ignore cells containing the number 0. If your future days are truly empty cells and not filled with zeros, you can simply use the standard `=AVERAGE(A1:A7)` formula.
Why am I getting a #NAME? error when using MINIFS?
The #NAME? error occurs when Excel does not recognize the function name. This typically happens if you are using an older version of Excel (such as Excel 2016 or earlier) that does not support the MINIFS function. You can use the array formula `=MIN(IF(A1:A7<>0, A1:A7))` instead.
Do I need to use quotation marks around the criteria in these formulas?
Yes, in functions like AVERAGEIF and MINIFS, logical operators combined with values (like "<>0") must be enclosed in double quotation marks so that Excel interprets the condition correctly.
Can I calculate the maximum value while ignoring zeros?
Yes, you can use the MAXIFS function similarly. For example, typing `=MAXIFS(A1:A7,A1:A7,"<>0")` will calculate the maximum value in the range while ignoring any zero values.
