Results 1 to 4 of 4

20021024, 13:54 #1
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Compare Oddity/Rounding (Excel 97SR2)
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

20021024, 14:01 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Compare Oddity/Rounding (Excel 97SR2)
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

20021024, 18:28 #3
 Join Date
 Dec 2000
 Location
 New Hampshire, USA
 Posts
 3,386
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Compare Oddity/Rounding (Excel 97SR2)
To add to what LC stated:
1. Equalit y tests with floatingpoint 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.

20021024, 20:14 #4
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Compare Oddity/Rounding (Excel 97SR2)
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