Excel making subtraction errors

Topic

New Reply

This is Excel 2002, SP3. I’ve been using this same spreadsheet for at least the past 10 years. Now, for the first time Excel is making subtraction errors. In the attached screenshot column H is the balance (H2 is selected to show you the formula). There are subtraction errors in rows 4, 6, and 8 (there are five more errors in a column of 49 rows, but you get the idea). What is going on?

Reply | Quote

Replies

Are the figures in Column F calculated?

One explanation that can often explain situations like this is that Excel is performing its calculations on the actual values in the cells, rather than the displayed values.
The displayed value may often be rounded, hiding extra decimal places.

You can go into Excel options..Advanced and turn on “Set Precision as Displayed” .

Reply | Quote

Yes, the values in column F are calculated. The value in F is the value in C x .007 (a screenshot with the formula is attached). If Excel is not rounding up the figures in rows 4 and 6 (2.695), what’s going on in row 8 (6.6325)? It’s giving 198.14 as the result of 204.78 minus 6.63, while in row 10 191.30 minus the same 6.63 equals 184.67?

Reply | Quote

Yes, the values in column F are calculated. The value in F is the value in C x .007 (a screenshot with the formula is attached). If Excel is not rounding up the figures in rows 4 and 6 (2.695), what’s going on in row 8 (6.6325)? It’s giving 198.14 as the result of 204.78 minus 6.63, while in row 10 191.30 minus the same 6.63 equals 184.67?

Is it possible that automatic calculation has been switched off? Try hitting F9 and see if the calculations right themselves.

Reply | Quote

Looks like rounding error to me too. You could use ROUND to return the calculated values to 2 decimal places. This has the advantage of allowing you to follow tax office rules for calculating tax.

cheers, Paul

Reply | Quote

To get a handle on what is happening, set all the cells to display more precision (say 4 dec places). I am confident that you will find that Excel is not getting its arithmetic wrong.

Reply | Quote

I turned on “Set Precision as Displayed” and the numbers started displaying correctly. But why is there a warning when you make this change that “data will permanently lose accuracy”?

Reply | Quote

The warning is because everything that is not shown is lost. For example if your actual data is 1.755, your display would be 1.76. Once you turn on the Precision as Displayed, the actual value becomes 1.76 – an increase of 0.005.

Reply | Quote

Make sure that setting only applies to one spreadsheet – the advantage of ROUND.

cheers, Paul

Reply | Quote

This is Excel 2002, SP3. I’ve been using this same spreadsheet for at least the past 10 years. Now, for the first time Excel is making subtraction errors. In the attached screenshot column H is the balance (H2 is selected to show you the formula). There are subtraction errors in rows 4, 6, and 8 (there are five more errors in a column of 49 rows, but you get the idea). What is going on?

The answer in H2 is perfectly correct as 243.86-5.39 = 238.47. As the formula asked for.

Reply | Quote

The answer in H2 is perfectly correct as 243.86-5.39 = 238.47. As the formula asked for.

The errors start showing up in rows 4 & 6.

Reply | Quote

A couple of points.

The first is that you should refrain from using formulas such as c2=””. Instead start using the function ISBLANK, as in =if(isblank(c2), etc. Far more elegant and accurate.

Type all your functions in lower case. Always. Excel will convert them to upper case when it recognises the function. You will get an error message as in @NAME? if it does not recognise the function. The function you typed in will remain in lower case making it easy to identify the source of the problem. Usually a typo.

Finally, the calculations in your spreadsheet are correct. Remove the formatting from all your cells. You will notice that the numbers are calculated to 4 decimal points and are rounded to 2 as per your formatting. Hence the perceived error.

In general, Excel will calculate numbers up to 15 decimal points. Comparing numbers can therefore lead you to believe that there are errors where there is fact a discrepancy in rounding.

There is no solution to this that I know of. You have functions such as ROUNDUP and ROUNDDOWN that might help. I don’t think it is worth the trouble.

Good luck.

Reply | Quote

The first is that you should refrain from using formulas such as c2=””. Instead start using the function ISBLANK, as in =if(isblank(c2), etc. Far more elegant and accurate.

These two tests are not the same. ISBLANK returns true only if there is nothing at all in the cell. A formula in C2 that returns “” will cause ISBLANK(C2) to return FALSE, whereas the test for C2=”” will catch it.

Reply | Quote

A few questions …..
1) Can you attach a copy of the worksheet?
2) What are the headings for cols F&G?
3) What is col H accumulating?
4) Will this formula work in col F
=IF(C2=””,0,ROUND(C2*0.07,2))

Reply | Quote

Its almost certainly rounding errors. When doing calculations on monetary values it is best to format all cells to a currency format. That will take care of all rounding issues using the standard rounding rules, ie those used in accounting practices.

The reason it just cropped up, is either the values you are putting in this time are just the right combo to trip you up, or it just got overlooked before and now there is more scrutiny, or a combination of both. In short a combination of good old human nature and software that is not quite intuitive enough yet.

So just format columns C, F, and H with a standard currency format and it will all work out for you.

Reply | Quote

So just format columns C, F, and H with a standard currency format and it will all work out for you.

I don’t think that is right. The picture below shows two identical columns of figures. Col A is formatted as Currency, Col B has 3 dec places.

To describe what is happening as Rounding Errors suggests that Excel is getting it wrong. Excel is correctly adding up the figures it is given.

Reply | Quote