All, I attach (I hope) a very simple petty cash spreadsheet. All entries are either values in GBP and pence - ie only to two places; or additions and subtractions, which should, logically, be limited to two places. None of the entries are caused by division (or multiplication), which could cause values beyond the two places.


I have expanded the column width to show many places of decimals. Can anyone explain why Excel gets its sums wrong? (And then appears to correct itself.) And what can I do about it?
Thanks
Richard
0 Petty Cash 2015 Mk II.xlsx

This has probably to do with floating point arithmetic used by Excel. See https://support.microsoft.com/en-us/kb/78113/ for explanations and workarounds

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks, Pecoflyer. I have looked at the link.
Although the explanation is clear the fixes are either inaccurate or inelegant. But here is a curiosity: if you take an empty cell and enter =17.73-25.00 you get -7.27, which is correct.
So the link explanation does not really solve the problem of why in my spreadsheet G18-F19 gives the wrong result in G19. In neither G18 or F19 are there any floating point errors, and Excel has shown it is perfectly capable of doing the calculation correctly!
Richard

This is both annoying and alarming, 'eh?

I removed all the conditional formatting from the sheet.

Would you try that?
For me, the results were that only G10, G16, and G21, had extra wrong bits added.

Doesn't your original have more cells that than with wrong values? I think G30 was wrong when I first looked at the sheet with conditional formatting.

Makes no sense to me, I've seen plenty of simple formulas work just fine, although I'm using USD and not GBP.

What happens if you re-do the sheet using USD? With or without conditional formatting?

Also try creating a new workbook, start fresh.
Also check that you don't have any circular references floating about in any open workbook.

(I'm using MS Office Professional Plus 10)

Thanks, Clancy

I have looked at the spreadsheet and realise that the IFs are unnecessary: so I have removed them. I have no idea why I put them in in the first place.
However, it makes no difference. Nor does changing currency or to number. The calculation is the calculation: the currency plays no part and is only an add-on (probably a bit of coding that concatenates the symbol and the arithmetic answer).

So I am still hoping for ideas.

Thanks

Richard

I had inadvertently changed your G5 formula, so the results for the following rows in G, changed. So removing the conditional formatting wasn't the problem Sooo I haven't got a clue, just worry about Excel's math.

Would love to see you test this in USD.