Forum Discussion
ms office formula gives 0
i have been using lots of time on my MS Office Excel. Every time i wanted to get the sum ( =sum() ), it always gave me 0 as the answer and i did not know how to solve this. I have Microsoft 365 subscription and windows 11 by the way..
please help me..i am doing my tax and can't move forward due to this.
jenn12 wrote: ``all data are set to numbers. [....] never had issues with that format``
As I said: the format of the cell does not matter; and looks can be deceiving.
Did you try formulas of the form =ISNUMBER(A1) for all of the "numbers"?
One thought: did you try (re)entering one of the "numbers" manually?
(Be sure the cell format is General or another numeric format first.)
-----
jenn12 wrote: ``circular reference is not highlighted``
Not sure what you mean by that. The circ ref notification might not appear in the lower left of the status bar (bottom of display). But you might still have circ refs somewhere -- perhaps not the active worksheet.
Unfortunately, you have go to each worksheet one-by-one and click Formulas > Error Checking > Circular References (if not grayed out).
-----
jenn12 wrote: ``tried both manual and automatic``
Another very unlikely possibility: EnableCalculation for the worksheet(s) is disable.
If that were the case, toggling Automatic Calculation on and pressing any of the f9 shortcuts do not work.
That state is controlled with VBA code. But you might have a defective add-in that is leaving that state set.
If you know how to use VBA, you can go into the Immediate Window and type
? ActiveSheet.EnableCalculation
to see the current state.
-----
jenn12 wrote: ``I am honestly going bananas``
Understandable.
If all else fails, the best and quickest way to get help is to attach an example Excel file (redacted) that demonstrates the problem.
I know: there are reasons why you might balk; and there are reasons why you are not allowed to.
But if you simply copy and paste-value the "numbers", enter the SUM formula, and strip away everything else, I don't see any risk.
7 Replies
Something that worked for me:
- Open File > Options.
- Select Formulas.
- Under Calculation options, check Enable iterative calculation.
- Configure the maximum iterations and maximum change, and apply the settings.
These settings are available on the Desktop version of Excel. I was going nuts as I was unable to find it in the web version. So, I just downloaded the copy of the file, made that change and reuploaded the file in the online Sharepoint. After that the sum started working correctly in the web version as well.
Hope this helps! :)
- It sounds like there might be a formula error causing Excel to return 0 instead of the sum. Make sure you're selecting the correct range of cells within the parentheses of the SUM function. If the issue persists, try restarting Excel or your computer. If you're still having trouble, consider reaching out to Microsoft Support for assistance, as you have a Microsoft 365 subscription.
All questions I've seen about SUM which returns zero were related to texts in the range. IMHO, reboot computer and call support will be wasting of time, it's faster to read what Excel says
jenn12 wrote: ``sum ( =sum() ), it always gave me 0``
Three most common reasons (more to less):
1. All of the "numbers" are actual text.
2. You have Manual calculation mode set instead of Automatic calculation.
3. You have circular references somewhere, perhaps not even in related cells.
#3 is the hardest one to find. So save that for last.
Re #1: Looks can be deceiving, and the format of the cells does not matter. Use formulas of the form =ISNUMBER(A1) to confirm (or deny) that all of the data are truly numeric. Probably not.
Re #2: The navigation to the feature depends on the Excel verion. In my version, I click Formulas > Calculation Options on the "ribbon" (top).
- 1. all data are set to numbers. Usually, it is set to standard and never had issues with that format.
2. i tried both manual and automatic - nothing works.
3. circular reference is not highlighted so I can't use that as an option.
4. i updated all my drivers and installed all new updates, cleaned my computer still the same..
Excel version: i believe i have the updated version as i have a monthly subscription and have been using 365 since 2016. This is the first time, i got issues.
I am honestly going bananas trying to crack my few brain cells out to fix this...maybe i let my computer sleep overnight and try again tomorrowjenn12 wrote: ``all data are set to numbers. [....] never had issues with that format``
As I said: the format of the cell does not matter; and looks can be deceiving.
Did you try formulas of the form =ISNUMBER(A1) for all of the "numbers"?
One thought: did you try (re)entering one of the "numbers" manually?
(Be sure the cell format is General or another numeric format first.)
-----
jenn12 wrote: ``circular reference is not highlighted``
Not sure what you mean by that. The circ ref notification might not appear in the lower left of the status bar (bottom of display). But you might still have circ refs somewhere -- perhaps not the active worksheet.
Unfortunately, you have go to each worksheet one-by-one and click Formulas > Error Checking > Circular References (if not grayed out).
-----
jenn12 wrote: ``tried both manual and automatic``
Another very unlikely possibility: EnableCalculation for the worksheet(s) is disable.
If that were the case, toggling Automatic Calculation on and pressing any of the f9 shortcuts do not work.
That state is controlled with VBA code. But you might have a defective add-in that is leaving that state set.
If you know how to use VBA, you can go into the Immediate Window and type
? ActiveSheet.EnableCalculation
to see the current state.
-----
jenn12 wrote: ``I am honestly going bananas``
Understandable.
If all else fails, the best and quickest way to get help is to attach an example Excel file (redacted) that demonstrates the problem.
I know: there are reasons why you might balk; and there are reasons why you are not allowed to.
But if you simply copy and paste-value the "numbers", enter the SUM formula, and strip away everything else, I don't see any risk.
