I have an table of numbers currency (#,###.##) that was exported in Excel with over 6,000 rows. When I add the column of numbers up I get I get something like "65,968.09". However when I wanted to do some comparisons and adding the numbers up a different way I found Excel said the numbers did not match (i.e. a1=b1 returned FALSE).
I then told Excel to display like 20+ digits after the decimal and I got a number like "65,968.08999999910000000000000000". This
The original data was only supposed to deal with dollars and cents, so it should not have anything beyond 2 spots after the decimal. To be sure that was the case I formatted the original data to show 20+ digits after the decimal and everything after the first 2 was zero. I checked it a few different way but it all looks clean.
I noticed that if I did SUMIF() on he same column then added up the totals from 3 types of criteria of the SUMIF I got a different total of the 3 types. In this case "65,968.09000000010000000000"
The three criteria had numbers like:
38,995.71000000010000000000
15,559.50000000000000000000
11,412.88000000000000000000
I now from a rounding and dollars and cents perspective "65,968.08999999910000000000000000" and "65,968.09000000010000000000" are the same.
I have tried adding parts of the number different ways and smaller ranges of the column and unless I get to a small portion of the column then I get variations of the .xxxxxxxxxxxxx type number.
However what is really going on here?
How is Excel adding stuff up that only has 2 digits after the decimal and getting other numbers?
Even if this was right why is adding with sum vs SUMIF adding up all the types of criteria showing two different amounts?
I am seeing this on both Excel 2007 and 2003.
Any thoughts and suggestions would be greatly appreciated.
thanks
Alan
I then told Excel to display like 20+ digits after the decimal and I got a number like "65,968.08999999910000000000000000". This
The original data was only supposed to deal with dollars and cents, so it should not have anything beyond 2 spots after the decimal. To be sure that was the case I formatted the original data to show 20+ digits after the decimal and everything after the first 2 was zero. I checked it a few different way but it all looks clean.
I noticed that if I did SUMIF() on he same column then added up the totals from 3 types of criteria of the SUMIF I got a different total of the 3 types. In this case "65,968.09000000010000000000"
The three criteria had numbers like:
38,995.71000000010000000000
15,559.50000000000000000000
11,412.88000000000000000000
I now from a rounding and dollars and cents perspective "65,968.08999999910000000000000000" and "65,968.09000000010000000000" are the same.
I have tried adding parts of the number different ways and smaller ranges of the column and unless I get to a small portion of the column then I get variations of the .xxxxxxxxxxxxx type number.
However what is really going on here?
How is Excel adding stuff up that only has 2 digits after the decimal and getting other numbers?
Even if this was right why is adding with sum vs SUMIF adding up all the types of criteria showing two different amounts?
I am seeing this on both Excel 2007 and 2003.
Any thoughts and suggestions would be greatly appreciated.
thanks
Alan
