Results 1 to 15 of 16
Thread: Excel making subtraction errors

20100429, 00:58 #1
 Join Date
 Mar 2001
 Posts
 167
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20100429, 01:37 #2
 Join Date
 Jun 2002
 Location
 Mt Macedon, Victoria, Australia
 Posts
 3,993
 Thanks
 1
 Thanked 45 Times in 44 Posts
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" .Regards
John

20100429, 12:42 #3
 Join Date
 Mar 2001
 Posts
 167
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20100429, 12:43 #4
 Join Date
 Dec 2009
 Location
 Earth
 Posts
 8,953
 Thanks
 62
 Thanked 1,104 Times in 1,027 Posts
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

20100429, 12:50 #5
 Join Date
 Feb 2003
 Location
 Runcorn, Cheshire, United Kingdom
 Posts
 372
 Thanks
 0
 Thanked 2 Times in 2 Posts

20100429, 17:08 #6
 Join Date
 Jun 2002
 Location
 Mt Macedon, Victoria, Australia
 Posts
 3,993
 Thanks
 1
 Thanked 45 Times in 44 Posts
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.
Regards
John

20100429, 22:14 #7
 Join Date
 Mar 2001
 Posts
 167
 Thanks
 0
 Thanked 0 Times in 0 Posts
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"?

20100429, 22:33 #8
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20100430, 11:33 #9
 Join Date
 Dec 2009
 Location
 Earth
 Posts
 8,953
 Thanks
 62
 Thanked 1,104 Times in 1,027 Posts
Make sure that setting only applies to one spreadsheet  the advantage of ROUND.
cheers, Paul

20100506, 05:11 #10
 Join Date
 Dec 2009
 Location
 Slough, Berkshire UK
 Posts
 933
 Thanks
 67
 Thanked 56 Times in 52 Posts

20100506, 05:50 #11
 Join Date
 May 2010
 Location
 Toronto, ON, Canada
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20100506, 06:04 #12

20100506, 08:19 #13
 Join Date
 Feb 2010
 Location
 Florida, USA
 Posts
 21
 Thanks
 2
 Thanked 5 Times in 4 Posts

20100506, 10:35 #14
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
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))

20100510, 15:03 #15
 Join Date
 Dec 2009
 Location
 St. Louis
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.