Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    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?
    Attached Images Attached Images

  2. #2
    Super Moderator
    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



  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    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?
    Attached Images Attached Images

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 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

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by altdotcom View Post
    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.

  6. #6
    Super Moderator
    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



  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    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"?

  8. #8
    5 Star Lounger
    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.

  9. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 Posts
    Make sure that setting only applies to one spreadsheet - the advantage of ROUND.

    cheers, Paul

  10. #10
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Slough, Berkshire UK
    Posts
    924
    Thanks
    55
    Thanked 52 Times in 50 Posts
    Quote Originally Posted by altdotcom View Post
    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.
    Clive

    All typing errors are my own work and subject to patents pending. Except errors by the spell checker. And that has its own patients.

  11. #11
    New Lounger
    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.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by Farokh Monajem View Post
    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.

    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    New Lounger
    Join Date
    Feb 2010
    Location
    Florida, USA
    Posts
    21
    Thanks
    2
    Thanked 5 Times in 4 Posts
    Quote Originally Posted by Curiousclive View Post
    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.

  14. #14
    5 Star Lounger
    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))

  15. #15
    New Lounger
    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •