Forum Discussion

Copper Contributor

Jan 16, 2026

Solved

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

community

excel

Excel on Mac

Formulas and Functions

Macros and VBA

office 365

NikolinoDE
Jan 17, 2026
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.

NikolinoDE

Platinum Contributor

Jan 17, 2026

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.

Stuart_Miller

Copper Contributor

Jan 22, 2026

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

NikolinoDE

Platinum Contributor

Jan 22, 2026

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 Sub

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