Excel Formula only displays zero

Excel Formula only displays zero

Didn't find your answer?

I have a spreadsheet where I have input a simple formula where the entry in the said cell (say c18) = the entry in cell C10. Cell C10 contains a formula adding 3 cells together. The result in C10 is correct, and cell C18 SHOULD have the same value in it. Instead the cell has a zero value displayed in it. I currently have the cells formatted as "Accounting", but have tried re-formatting them to "Numbers", only to get a "0" instead of a dash.
I have also tried referring to cell C10 in different cells as oppossed to C18, all giving the same results.
Why does my spreadsheet do this?
Thanks,
Paul
Paul Caddick

Replies (9)

Please login or register to join the discussion.

avatar
By brookfinancial
05th Oct 2007 12:57

Rounding
As mentioned below it could be something to do with the rounding settings of your spreadsheet (if the answer is less than 0.5 - or precision as displayed setting which can be edited through the Tools menu.

If you rule the above out and are happy that you have number formatting as opposed to text the only other thing you could try is Paste Link but of course that might not be appropriate for your spreadsheet. If you always want the cell to link to C10 then Paste Link may be an easy way around it.

Good Luck!

Alison

Thanks (0)
avatar
By ACDWebb
24th Sep 2007 18:01

Difficult to say
Is automatic recalculation switched off?

Have you inadvertantly created a circular formula somewhere

Does the result change to what you expect if you press F9

Thanks (0)
Replying to asillahi:
avatar
By ndbeasle
06th Sep 2013 15:48

Brilliant. Thanks.

Thanks (0)
By David2e
25th Sep 2007 06:03

Don't think I have seen
Hi Paul

I am quite curious on your problem. Although occassionally stumling on unexplained oddities in Excel I really can't think why C18 is not correct.

Even with Alan mentioning automatic calulcation it should still work when you enter the formula, a circular reference should also "work" but with an error... although checking all these things is often a good first step.

The only other thing I would do to test this is take the forumula in C10 and enter it directly in C18 to confirm this works directly itself. I expect it should, if not the problem lies in that cell... if it does the problem is with the C10 =C18. Are you entirely sure the correctly cell is being chosen (ie C18)? Possibly you have it using merge so it could be say cell C16 merged across to appear as C18?

If none of that helps, please feel free to email me any part of your sheet (as long as the error is there) as I am curious as to what else could cause this problem. [email protected]

David Toohey
The Accountants Circle

Thanks (0)
avatar
By listerramjet
25th Sep 2007 08:53

you could try
clearing the cell formatting from all the relevant cells. You don't provide much detail but it may be the formatting is applying rounding to give apparently different answers.

Thanks (0)
avatar
By User deleted
22nd Oct 2007 14:55

Total not displaying properly
I've suffered from the same thing at odd times in the past.

World order is restored and an end to mass panic is obtained by clicking on the offending cell, pressing F2 and then Return. Well most of the time anyway.

Thanks (0)
avatar
By commonesnes
15th Jan 2015 20:15

Thank you!  I am having

Thank you!  I am having similar problems I will try out the recommendations listed in this thread. I've checked the formatting and checked to be sure the formulae work, but I still get 000s when I e.g. add three or four cells the contents of which do not add up to 0.  

Thanks (0)
avatar
By CARyan
25th Jan 2020 06:19

This problem may be solved if you go to Excel Tools Options and enable iterative under formula

At the left bottom of your screen you may be seeing circular reference which is causing the above..

Ryan

Thanks (1)
avatar
By Ankur Gupta
11th Apr 2020 19:18

Dear all,

I see one major reason and perhaps one related reason. My examples and my observations:
1. i was editing a very old excel file with lots & lots of data. As i was summing up few items, result showed 0 instead. I notice the sum formula was auto modified to consider itself in the range of sum. i.e. sum cell address is L10 and summing L1 to L9 but, as i strike enter, it automatically took L1 to L10 and showed 0. I think, first save the file. Then, check formula and correct it L1 to L9 and enter. It showed correct result. So, reason -circular ref in same formula. I mentioned that my excel is old (not by version but, i am using this specific file for around a year now). My experience is that this problem occurs when file is old and has lots and lots of data with formulae.

2. second instance: i gave L10 address in L 20 but, instead of showing figure in L10, it showed 0. Then, reading views mentioned in this dicscussion above, i checked circular references at other places. Corrected few that i knew and that showed using "Error checking" under 'Formulas'. Still, it didn't resolve. I saved file and rechecked for circular reference. It showed that again, L10 which was sum of L1 to L9 has become L1 to L10 so, circular ref. in L10. I corrected it and L20 correctly showed L10 value.

Thanks (0)