To calculate a moving average in Microsoft Excel that excludes zero values, you can use the AVERAGEIF function combined with OFFSET or INDEX. This approach ensures that only non-zero numbers within your specified window are included in the calculation, providing a more accurate trend line for datasets containing empty or placeholder zeros.
Understanding the Problem with Standard AVERAGE
When creating a moving average, the standard
AVERAGE function treats zero as a valid numerical value. If your dataset uses zeros to represent missing data, gaps, or null states, including them in the average will skew your results downward. For example, if you have five days of sales data where one day had no sales (recorded as 0), the standard average will dilute the performance metric by dividing the total sum by five instead of four. This leads to an inaccurate representation of typical activity during active periods.
To solve this, we need a formula that identifies the range of cells for the current moving window and then calculates the mean of only those cells that do not equal zero. This requires dynamic referencing because the window shifts as you move down the spreadsheet.
Using AVERAGEIF with Dynamic Ranges
The most robust method involves using
AVERAGEIF paired with a dynamic range reference. The
AVERAGEIF function allows you to specify a criteria, such as "
<>0", which tells Excel to ignore any cell containing zero. However, you must first define the exact set of cells that make up your moving window.
Assume your data starts in cell
A2 and you want a moving average of the last 5 entries. In cell
B7 (which corresponds to the fifth data point), you would enter the following formula:
=AVERAGEIF(OFFSET(A$2, ROW()-7, 0, 5, 1), "<>0")
Let's break down how this works. The
OFFSET function creates a reference to a range. It starts at
A$2 (the absolute start of your data). The row offset is calculated by subtracting 7 from the current row number (
ROW()-7). If you are in row 7, the offset is 0, pointing to
A2. If you are in row 8, the offset is 1, pointing to
A3, and so on. The height of the range is set to 5, representing your lookback period. The width is 1 column.
The second argument, "
<>0", is the criteria. It instructs
AVERAGEIF to include only cells in the defined range that are not equal to zero. This effectively filters out the placeholder zeros while maintaining the correct count of non-zero items for the division.
Alternative Using INDEX and MATCH
Some users prefer
INDEX over
OFFSET because
OFFSET is volatile and recalculates every time the sheet changes, which can slow down large workbooks. An alternative approach uses
INDEX to define the start and end points of your window.
For a 5-period moving average skipping zeros, starting in row 7, you could use:
=AVERAGEIF(INDEX(A:A, MAX(2, ROW()-4)):INDEX(A:A, ROW()-1), "<>0")
Here,
INDEX(A:A, ROW()-4) finds the cell four rows above the current row, and
INDEX(A:A, ROW()-1) finds the cell immediately above the current row. This creates a dynamic range like
A3:A6 when in row 7. The
MAX(2, ...) part prevents errors in the first few rows where the calculation might try to reference rows above the data start.
Filling Down the Formula
Once you have entered the formula in the first applicable cell, you need to apply it to the rest of your dataset. You can drag the fill handle down to copy the formula. Ensure that the relative references adjust correctly. If you are using the
OFFSET method, the
ROW() function automatically updates for each new row, making the dragging process seamless.
If you encounter issues with the formula not updating, check if your calculation options are set to manual. Go to the
Formulas tab and ensure
Calculation Options is set to
Automatic. This is a common pitfall when working with complex array-like formulas.
For more information on handling arrays and filling down formulas efficiently, you might find our guide on
Excel 2021: How to "fill down" an array? helpful. Additionally, if your window size needs to change based on other variables, consider reading about
Calculating Moving Average: Excel - Dynamic Lookback (Window) Duration Based on Other Changing Values.
Handling Empty Cells vs. Zero Values
It is important to distinguish between empty cells and cells containing the number 0. The
AVERAGEIF function with "
<>0" will skip both zeros and empty cells, treating them similarly in terms of exclusion. However, if you have genuinely missing data that should be ignored entirely, this behavior is usually desired. If you have text labels or error values in the range,
AVERAGEIF will also ignore them, which is generally safe for numeric analysis.
If your dataset contains negative numbers, they will still be included in the average unless you add additional criteria. For instance, to exclude both zeros and negative numbers, you might need a more complex setup or helper columns, but for standard moving averages, excluding just zeros is the primary goal.
Troubleshooting Common Errors
If you see a
#DIV/0! error, it means all values in your current window are zero or empty. Since there are no non-zero numbers to average, Excel cannot divide by the count of valid items. To handle this gracefully, you can wrap your formula in an
IFERROR function:
=IFERROR(AVERAGEIF(OFFSET(A$2, ROW()-7, 0, 5, 1), "<>0"), "")
This returns an empty string if the average cannot be calculated, keeping your chart or dashboard clean. Another common issue is incorrect range alignment. Always verify that your offset logic points to the correct historical data points by testing the formula in a single cell before dragging it down.
Frequently Asked Questions
Why does my moving average drop suddenly?
Sudden drops often occur when a previously zero-filled cell becomes a positive number, or vice versa. Because the denominator (count of non-zero items) changes dynamically, adding or removing a zero can significantly alter the average. Check your source data for inconsistent zero usage.
Can I use this method for weighted averages?
No,
AVERAGEIF performs a simple arithmetic mean. For weighted averages excluding zeros, you would need to use
SUMPRODUCT combined with logical tests to filter out zeros from both the numerator and denominator.
What if I want to skip blank cells but keep zeros?
AVERAGEIF with "
<>0" skips both blanks and zeros. To keep zeros but skip blanks, you cannot use a simple criteria string. You would need to use
AVERAGEIFS with multiple conditions or a helper column that marks blanks separately.
Does this formula work in Google Sheets?
Yes, the syntax for
AVERAGEIF and
OFFSET is largely identical in Google Sheets. You can copy the same formulas directly into a Google Sheet without modification.
How do I visualize this moving average?
Select the column containing your calculated moving averages and insert a Line Chart. This will plot the smoothed trend line alongside your original data, highlighting the underlying pattern while ignoring noise represented by zeros.