Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUM returns wrong result
I am using the SUM function to add positive and negative currency values. The
numbers are 2053.12, -1272.15, -260, -420.97 and -100. Excel returns a result of -0.000000000000227. The sum of the negative numbers is -2053.12 and the positive number is +2053.12 so I was expecting 0.00. I can use the ROUND function to sort this out but I am very interested in knowing what causes this to happen and if there is something I can do so that I can just use the SUM function to return the correct result. -- Thanks, MarkN |
#2
|
|||
|
|||
SUM returns wrong result
Mark,
Take a look at this page http://www.cpearson.com/excel/rounding.htm A good previous thread at http://tinyurl.com/8jjf4 Not directly related, but an interesting thread at http://tinyurl.com/d4475 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... I am using the SUM function to add positive and negative currency values. The numbers are 2053.12, -1272.15, -260, -420.97 and -100. Excel returns a result of -0.000000000000227. The sum of the negative numbers is -2053.12 and the positive number is +2053.12 so I was expecting 0.00. I can use the ROUND function to sort this out but I am very interested in knowing what causes this to happen and if there is something I can do so that I can just use the SUM function to return the correct result. -- Thanks, MarkN |
#3
|
|||
|
|||
SUM returns wrong result
As Bob's links explain, computers do binary math. None of your floating
point numbers have exact binary representations, and hence must be approximated. Approximation to inputs implies that the output is necessarily only approximate. The binary approximation to 2053.12 is smaller than the exact value. The binary approximations to 1272.15 and 420.97 are larger than the exact value. Since only the first one has a positive sign in your calculation, the approximate result will be less than the exact result. Specifically 2053.1199999999998908606357872486114501953125 -1272.15000000000009094947017729282379150390625 -260 -420.970000000000027284841053187847137451171875 -100 ----------------------------------------------- -0.000000000000227373675443232059478759765625 As a practical matter, you don't need to determine the exact binary approximations to predict the accuracy of the final answer. Help for "Specifications" clearly documents Excel's limit of 15 digit accuracy; thus 2053.12000000000??? -1272.15000000000??? -260 -420.970000000000?? -100 ------------------- 0.00000000000??? consistent with actual result -0.000000000000227 This is not just an Excel issue, it is common to all software that does binary math (almost all software), and as Bob's link to Chip Pearson's web site shows, the specific accuracy is determined by the IEEE standard for double precision. Jerry MarkN wrote: I am using the SUM function to add positive and negative currency values. The numbers are 2053.12, -1272.15, -260, -420.97 and -100. Excel returns a result of -0.000000000000227. The sum of the negative numbers is -2053.12 and the positive number is +2053.12 so I was expecting 0.00. I can use the ROUND function to sort this out but I am very interested in knowing what causes this to happen and if there is something I can do so that I can just use the SUM function to return the correct result. |
#4
|
|||
|
|||
SUM returns wrong result
Thanks very much Bob.
-- Cheers, MarkN "Bob Phillips" wrote: Mark, Take a look at this page http://www.cpearson.com/excel/rounding.htm A good previous thread at http://tinyurl.com/8jjf4 Not directly related, but an interesting thread at http://tinyurl.com/d4475 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... I am using the SUM function to add positive and negative currency values. The numbers are 2053.12, -1272.15, -260, -420.97 and -100. Excel returns a result of -0.000000000000227. The sum of the negative numbers is -2053.12 and the positive number is +2053.12 so I was expecting 0.00. I can use the ROUND function to sort this out but I am very interested in knowing what causes this to happen and if there is something I can do so that I can just use the SUM function to return the correct result. -- Thanks, MarkN |
#5
|
|||
|
|||
SUM returns wrong result
Thanks Jerry,
I might not like it, but at least I can understand it! -- Cheers, MarkN "Jerry W. Lewis" wrote: As Bob's links explain, computers do binary math. None of your floating point numbers have exact binary representations, and hence must be approximated. Approximation to inputs implies that the output is necessarily only approximate. The binary approximation to 2053.12 is smaller than the exact value. The binary approximations to 1272.15 and 420.97 are larger than the exact value. Since only the first one has a positive sign in your calculation, the approximate result will be less than the exact result. Specifically 2053.1199999999998908606357872486114501953125 -1272.15000000000009094947017729282379150390625 -260 -420.970000000000027284841053187847137451171875 -100 ----------------------------------------------- -0.000000000000227373675443232059478759765625 As a practical matter, you don't need to determine the exact binary approximations to predict the accuracy of the final answer. Help for "Specifications" clearly documents Excel's limit of 15 digit accuracy; thus 2053.12000000000??? -1272.15000000000??? -260 -420.970000000000?? -100 ------------------- 0.00000000000??? consistent with actual result -0.000000000000227 This is not just an Excel issue, it is common to all software that does binary math (almost all software), and as Bob's link to Chip Pearson's web site shows, the specific accuracy is determined by the IEEE standard for double precision. Jerry MarkN wrote: I am using the SUM function to add positive and negative currency values. The numbers are 2053.12, -1272.15, -260, -420.97 and -100. Excel returns a result of -0.000000000000227. The sum of the negative numbers is -2053.12 and the positive number is +2053.12 so I was expecting 0.00. I can use the ROUND function to sort this out but I am very interested in knowing what causes this to happen and if there is something I can do so that I can just use the SUM function to return the correct result. |
#6
|
|||
|
|||
SUM returns wrong result
You're welcome, glad it helped.
Jerry MarkN wrote: Thanks Jerry, I might not like it, but at least I can understand it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
How can i change this VBA project According to Indian Numeric | Excel Discussion (Misc queries) | |||
Conversion | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |