Arithmetic problems with Excel 2010

  u123935 16:13 10 Oct 2017
Locked

I have a spreadsheet containing several worksheets which feed into a summary sheet. On the summary sheet, one cell is causing me problems as follows :-

cell S39 = SUM (S36:S38) where S36 = £422.78, S37 = £444.76 and S38 = blank. All those cells are formatted as Currency with two decimal places. The result is a blank cell, also formatted as Currency. I have tried setting and unsetting "set precision as displayed", but with the same answer. The galling thing is, adjacent cells give the correct result!!! It's driving me mad. Can anyone help?

  compumac 16:34 10 Oct 2017

Try copying the format from a cell that does display as required, to the cell that needs the correct format. Try that for a starter.

  wee eddie 17:20 10 Oct 2017

Add 0.00 to Cell S38

  lotvic 18:55 10 Oct 2017

A word of caution:

Excel: The dangers of “Set precision as displayed” click here

If you turn on this feature after you have already built a spreadsheet, your spreadsheet will permanently lose all accuracy.

  u123935 21:28 10 Oct 2017

Thanks Guys. Tried all three and still same result. Shall I call in Sherlock?

  compumac 21:32 10 Oct 2017

You say the spreadsheet contains several worksheets. - On the summary sheet where does S38 get its input from? Could that source be the problem?

  exdragon 08:38 11 Oct 2017

I've just tried it and it seems to be ok. Is it worth inserting another row above the summary sheet (or the 'feed in' sheet), copying the original row's figures into it and then deleting the original row? Of course, this may not be possible on your particular workbook. There may be something odd lurking in cell S38.

  u123935 20:11 11 Oct 2017

Thanks. Tried that but with same result. I'll try inserting a new column to see if that solves it.

  Simsy 22:55 16 Oct 2017

long shot here... What colour is the text in the offending cell? Any chance that it's somehow been formatted as the same colour as the background colour?

Regards,

Simsy

  Fermat's Theorem 00:15 23 Oct 2017

Hi.. I notice that in your query you left a space after the SUM and before the first bracket. If you did so in the spreadsheet, the function wouldn’t work. Just a thought �/p>

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Last Chance: Win an Honor Band 5

Acclaimed illustrator James Jean's comic book past gets celebrated in new book

Best Apple tech gifts for Christmas 2019

Noël (2019) : idées cadeaux & guide d'achat !