Forum Discussion
Change in return of AVERAGE function - Mac Excel
I was puzzled that a spreadsheet I use daily generated an error today that had not existed in earlier versions. Attempts to confirm the validity of the spreadsheet functions by running prior versions that had previously run error free resulted in the same error.
Eventually, it seems that the operation of the AVERAGE function has been changed in an Excel program update that was installed yesterday. Previously, if an AVERAGE function addressed a range of empty cells, it would return a zero value. Now it is returning the error #DIV/0! This is strange because the AVERAGE function will now return zero if the addressed range contains zeros rather than just being empty. Not sure if Microsoft intended this change or if the change might also apply to other functions.
In muy case, I'm able to change my spreadsheet to provide for this but it would have been nice to have some warning.
Only numeric values count
Empty or text-only ranges now correctly trigger #DIV/0!
That’s why:
Zeros → AVERAGE = 0
Empty → AVERAGE = #DIV/0!
This aligns Mac Excel with Windows Excel and Excel Online.
So when you open:
an old file
or even an older copy of Excel
the new calculation logic is applied, producing the same error everywhere.
general fix
=IFERROR(AVERAGE(A1:A10), 0)
This preserves your prior behavior explicitly.
If blanks should count as zero
=AVERAGE(IF(A1:A10="",0,A1:A10))
(Confirm with Cmd+Shift+Enter if not using dynamic arrays.)
Other functions affected similarly
This stricter behavior also applies to:
- AVERAGEIF / AVERAGEIFS
- STDEV, VAR
- MIN, MAX (when all values are non-numeric)
- Any function that divides by COUNT()
So it’s worth auditing any formulas that assumed empty = zero.
Bottom line
Your understanding is correct
This was a calculation-engine change, not your mistake
Your workaround is the correct long-term approach
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
3 Replies
Only numeric values count
Empty or text-only ranges now correctly trigger #DIV/0!
That’s why:
Zeros → AVERAGE = 0
Empty → AVERAGE = #DIV/0!
This aligns Mac Excel with Windows Excel and Excel Online.
So when you open:
an old file
or even an older copy of Excel
the new calculation logic is applied, producing the same error everywhere.
general fix
=IFERROR(AVERAGE(A1:A10), 0)
This preserves your prior behavior explicitly.
If blanks should count as zero
=AVERAGE(IF(A1:A10="",0,A1:A10))
(Confirm with Cmd+Shift+Enter if not using dynamic arrays.)
Other functions affected similarly
This stricter behavior also applies to:
- AVERAGEIF / AVERAGEIFS
- STDEV, VAR
- MIN, MAX (when all values are non-numeric)
- Any function that divides by COUNT()
So it’s worth auditing any formulas that assumed empty = zero.
Bottom line
Your understanding is correct
This was a calculation-engine change, not your mistake
Your workaround is the correct long-term approach
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Many thanks. Your answer is what I suspected.
I had not counted on the other affected formulas, such as MAX, MIN or COUNT() issues. I have many formulas relying on blank = 0 and have not yet seen issues with blanks but may be missing them.
I have a lot of work to do to fix this. I wish there was a tool to hunt these down in a huge spreadsheet..For very big workbooks, you can run a macro to highlight formulas referencing ranges that may include blanks:
Sub HighlightFormulasWithRanges() Dim ws As Worksheet, cell As Range For Each ws In ActiveWorkbook.Worksheets For Each cell In ws.UsedRange If cell.HasFormula Then If InStr(1, cell.Formula, "AVERAGE", vbTextCompare) > 0 _ Or InStr(1, cell.Formula, "MAX", vbTextCompare) > 0 _ Or InStr(1, cell.Formula, "MIN", vbTextCompare) > 0 _ Or InStr(1, cell.Formula, "STDEV", vbTextCompare) > 0 _ Or InStr(1, cell.Formula, "VAR", vbTextCompare) > 0 Then cell.Interior.Color = RGB(255, 255, 0) 'Highlight in yellow End If End If Next cell Next ws End SubThis highlights all formulas with functions that are sensitive to blank cells.
You can then manually check / wrap them with IFERROR or IF(…="",0,…) as needed.
