Share this content
0
100161

Excel Formula only displays zero

Excel Formula only displays zero

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

Please login or register to join the discussion.

avatar
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)
avatar
to asillahi
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
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 Anonymous
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
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)
Share this content