In doing annual accounts I want to subtract the result (in £) from one cell from another cell (also in £) on the same sheet. I keep getting a consistent error of 0.01 whichever way I try to write the formula, e.g. =SUM(D3-D5) or =SUM(D3:D4-E5) or =SUM(Earnings.E4-B5) or =SUM(Earnings.E4-D5) or =SUM(Earnings.E4)-E5.
Addition works OK, it’s only subtraction that produces an error of 0.01
Can you help please?
What should all that mean and what do you want to calculate? None of your formulas seem to make a lot of sense though some may be syntactically correct. Please describe the values you in which cells and which one you want to subtract from each other. I doesn’t make a lot of sense to provide a number of formulas, telling they don’t work,l if none had an idea what calculation should be performed.
=SUM(D3-D5): syntactically correct but D3-D5 is the way you would do that difference
=SUM(D3:D4-E5) is an invalid formula (Wrong data type error)
=SUM(Earnings.E4-B5) syntactically correct but =Earnings.E4-B5 is the way you would do that difference (same for the same structured other formulas)
=SUM(Earnings.E4)-E5 again is =Earnings.E4-E5
Why do you use the SUM() function here?
What you want to achieve obviously is done by = D3-D5 and the =SUM(D3:D4-E5) seems to not be related to the question understandably. .
By a probability of about 100% your issue is due to having values in your cells calculated in one or another way to a higher precision than the one used by the NumberFormat for the display.
This often occurs specifically with currency formats being set to 2 decimal places by default. Somewhere you may have calculated a value like D3 = K3 + L3 *17%. …
Set the afflicted operand cells to a neutral format with, say, 5 decimal places. The difference should show then and make clear the results that were against your expectations.
Generally: If you calculate something for subsequent use in financial calculations you should consider the needs of rounding explicitly. It isn’t done by formatting.
My unsolicited advice: Never use ‘Precision as shown’. It works for the complete document and you hand over control over things needing considerations to automatisms in the dark the concepts of which you won’t know precisely. What about values formatted to fractions? What about date/time formats? What about the next version probably having changed some details insofar? To know is better than to hit a few keys and then hope it comes out as desired.
Exchange rates, e.g. ar commonly given with 5 decimal places. You may inadvertently have set a cell containing such a value to only 3 places. This might be relevant …
Likely you have some rounding error in cells you try to SUM that individually don’t show up if formatted with two decimals but add up to the total sum and then propagate. Good practice is to round interim calculation results to the desired decimals before summing them (i.e. use ROUND(...;2) on interim calculations). If the involved cells are displayed with consistent decimal formatting you can also try to activate Tools → Options → Calc → Calculate, General Calculations, Precision as shown. That may help in some cases but also slows things down in large spreadsheets. The better approach is to round interim calculations, which helps in most situations.
See 0.1 + 0.2 ≠ 0.3 for technical details.
Btw, note that =SUM(D3-D5) is superfluous and instead can be written as =D3-D5