How to Calculate an Annualized Average from Nonzero Months in Excel
Question details
The user needs an Excel-compatible array formula that averages only nonzero monthly values from selected month columns, then multiplies the result by 12 to annualize the value.

- Product
- Microsoft Excel / WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Monthly amounts are placed in alternating columns from E5 through AA5, with intervening non-month columns and possible zero values. The user wants to calculate an On Pace For annual value without a #VALUE! error.
- Observed behavior
- A standard AVERAGE over the range would include zero values and non-month columns, and an incorrectly entered array formula can return a #VALUE! error.
Make sure your monthly values are numeric and arranged in the same alternating-column layout as the formula range. If you use an older Excel version, be prepared to confirm array formulas with Ctrl+Shift+Enter.
Use an AVERAGE(IF(ISODD(COLUMN()))) array formula to ignore zeros and non-month columns
Use this solution when monthly values are in alternating columns and you need to annualize the average of only the nonzero month cells.
The formula =AVERAGE(IF((E5:AA5>0)*ISODD(COLUMN(E5:AA5)),E5:AA5))*12 tests each cell in E5:AA5. The condition (E5:AA5>0) removes zeros, while ISODD(COLUMN(E5:AA5)) keeps only month columns that fall in odd columns such as E, G, and I.
Click the cell where you want the annualized average to appear, for example AC5.
Type =AVERAGE(IF((E5:AA5>0)*ISODD(COLUMN(E5:AA5)),E5:AA5))*12 in the formula bar.
In Microsoft 365 or Excel 2021, press Enter. In older Excel versions, press Ctrl+Shift+Enter to confirm it as an array formula.
Confirm that the result equals the average of the nonzero month cells multiplied by 12 and that no #VALUE! error appears.

Calculate the Annualized Average from Nonzero Months in WPS Office Spreadsheets
WPS Office Spreadsheets supports the same Excel array formula, so you can average only nonzero month cells and multiply by 12 without changing your workflow. It opens .xlsx workbooks, preserves formulas, and is free to use.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Menu > Open, and select the .xlsx workbook that contains your monthly data.
- 2. Select the result cell: Click the cell where the annualized average should appear, such as AC5.
- 3. Enter the array formula: Type =AVERAGE(IF((E5:AA5>0)*ISODD(COLUMN(E5:AA5)),E5:AA5))*12 and press Enter. WPS Spreadsheets evaluates array formulas dynamically.
- 4. Verify the annualized result: Confirm the returned value equals the average of nonzero month cells multiplied by 12, with zero and non-month columns excluded.

Frequently Asked Questions
Why does the formula multiply the average by 12?
Because the AVERAGE(IF(...)) part calculates a single-month equivalent from the nonzero monthly values. Multiplying by 12 annualizes that monthly average for an On Pace For full-year value.
What does the ISODD(COLUMN(E5:AA5)) part do?
It returns TRUE for odd-numbered columns. If your month columns are E, G, I, and so on, those are columns 5, 7, and 9, so the condition keeps month columns and ignores the non-month columns between them.
Do I need to press Ctrl+Shift+Enter in older Excel versions?
Yes. In Excel 2019 and earlier, confirm the formula with Ctrl+Shift+Enter so Excel treats it as an array formula. In Microsoft 365, Excel 2021, and WPS Office Spreadsheets, you can simply press Enter.
How can I adjust the formula if my month columns are not every other column?
Change the column condition to match your layout. For example, add a helper row that marks month columns, then use that helper range in the IF condition instead of ISODD(COLUMN(E5:AA5)).
What if the range contains blank cells or errors?
Blank cells are ignored because they do not satisfy E5:AA5>0. To suppress possible errors, wrap the formula in IFERROR, for example: =IFERROR(AVERAGE(IF((E5:AA5>0)*ISODD(COLUMN(E5:AA5)),E5:AA5))*12,0).




