# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Formulas Keep Returning a Value of Zero

## jbcoe

Someone in our office has an interesting condition that I cannot seem to solve. Whenever they type a simple formula in their spreadsheet, the value keeps returning a 0. When you type something like 10/2 the value returns as 5. Once the = sign is used (=10/2), the value is 0.  This also happens when we try to reference other cells (i.e. =k12/1500 will also return a value of 0.)

I suspect it is a formatting issue, but I am not seeing it. From what I can tell, text cells are formatted as "General" and numbers are formatted as numbers.

Thanks

----------


## NBVC

Try going to Tools|Options and in the Transition tab, make sure all the checkboxes are unchecked.  Does that change/fix anything?

----------


## jbcoe

No Luck. We are still getting the same results. Thanks for the suggestion.

Would importing data from somewhere else cause Excel to behave this way? It was explained to me that data from our accounting software was imported into the spreadsheet. I do not see any evidence that this has caused a problem anywhere else though.

----------


## jbcoe

Although we did not "solve" the problem, it seems to be working as expected. We closed Excel and opened the file again and the issue seemed to resolve itself. Thanks for your help.

----------


## McFarvo

I had this same problem in Excel 2007! I have no idea why it started, but no matter the formula, when I pressed Enter on that cell, it returned a "0" which was very frustrating. 

The extremely tedious work-around (and the only solution that worked for me) was to go to 

Excel Options
 Formulas
  Calculation options
   Workbook Calculation
    Automatic

changed from "Automatic" to "Manual"

Now, I get the correct answers for formulas only if I click into the formula bar and press enter OR press F9 to recalculate all fields. I tried re-opening the file, but Automatic workbook calc still messes everything up, giving me 0s all over the place. 

This seems like a major bug that would probably be fixed in a more recent version of Excel.  :Mad: 

All hail F9 key!

----------


## devansh

I eventually did the same thing McFarvo. I had to re-enter some 20-odd formulae to reach my final result. 
I tried restarting my MS office, and even computer yesterday jbcoe. It didn't work. But today's a new day and the same sheet, the same formulae, all are updating automatically. This isn't the first time!  Crazy bug (Excel 2010)!

----------


## FDibbins

devansh thanks for the update  :Smilie: 

(Even though this thread is over 5 years old, all feedback is welcome)

----------


## nishkiara

I found that this was happening when I had a circular reference. It did not resolve until I fixed the error in the problem cell. Look at the status bar at the bottom of the sheet and it'll tell you where the issue lies. Fix it and VIOLA, no more 0.

----------


## nanioc

> I found that this was happening when I had a circular reference. It did not resolve until I fixed the error in the problem cell. Look at the status bar at the bottom of the sheet and it'll tell you where the issue lies. Fix it and VIOLA, no more 0.



Hey Nishkiara! Thanks! I followed your tip and work on my sheet too. After fixing the circular references my formulas star to work again! Thank you again!!!

----------


## TaraUm

> I found that this was happening when I had a circular reference. It did not resolve until I fixed the error in the problem cell. Look at the status bar at the bottom of the sheet and it'll tell you where the issue lies. Fix it and VIOLA, no more 0.



Thank you. You save my life.

----------


## 1oduro

> Hey Nishkiara! Thanks! I followed your tip and work on my sheet too. After fixing the circular references my formulas star to work again! Thank you again!!!



I would have kissed you if I could.
Circular cell referencing error nearly marred my 3 years' worth of data. You're a saviour.
Office 2021 and this still works flawlessly
Thank you and God really bless you.

----------


## TryingtoHelp

I've had this problem, and the issue was a merged cell. I'd entered = and then clicked on a merged cell on a different worksheet, and it was returning 0. 

The formula read something like ='Sheet 1'!A1:A5. The solution was to just edit so that it didn't reference the whole range of merged cells, just one cell. So instead, it'd be something like ='Sheet 1'!A1.

----------


## FDibbins

Another instance showing that merged cells are the devils work and should be avoided at all cost.

----------


## BenRich

Dear All
I have followed all the detail in the threads and still come up with Zero's
It only occurs when I download a document from the software we are using
Help!!!
Cheers BenRich

----------


## AliGW

*Administrative Note:*

Welcome to the forum.  :Smilie: 

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

----------

