Forum Discussion

Stuart_Miller's avatar
Stuart_Miller
Copper Contributor
Jan 16, 2026

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.

community
excel
Excel on Mac
Formulas and Functions
Macros and VBA
office 365

1 Reply

  • NikolinoDE's avatar
    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.

Resources