Formula Errors

How to Average the Latest Months with Data in Excel (Ignore Zeros)

Huma Ashraf Ch Oct 12, 2026 869 views

Question details

The user needs an Excel formula to calculate the average of the most recent specified number of months, ignoring blank cells and zero values, without triggering #REF! errors when data is insufficient.

How to Average the Latest Months with Data in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing monthly data where some recent months might be blank or zero, requiring a dynamic rolling average based on a specific number of trailing months.
Observed behavior: Traditional formulas using OFFSET often return a #REF! error when the specified backward range exceeds the available data on the worksheet.

Before you start

Check your Excel version to see if it supports modern dynamic array functions like FILTER and TAKE, as this will determine the most efficient formula approach for your data.

Solution 1Recommended

Use Dynamic Array Functions (FILTER and TAKE)

The cleanest and most robust method for newer Excel versions (Microsoft 365 and Excel 2021) to extract and average the latest non-zero values.

This modern approach avoids the volatility of the OFFSET function and elegantly handles ranges with empty or zero values.

Select the target cell

Click on the cell where you want the rolling average to be displayed.

Input the dynamic formula

Type the formula =AVERAGE(TAKE(FILTER($B20:G20, $B20:G20<>0), , -$B$27)). Replace $B20:G20 with your actual data range, and $B$27 with the cell containing the number of months you want to average.

Apply to other rows

Press Enter to calculate the average. You can then copy the formula across and down to apply it to other rows in your dataset.

Use Dynamic Array Functions (FILTER and TAKE)

How it Works: The FILTER function removes zeros and blanks. The TAKE function with a negative number extracts the specified number of trailing values from that filtered list, which are then averaged.

Solution 2

Use OFFSET with Error Handling

A traditional formula approach for older Excel versions that do not support dynamic arrays.

Powerful Spreadsheet Alternative

Easily Calculate Rolling Averages with WPS Spreadsheet

WPS Spreadsheet is a robust, free tool that fully supports advanced data analysis formulas, including AVERAGE, OFFSET, and IFERROR. You can easily manage monthly reports and calculate dynamic averages with complete compatibility.

1. Open your monthly data: Launch WPS Spreadsheet and open your existing .xlsx file containing the monthly data.
2. Select the formula cell: Click on the cell where you want to output the rolling average.
3. Apply the rolling average formula: Type in your preferred OFFSET or AVERAGE formula to calculate the latest months.
4. Fill the series: Press Enter, then drag the fill handle at the bottom-right corner of the cell to apply the calculation to the rest of your report.

Fully compatible with Microsoft Excel formulas and .xlsx filesSupports advanced mathematical, logical, and array functionsFree and lightweight alternative for complex data analysisFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does my OFFSET formula return a #REF! error?

A #REF! error occurs when the OFFSET function attempts to reference a range that extends beyond the edge of the worksheet, such as trying to look 6 columns back when you are currently in column D. Wrapping your formula in IFERROR handles this gracefully.

How do I ignore zero values in a standard AVERAGE function?

You can use the AVERAGEIF function. For example, entering =AVERAGEIF(B2:G2, "<>0") will calculate the mean of the range while automatically excluding any cells that contain a zero or are blank.

Are FILTER and TAKE functions available in older Excel versions?

No, FILTER and TAKE are dynamic array functions introduced in Microsoft 365 and Excel 2021. For older versions, you must use traditional formulas combining OFFSET, INDEX, or MATCH.

Excel & Data Problems