Calculation Issues

How to Calculate an Annualized Average from Nonzero Months in Excel

Jason MillerJason Miller Nov 13, 2026 869 views

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.

How to Calculate an Annualized Average from Nonzero Months in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click the cell where you want the annualized average to appear, for example AC5.

2
Enter the array formula

Type =AVERAGE(IF((E5:AA5>0)*ISODD(COLUMN(E5:AA5)),E5:AA5))*12 in the formula bar.

3
Confirm the formula

In Microsoft 365 or Excel 2021, press Enter. In older Excel versions, press Ctrl+Shift+Enter to confirm it as an array formula.

4
Check the returned value

Confirm that the result equals the average of the nonzero month cells multiplied by 12 and that no #VALUE! error appears.

Use an AVERAGE(IF(ISODD(COLUMN()))) array formula to ignore zeros and non-month columns
Formula works across supported Excel versions: In WPS Office Spreadsheets, the same formula can be entered directly and evaluated with its array formula engine.
WPS Office Spreadsheets

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. 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. 2. Select the result cell: Click the cell where the annualized average should appear, such as AC5.
  3. 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. 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.
Uses the same AVERAGE(IF(...)) array formula as Microsoft ExcelIgnores zero values and non-month columns before annualizingOpens, edits, and saves .xlsx files with full compatibilityFree, lightweight, and familiar ribbon interfaceNo subscription required for personal or professional use
microsoft office alternative - wps office

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).