SOLVED

AVERAGE or SUM Formula returning 0

Copper Contributor

Hi,

I have Excel on Mac and been struggling with a simple AVERAGE and SUM formula which keeps returning 0. I checked the data types, checked for texts with no luck. I notice the formula builder returns the correct answer but the cell just returns 0.

Please help, currently using it to study Data analytics and this is making it really frustrating.

6 Replies

@DoubleLLooms 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@HansVogelaar 

 

thanks for reaching out.

its actually a publically available spreadsheet from a YouTube channel so its fine.

i do wonder if it could be a local setting causing the issue.

so in the spreadsheet, theres the main 'Data' sheet and then a Sheet2 where i was hoping to practice in but not getting anywhere when simple formulas aren't working. i have copied it into my onedrive and the link below.

 

spreadsheet in onedrive 

@DoubleLLooms 

This is what I see when I open the workbook:

HansVogelaar_0-1720367324149.png

Does the problem persist if you download the workbook and open it?

@HansVogelaar

i saw this too.. on excel online. its the exact same workbook i have locally.. could it be something in the excel setting on my mac then?
best response confirmed by DoubleLLooms (Copper Contributor)
Solution

@DoubleLLooms Make sure that Calculation Options on the Formulas tab of the ribbon is set to Automatic.

@HansVogelaar 

you're a LEGEND! it was set to "automatic" but when i changed it to "automatic except for data tables" the formula results appeared :) - dont know why considering the raw data is in a table.

thank you. 

 

 

 

1 best response

Accepted Solutions
best response confirmed by DoubleLLooms (Copper Contributor)
Solution

@DoubleLLooms Make sure that Calculation Options on the Formulas tab of the ribbon is set to Automatic.

View solution in original post