Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with = in function
Hi,
Please help in understanding this: Cells d10,d11,d12 have the value 10.7 in them Cell e10 has the value .7 directly input Cell e11 has the value .7 derived by the formula =d11-int(d11) Cell e12 has the value .7 dervied by the formula = e11-trunc(e11) When values in cells e10,e11,e12 are tested for =.7, cell f10 shows true, f11 and f12 show false. Why? Thanks in Advance for the Help. Raj |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with = in function
Hi,
If you format E10, E11 & E12 to a number with lots of decimal places you'll finf that E10 is 0.7 but E11 & E12 are actually 0.6999999999999999 so hence the evaluation as false when tested for =0.7. The reason for this is in the way Excel does arithmetic and there being no precise binary equivalent of 0.7. For a full explanation have a look here http://support.microsoft.com/default.aspx/kb/78113 Mike "Raj" wrote: Hi, Please help in understanding this: Cells d10,d11,d12 have the value 10.7 in them Cell e10 has the value .7 directly input Cell e11 has the value .7 derived by the formula =d11-int(d11) Cell e12 has the value .7 dervied by the formula = e11-trunc(e11) When values in cells e10,e11,e12 are tested for =.7, cell f10 shows true, f11 and f12 show false. Why? Thanks in Advance for the Help. Raj |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with = in function
I should have added
=ROUND(E11,1)=0.7 rounding E11 to 1 decimal place makes it evaluate as TRUE "Mike H" wrote: Hi, If you format E10, E11 & E12 to a number with lots of decimal places you'll finf that E10 is 0.7 but E11 & E12 are actually 0.6999999999999999 so hence the evaluation as false when tested for =0.7. The reason for this is in the way Excel does arithmetic and there being no precise binary equivalent of 0.7. For a full explanation have a look here http://support.microsoft.com/default.aspx/kb/78113 Mike "Raj" wrote: Hi, Please help in understanding this: Cells d10,d11,d12 have the value 10.7 in them Cell e10 has the value .7 directly input Cell e11 has the value .7 derived by the formula =d11-int(d11) Cell e12 has the value .7 dervied by the formula = e11-trunc(e11) When values in cells e10,e11,e12 are tested for =.7, cell f10 shows true, f11 and f12 show false. Why? Thanks in Advance for the Help. Raj |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with = in function
Hi,
If you expand the decimals in F11 and F12, the result is 0.699999999999999. So the FALSE answer is correct. The reason for the answer not being 0.7 is conversion of numbers from decimal and binary and vice versa. To solve this problem, you may use the round function =ROUND(E10,0)=0.7 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Raj" wrote in message ... Hi, Please help in understanding this: Cells d10,d11,d12 have the value 10.7 in them Cell e10 has the value .7 directly input Cell e11 has the value .7 derived by the formula =d11-int(d11) Cell e12 has the value .7 dervied by the formula = e11-trunc(e11) When values in cells e10,e11,e12 are tested for =.7, cell f10 shows true, f11 and f12 show false. Why? Thanks in Advance for the Help. Raj |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with = in function
"Raj" wrote:
d10,d11,d12 have the value 10.7 in them Cell e10 has the value .7 directly input Cell e11 has the value .7 derived by the formula =d11-int(d11) Cell e12 has the value .7 dervied by the formula = e11-trunc(e11) When values in cells e10,e11,e12 are tested for =.7, cell f10 shows true, f11 and f12 show false. Why? Most decimal fractions cannot be represented exactly in the internal form that Excel uses to represent numbers -- a standard binary floating-pointing form. You are encountering two different approximations for 0.7. 10.7 is represented internally as 10.6999999999999,992894572642398998141288757324218 75. (The comma is my way of demarcating 15 significant digits to the left.) When you subtract 10, you get 0.699999999999999,28945726423989981412887573242187 5. But 0.7 is represented internally as 0.699999999999999,95559107901499373838305473327636 71875. You can see that they differ starting in the 16th significant digit, and the first representation is indeed less than the second representation. I will explain why we get two different approximations of 0.7 below. But the important lesson here is: use ROUND prolifically when you are dealing with numbers with decimal fractions. For example, compute ROUND(D11-INT(D11),1), or compare ROUND(E11,1)=0.7. I prefer the first solution whenever feasible. Note: Although formatting might change the appearance of numbers, it does not change the underlying value. Formatting E11 as Number with 1 decimal place is not the same computing ROUND(D11-INT(D11),1), even though they might look the same. Another alternative is to set the calculation option "Precision as displayed" under Tools Options Calculation. But since that affects all cells that do not use the General format, that option can lead to untoward surprises. I do not use it. So, why do we get two different approximations of 0.7? In a nutshell, because numbers are represented internally by 53 consecutive powers of 2 ("bits"). With 0.7, we can use the full 53 bits to represent 0.7. But with 10.7, some of the bits are used to represent 10; so there are fewer bits to represent 0.7. In this case, that results in a less accurate representation of 0.7. When we subtract 10, we are left with the less accurate representation of 0.7. All of this seems mysterious and difficult to predict, for a number of complicated reasons. For example, you would encounter no problem with your original formulation if D11 contained 1.7. By coincidence, the internal representation of 1.7 has the same approximation of 0.7 as 0.7 itself. But I reiterate: that is coincidence. Again, use ROUND prolifically to avoid most surprises. (But unfortunately not all!) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If function problem | Excel Discussion (Misc queries) | |||
Problem with IF function.... | Excel Worksheet Functions | |||
Mid Function Problem | Excel Discussion (Misc queries) | |||
Problem with IF function | Excel Worksheet Functions | |||
FV Function Problem | Excel Worksheet Functions |