If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




subtraction returning incorrect calue
We have a spreadsheet that subtracts one number from another. We did not
understand why Excel is rounding the way it did so we decided to add decimal places until we see the value change. For instance, one of our number was going out to x,xxx.499999999999999999999999999999900000 while the other number was x,xxx.5000000000000000000000000000000000. When we rounded the numbers, one rounded up while the other rounded down. This is fine. Now that we fixed the formula to TRUNC instead of ROUND, I thought the issue would go away but now when I subtracted two numbers that were like x,xxx.xx000000000000000000000000 and another number that is also x,xxx.xx000000000000000000000000, why would I receive an answer that comes back as x,xxx.xx4999999999900000000000000000? This doesn't make sense to me. Has anyone else run into this issue? How did you fix it?  Ashish 
Ads 
#2




subtraction returning incorrect calue
"Ashish_Vaidya" wrote:
when I subtracted two numbers [...], why would I receive an answer that comes back as x,xxx.xx4999999999900000000000000000? FYI, it does not make sense to format more 15 "significant digits"  that is, 15 digits starting with the first nonzero digit on the left. Has anyone else run into this issue? How did you fix it? Yes, this is very common. "Everyone" runs into this issue at some point. The short answer is: always explicitly round expressions that involve values that might have decimal fractions. Typically, use the ROUND function. For example, IF(10.110=0.1,TRUE) returns FALSE(!). But IF(ROUND(10.110),1)=0.1,TRUE) return TRUE, as expected. (Alternatively, you might use the Precision As Displayed calculation. But that can result in surprising results. If you choose to try PAD, be sure to make a backup copy of your Excel file first. However, note that PAD will not help in the example above.) The longer answer can become quite involved. In short, Excel stores numbers and performs arithmetic using a standard form called (IEEE 754) 64bit floating point. For overwhelming details, see http://support.microsoft.com/kb/78113. But the point to note is: most decimal fractions cannot be represented exactly. Instead, they are approximated by the sum of 53 consecutive powers of two ("bits"). Sometimes this results in different representations for the same decimal fraction. For example, 10.1 is represented internally as exactly 10.0999999999999,996447286321199499070644378662109 375, whereas 0.1 is exactly 0.100000000000000,00555111512312578270211815834045 41015625. The whole thing is complicatd by the fact that Excel has implemented some heuristics to try to mitigate the effects of these differences. Unfortunately, the Excel implementation is halfbaked. It leads to even more confusion when, for example, subtractiing some numbers results in exactly zero, but subtracting other "similar" numbers does not. Moreover, the halfbaked implementation can have "impossible" results; for example IF(A1=A2,TRUE) might return TRUE, but IF(A1A2=0,TRUE) might not.  original message  "Ashish_Vaidya" wrote: We have a spreadsheet that subtracts one number from another. We did not understand why Excel is rounding the way it did so we decided to add decimal places until we see the value change. For instance, one of our number was going out to x,xxx.499999999999999999999999999999900000 while the other number was x,xxx.5000000000000000000000000000000000. When we rounded the numbers, one rounded up while the other rounded down. This is fine. Now that we fixed the formula to TRUNC instead of ROUND, I thought the issue would go away but now when I subtracted two numbers that were like x,xxx.xx000000000000000000000000 and another number that is also x,xxx.xx000000000000000000000000, why would I receive an answer that comes back as x,xxx.xx4999999999900000000000000000? This doesn't make sense to me. Has anyone else run into this issue? How did you fix it?  Ashish 
#3




subtraction returning incorrect calue
"Joe User" wrote:
the Excel implementation is halfbaked. It leads to even more confusion Although I do not like this Excel heuristic, I do not want to leave you with the impression that Excel has a lock on confusing behaviors. The operative word is "more" confusion. For example, because of the nature of 64bit floating bit arithmetic, the order of operations can lead to different results. For example, 10.1  10  0.1 results in an infinitesimal number (about 3.6E16), whereas 10.1  0.1  10 results in exactly zero. All the more reason to rely on some form of explicit rounding, be it the ROUND function (my preference) or the Precision As Displayed calculation option. The advantage of PAD is that it is pervasive; no need to for you to remember to use ROUND. The disadvantage of PAD is that is pervasive ;). It might apply to formulas where you prefer not to round. For example, typically I do not round intermediate amortization schedule formulas; otherwise, the quantization error results in apparent errors. I only round the actual payment, since that is constrained by realworld requirements. (You can pay fractional cents.) And note that PAD can have deleterious and irreversible effects on constants, e.g. periodic interest rates, that you might want to display with limited decimal places, but you want the value to remain "exact". (That is, as "exact" as 64bit floating point permits.)  original message  "Joe User" wrote: "Ashish_Vaidya" wrote: when I subtracted two numbers [...], why would I receive an answer that comes back as x,xxx.xx4999999999900000000000000000? FYI, it does not make sense to format more 15 "significant digits"  that is, 15 digits starting with the first nonzero digit on the left. Has anyone else run into this issue? How did you fix it? Yes, this is very common. "Everyone" runs into this issue at some point. The short answer is: always explicitly round expressions that involve values that might have decimal fractions. Typically, use the ROUND function. For example, IF(10.110=0.1,TRUE) returns FALSE(!). But IF(ROUND(10.110),1)=0.1,TRUE) return TRUE, as expected. (Alternatively, you might use the Precision As Displayed calculation. But that can result in surprising results. If you choose to try PAD, be sure to make a backup copy of your Excel file first. However, note that PAD will not help in the example above.) The longer answer can become quite involved. In short, Excel stores numbers and performs arithmetic using a standard form called (IEEE 754) 64bit floating point. For overwhelming details, see http://support.microsoft.com/kb/78113. But the point to note is: most decimal fractions cannot be represented exactly. Instead, they are approximated by the sum of 53 consecutive powers of two ("bits"). Sometimes this results in different representations for the same decimal fraction. For example, 10.1 is represented internally as exactly 10.0999999999999,996447286321199499070644378662109 375, whereas 0.1 is exactly 0.100000000000000,00555111512312578270211815834045 41015625. The whole thing is complicatd by the fact that Excel has implemented some heuristics to try to mitigate the effects of these differences. Unfortunately, the Excel implementation is halfbaked. It leads to even more confusion when, for example, subtractiing some numbers results in exactly zero, but subtracting other "similar" numbers does not. Moreover, the halfbaked implementation can have "impossible" results; for example IF(A1=A2,TRUE) might return TRUE, but IF(A1A2=0,TRUE) might not.  original message  "Ashish_Vaidya" wrote: We have a spreadsheet that subtracts one number from another. We did not understand why Excel is rounding the way it did so we decided to add decimal places until we see the value change. For instance, one of our number was going out to x,xxx.499999999999999999999999999999900000 while the other number was x,xxx.5000000000000000000000000000000000. When we rounded the numbers, one rounded up while the other rounded down. This is fine. Now that we fixed the formula to TRUNC instead of ROUND, I thought the issue would go away but now when I subtracted two numbers that were like x,xxx.xx000000000000000000000000 and another number that is also x,xxx.xx000000000000000000000000, why would I receive an answer that comes back as x,xxx.xx4999999999900000000000000000? This doesn't make sense to me. Has anyone else run into this issue? How did you fix it?  Ashish 
#4




subtraction returning incorrect calue
While I understand the nature of fitting a product's capabilities to a wide
variety of users and their particular needs, it becomes terribly inefficient to try and rewrite all of my formulas to a ROUND function due to this phenomena. Thank you for your answer as it answers my question, yet unsatisfied with the solution.  Ashish "Joe User" wrote: "Ashish_Vaidya" wrote: when I subtracted two numbers [...], why would I receive an answer that comes back as x,xxx.xx4999999999900000000000000000? FYI, it does not make sense to format more 15 "significant digits"  that is, 15 digits starting with the first nonzero digit on the left. Has anyone else run into this issue? How did you fix it? Yes, this is very common. "Everyone" runs into this issue at some point. The short answer is: always explicitly round expressions that involve values that might have decimal fractions. Typically, use the ROUND function. For example, IF(10.110=0.1,TRUE) returns FALSE(!). But IF(ROUND(10.110),1)=0.1,TRUE) return TRUE, as expected. (Alternatively, you might use the Precision As Displayed calculation. But that can result in surprising results. If you choose to try PAD, be sure to make a backup copy of your Excel file first. However, note that PAD will not help in the example above.) The longer answer can become quite involved. In short, Excel stores numbers and performs arithmetic using a standard form called (IEEE 754) 64bit floating point. For overwhelming details, see http://support.microsoft.com/kb/78113. But the point to note is: most decimal fractions cannot be represented exactly. Instead, they are approximated by the sum of 53 consecutive powers of two ("bits"). Sometimes this results in different representations for the same decimal fraction. For example, 10.1 is represented internally as exactly 10.0999999999999,996447286321199499070644378662109 375, whereas 0.1 is exactly 0.100000000000000,00555111512312578270211815834045 41015625. The whole thing is complicatd by the fact that Excel has implemented some heuristics to try to mitigate the effects of these differences. Unfortunately, the Excel implementation is halfbaked. It leads to even more confusion when, for example, subtractiing some numbers results in exactly zero, but subtracting other "similar" numbers does not. Moreover, the halfbaked implementation can have "impossible" results; for example IF(A1=A2,TRUE) might return TRUE, but IF(A1A2=0,TRUE) might not.  original message  "Ashish_Vaidya" wrote: We have a spreadsheet that subtracts one number from another. We did not understand why Excel is rounding the way it did so we decided to add decimal places until we see the value change. For instance, one of our number was going out to x,xxx.499999999999999999999999999999900000 while the other number was x,xxx.5000000000000000000000000000000000. When we rounded the numbers, one rounded up while the other rounded down. This is fine. Now that we fixed the formula to TRUNC instead of ROUND, I thought the issue would go away but now when I subtracted two numbers that were like x,xxx.xx000000000000000000000000 and another number that is also x,xxx.xx000000000000000000000000, why would I receive an answer that comes back as x,xxx.xx4999999999900000000000000000? This doesn't make sense to me. Has anyone else run into this issue? How did you fix it?  Ashish 
#5




subtraction returning incorrect calue
"Ashish_Vaidya" wrote:
While I understand the nature of fitting a product's capabilities to a wide variety of users and their particular needs Actually, this problem arises for the lack of that, IMHO. unsatisfied with the solution. Then perhaps the Precision As Displayed option is the right thing for you. You will need to format all cells whose values that you want rounded to some numeric format that specifies the number of decimal places, e.g. Number or Accounting. You might have that already. Also, you might want to format all cells whose values that you do __not__ want rounded to General. For example, typically we want annual interest rates and periodic rates derived from them to be "exact" (to the degree permitted by the 64bit floating point form). Of course, that means you lose control over the appearance of the cell. Then you might need ROUND only in formulas where you compare expression, like IF(A1+B1 = C1D1, ..., ...). That should be IF(ROUND(A1+B1,2) = ROUND(C1D1,2), ..., ...). Or you could just wait and see if a problem arises ;).  original message  "Ashish_Vaidya" wrote: While I understand the nature of fitting a product's capabilities to a wide variety of users and their particular needs, it becomes terribly inefficient to try and rewrite all of my formulas to a ROUND function due to this phenomena. Thank you for your answer as it answers my question, yet unsatisfied with the solution.  Ashish "Joe User" wrote: "Ashish_Vaidya" wrote: when I subtracted two numbers [...], why would I receive an answer that comes back as x,xxx.xx4999999999900000000000000000? FYI, it does not make sense to format more 15 "significant digits"  that is, 15 digits starting with the first nonzero digit on the left. Has anyone else run into this issue? How did you fix it? Yes, this is very common. "Everyone" runs into this issue at some point. The short answer is: always explicitly round expressions that involve values that might have decimal fractions. Typically, use the ROUND function. For example, IF(10.110=0.1,TRUE) returns FALSE(!). But IF(ROUND(10.110),1)=0.1,TRUE) return TRUE, as expected. (Alternatively, you might use the Precision As Displayed calculation. But that can result in surprising results. If you choose to try PAD, be sure to make a backup copy of your Excel file first. However, note that PAD will not help in the example above.) The longer answer can become quite involved. In short, Excel stores numbers and performs arithmetic using a standard form called (IEEE 754) 64bit floating point. For overwhelming details, see http://support.microsoft.com/kb/78113. But the point to note is: most decimal fractions cannot be represented exactly. Instead, they are approximated by the sum of 53 consecutive powers of two ("bits"). Sometimes this results in different representations for the same decimal fraction. For example, 10.1 is represented internally as exactly 10.0999999999999,996447286321199499070644378662109 375, whereas 0.1 is exactly 0.100000000000000,00555111512312578270211815834045 41015625. The whole thing is complicatd by the fact that Excel has implemented some heuristics to try to mitigate the effects of these differences. Unfortunately, the Excel implementation is halfbaked. It leads to even more confusion when, for example, subtractiing some numbers results in exactly zero, but subtracting other "similar" numbers does not. Moreover, the halfbaked implementation can have "impossible" results; for example IF(A1=A2,TRUE) might return TRUE, but IF(A1A2=0,TRUE) might not.  original message  "Ashish_Vaidya" wrote: We have a spreadsheet that subtracts one number from another. We did not understand why Excel is rounding the way it did so we decided to add decimal places until we see the value change. For instance, one of our number was going out to x,xxx.499999999999999999999999999999900000 while the other number was x,xxx.5000000000000000000000000000000000. When we rounded the numbers, one rounded up while the other rounded down. This is fine. Now that we fixed the formula to TRUNC instead of ROUND, I thought the issue would go away but now when I subtracted two numbers that were like x,xxx.xx000000000000000000000000 and another number that is also x,xxx.xx000000000000000000000000, why would I receive an answer that comes back as x,xxx.xx4999999999900000000000000000? This doesn't make sense to me. Has anyone else run into this issue? How did you fix it?  Ashish 
#6




subtraction returning incorrect calue
Why do you need more than 15 digits of precision? You can calculate the
distance to the sun to the nearest micrometre in 15 digits. What application can need more precision than that? Regards, Fred "Ashish_Vaidya" wrote in message ... While I understand the nature of fitting a product's capabilities to a wide variety of users and their particular needs, it becomes terribly inefficient to try and rewrite all of my formulas to a ROUND function due to this phenomena. Thank you for your answer as it answers my question, yet unsatisfied with the solution.  Ashish "Joe User" wrote: "Ashish_Vaidya" wrote: when I subtracted two numbers [...], why would I receive an answer that comes back as x,xxx.xx4999999999900000000000000000? FYI, it does not make sense to format more 15 "significant digits"  that is, 15 digits starting with the first nonzero digit on the left. Has anyone else run into this issue? How did you fix it? Yes, this is very common. "Everyone" runs into this issue at some point. The short answer is: always explicitly round expressions that involve values that might have decimal fractions. Typically, use the ROUND function. For example, IF(10.110=0.1,TRUE) returns FALSE(!). But IF(ROUND(10.110),1)=0.1,TRUE) return TRUE, as expected. (Alternatively, you might use the Precision As Displayed calculation. But that can result in surprising results. If you choose to try PAD, be sure to make a backup copy of your Excel file first. However, note that PAD will not help in the example above.) The longer answer can become quite involved. In short, Excel stores numbers and performs arithmetic using a standard form called (IEEE 754) 64bit floating point. For overwhelming details, see http://support.microsoft.com/kb/78113. But the point to note is: most decimal fractions cannot be represented exactly. Instead, they are approximated by the sum of 53 consecutive powers of two ("bits"). Sometimes this results in different representations for the same decimal fraction. For example, 10.1 is represented internally as exactly 10.0999999999999,996447286321199499070644378662109 375, whereas 0.1 is exactly 0.100000000000000,00555111512312578270211815834045 41015625. The whole thing is complicatd by the fact that Excel has implemented some heuristics to try to mitigate the effects of these differences. Unfortunately, the Excel implementation is halfbaked. It leads to even more confusion when, for example, subtractiing some numbers results in exactly zero, but subtracting other "similar" numbers does not. Moreover, the halfbaked implementation can have "impossible" results; for example IF(A1=A2,TRUE) might return TRUE, but IF(A1A2=0,TRUE) might not.  original message  "Ashish_Vaidya" wrote: We have a spreadsheet that subtracts one number from another. We did not understand why Excel is rounding the way it did so we decided to add decimal places until we see the value change. For instance, one of our number was going out to x,xxx.499999999999999999999999999999900000 while the other number was x,xxx.5000000000000000000000000000000000. When we rounded the numbers, one rounded up while the other rounded down. This is fine. Now that we fixed the formula to TRUNC instead of ROUND, I thought the issue would go away but now when I subtracted two numbers that were like x,xxx.xx000000000000000000000000 and another number that is also x,xxx.xx000000000000000000000000, why would I receive an answer that comes back as x,xxx.xx4999999999900000000000000000? This doesn't make sense to me. Has anyone else run into this issue? How did you fix it?  Ashish 
Thread Tools  
Display Modes  

