Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare Oddity/Rounding (Excel 97-SR2)

    This is a rhetorical question/observation. (also applies to XL2K SR1). I'm restructuring a spreadsheet and wanted to double check that some relocated/reworked formulas produced the same answers as their previous counterparts. So, I put a simple IF(Old<>New,"Error!","OK") check alongside the new calcs. All bar 2 come up ok. I know it's an accuracy/rounding issue.

    =SUM(lstPeopleTandSVar) equals 70.07965066

    =SUM(D74D74) equals 70.07965066

    =IF(ROUND(SUM(lstPeopleTandSVar),2)<>ROUND(SUM(D74D74),2),"Error!","OK") equals "OK"

    =IFSUM(lstPeopleTandSVar)<>SUM(D74D74),"Error!","OK") equals "Error!"

    Why do I need to apply the ROUND() in order to get just 2 out of 30 similar comparisons to display the correct result? Just checked, ROUNDing to 11 places compares OK, 12+ is where the difference comes in. As the underlying formulas for the two arrays being compared are subtly different it's not really a surprise that the answers are also subtley different. Though in only 2 out of 30 cases (3150 cells) seem like pot luck!

    As I say, it's a rhetorical question..

    Regards
    Peter

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare Oddity/Rounding (Excel 97-SR2)

    Converting decimal numbers to binary floating point, doing calculations, and then converting them back to decimal for display can produce rounding errors. It all depends on what the values are (integers and some fractional values do convert and calculate exactly) and what the calculations are as to which will and will not produce the rounding errors.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare Oddity/Rounding (Excel 97-SR2)

    To add to what LC stated:

    1. Equalit y tests with floating-point numbers is an unreliable method.
    2. When you insert the character representation of a number, it is inlikely that will equal what you believe to be a mathematically equivalent result computed on a computer.

    For the test to "work", you'll either haver to test for equality withing some tolerance, or you will need to round each number to the same number of digits and base the comparison on the rounded result. In either case, this is not a TRUE test of equality.

    Unless all numbers can be represented exactly as powers of 2, you are stuck wit using inexact tests.

  4. #4
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare Oddity/Rounding (Excel 97-SR2)

    I understand the issues with floating point number calculations. I guess what struck me as odd was that out of a set of routines that I think does 5M+ calculations into 3000+ cells that only 2 comparison for a match with the previous method of calculating should show a discrepency. Appreciating now that there can be an issue with such math, I would expect more discrepencies.

    It was a rhetorical question/oberservation. Nonetheless, some very interesting, and clear, insights into why what happens happens. Thank you all.

    Regards
    Peter

Posting Permissions

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