Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare values
I have a problem in a macro where I'm comparing two values. They appear to
be the same but when tested in the code, are different. I recreated the problem using this scenario: A1-D1 = .2003, .0542, .0257 and 0, respectively. A2-D2 have the same values. E1 = .2802 E2 = the formula sum(A2:D2) When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True": Sub testit() frst = Range("E1").Value Scnd = Range("E2").Value MsgBox frst MsgBox Scnd MsgBox (frst = Scnd) End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare values
Increase the number of decimal places shown for cells E1 and E2 and
you will see that they are not exactly equal. This is a common problem with computers which use binary numbers to represent decimals - some decimal fractions cannot be represented exactly with binary fractions, and so rounding errors occur. I'm sure that JoeU will give a more detailed explanation. Hope this helps. Pete On Sep 21, 4:23*pm, Steve wrote: I have a problem in a macro where I'm comparing two values. *They appear to be the same but when tested in the code, are different. *I recreated the problem using this scenario: A1-D1 = .2003, .0542, .0257 and 0, respectively. A2-D2 have the same values. E1 = .2802 E2 = the formula sum(A2:D2) When I run the following code I get the message box values ".2802", ".2802" and "False". *The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True": Sub testit() frst = Range("E1").Value Scnd = Range("E2").Value MsgBox frst MsgBox Scnd MsgBox (frst = Scnd) End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare values
Your code works fine for me. I get True as the final msgbox.
-- HTH... Jim Thomlinson "Steve" wrote: I have a problem in a macro where I'm comparing two values. They appear to be the same but when tested in the code, are different. I recreated the problem using this scenario: A1-D1 = .2003, .0542, .0257 and 0, respectively. A2-D2 have the same values. E1 = .2802 E2 = the formula sum(A2:D2) When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True": Sub testit() frst = Range("E1").Value Scnd = Range("E2").Value MsgBox frst MsgBox Scnd MsgBox (frst = Scnd) End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare values
Works fine for me too! Are you running it with the right worksheet? stick
with worksheets ("your worksheet name") and end with either side of your sub to be sure! otherwise cant figure it! "Jim Thomlinson" wrote: Your code works fine for me. I get True as the final msgbox. -- HTH... Jim Thomlinson "Steve" wrote: I have a problem in a macro where I'm comparing two values. They appear to be the same but when tested in the code, are different. I recreated the problem using this scenario: A1-D1 = .2003, .0542, .0257 and 0, respectively. A2-D2 have the same values. E1 = .2802 E2 = the formula sum(A2:D2) When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True": Sub testit() frst = Range("E1").Value Scnd = Range("E2").Value MsgBox frst MsgBox Scnd MsgBox (frst = Scnd) End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare values
I increased to 123 decimal places and there are no differences.
"Pete_UK" wrote: Increase the number of decimal places shown for cells E1 and E2 and you will see that they are not exactly equal. This is a common problem with computers which use binary numbers to represent decimals - some decimal fractions cannot be represented exactly with binary fractions, and so rounding errors occur. I'm sure that JoeU will give a more detailed explanation. Hope this helps. Pete On Sep 21, 4:23 pm, Steve wrote: I have a problem in a macro where I'm comparing two values. They appear to be the same but when tested in the code, are different. I recreated the problem using this scenario: A1-D1 = .2003, .0542, .0257 and 0, respectively. A2-D2 have the same values. E1 = .2802 E2 = the formula sum(A2:D2) When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True": Sub testit() frst = Range("E1").Value Scnd = Range("E2").Value MsgBox frst MsgBox Scnd MsgBox (frst = Scnd) End Sub |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare values
I'm really happy for you but it doesn't really help me much does it?
"Jim Thomlinson" wrote: Your code works fine for me. I get True as the final msgbox. -- HTH... Jim Thomlinson "Steve" wrote: I have a problem in a macro where I'm comparing two values. They appear to be the same but when tested in the code, are different. I recreated the problem using this scenario: A1-D1 = .2003, .0542, .0257 and 0, respectively. A2-D2 have the same values. E1 = .2802 E2 = the formula sum(A2:D2) When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True": Sub testit() frst = Range("E1").Value Scnd = Range("E2").Value MsgBox frst MsgBox Scnd MsgBox (frst = Scnd) End Sub |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare values
What that tells me is there has to be more to this than what you have posted.
Are there other formulas involved. Is your code copeid exactly. Is there more to the code... Since you are obviously not using option explicit and declaring variable spelling counts so if you have retyped the code for your question then you need to review that. Perhpas try opening a new workbook and following your question exactly as posted and let us know how that goes... -- HTH... Jim Thomlinson "Steve" wrote: I'm really happy for you but it doesn't really help me much does it? "Jim Thomlinson" wrote: Your code works fine for me. I get True as the final msgbox. -- HTH... Jim Thomlinson "Steve" wrote: I have a problem in a macro where I'm comparing two values. They appear to be the same but when tested in the code, are different. I recreated the problem using this scenario: A1-D1 = .2003, .0542, .0257 and 0, respectively. A2-D2 have the same values. E1 = .2802 E2 = the formula sum(A2:D2) When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True": Sub testit() frst = Range("E1").Value Scnd = Range("E2").Value MsgBox frst MsgBox Scnd MsgBox (frst = Scnd) End Sub |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare values
I cannot explain your problem exactly because I cannot duplicate with the
example numbers that you provide. But I can explain the concepts. First, numbers with decimal fractions usually cannot be represented exactly in the internal form that Excel and most applications use (binary floating point). For example, the following shows the constant entered on the left and the exact internal value on the right. (The comma is my way of demarcating the first 15 significant digits, which is all that Excel will format.) A1: 0.2003 0.200300000000000,00581756864903582027181982994079 58984375 A2: 0.0542 0.0541999999999999,9817923423961474327370524406433 10546875 A3: 0.0257 0.0257000000000000,0062172489379008766263723373413 0859375 A4: 0 0 A5: =sum(A1:A4) 0.2802 0.280200000000000,00461852778244065120816230773925 78125 A6: 0.2802 0.280200000000000,00461852778244065120816230773925 78125 As you can see, in this example, SUM(A1:A4) is indeed identical to the constant 0.2802 internally. We probably cannot duplicate your results because A1, A2, A3 and perhaps even A4 (A1:D1 and A2:D2 in your posting) are probably not constants that you entered. Instead, you are probably posting the displayed values that result from formulas. As is often the case then, displayed values are not exactly what they appear to be, even when increase the number of decimal places. So it would be prudent to write =ROUND(SUM(A1:A4),4). And I suspect that will remedy your problem. Just for fun, try =IF(10.1 - 10 = 0.1, TRUE). It will return FALSE(!). But try =IF(ROUND(10.1 - 10,2) = 0.1, TRUE). It will return TRUE as expected. The reason is: 10.1 - 10 results in exactly 0.0999999999999996,4472863211994990706443786621093 75 , whereas the constant 0.1 is exactly 0.100000000000000,00555111512312578270211815834045 41015625 . In this case, you will see the difference if you format cells with those expressions as Number with 16 decimal places. When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True" As noted above, both produce TRUE in this case when we use the constants that you posted. However, in general, the difference might arise because Excel employs some (half-baked) heuristics to try to ameliorate the problem. Refer to the section titled "Example When a Value Reaches Zero" in http://support.microsoft.com/kb/78113/en-us . For example, suppose E1 is the constant 0.1, and E2 is =E1+2^-56. You will not see the difference using Excel; Excel displays 0.100000000000000 in both cases. Excel converts only the first 15 significant digits; that is, not counting the first 15 significant digits. After that, it simply appends zeros. But internally, the values are obviously different; to wit: E1: 0.100000000000000,00555111512312578270211815834045 41015625 E2: 0.100000000000000,01942890293094023945741355419158 935546875 Nonetheless, in Excel =E1=E2 displays TRUE. In VBA: Msgbox Range("E1") = Range("E2") displays FALSE because VBA does not implement any "remedial" heuristics. ----- original message ----- "Steve" wrote in message ... I have a problem in a macro where I'm comparing two values. They appear to be the same but when tested in the code, are different. I recreated the problem using this scenario: A1-D1 = .2003, .0542, .0257 and 0, respectively. A2-D2 have the same values. E1 = .2802 E2 = the formula sum(A2:D2) When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True": Sub testit() frst = Range("E1").Value Scnd = Range("E2").Value MsgBox frst MsgBox Scnd MsgBox (frst = Scnd) End Sub |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare values
PS....
I wrote: For example, suppose E1 is the constant 0.1, and E2 is =E1+2^-56. [....] E1: 0.100000000000000,00555111512312578270211815834045 41015625 E2: 0.100000000000000,01942890293094023945741355419158 935546875 Nonetheless, in Excel =E1=E2 displays TRUE. In light of that, you might find it suprising that =E1-E2=0 displays FALSE in this example. This is why I refer to the Excel remedial algorithm as "half-baked". But don't get me wrong: I am personally grateful for their half-baked approach. It gives us an easy way to defeat it ;-). ----- original message ----- "JoeU2004" wrote in message ... I cannot explain your problem exactly because I cannot duplicate with the example numbers that you provide. But I can explain the concepts. First, numbers with decimal fractions usually cannot be represented exactly in the internal form that Excel and most applications use (binary floating point). For example, the following shows the constant entered on the left and the exact internal value on the right. (The comma is my way of demarcating the first 15 significant digits, which is all that Excel will format.) A1: 0.2003 0.200300000000000,00581756864903582027181982994079 58984375 A2: 0.0542 0.0541999999999999,9817923423961474327370524406433 10546875 A3: 0.0257 0.0257000000000000,0062172489379008766263723373413 0859375 A4: 0 0 A5: =sum(A1:A4) 0.2802 0.280200000000000,00461852778244065120816230773925 78125 A6: 0.2802 0.280200000000000,00461852778244065120816230773925 78125 As you can see, in this example, SUM(A1:A4) is indeed identical to the constant 0.2802 internally. We probably cannot duplicate your results because A1, A2, A3 and perhaps even A4 (A1:D1 and A2:D2 in your posting) are probably not constants that you entered. Instead, you are probably posting the displayed values that result from formulas. As is often the case then, displayed values are not exactly what they appear to be, even when increase the number of decimal places. So it would be prudent to write =ROUND(SUM(A1:A4),4). And I suspect that will remedy your problem. Just for fun, try =IF(10.1 - 10 = 0.1, TRUE). It will return FALSE(!). But try =IF(ROUND(10.1 - 10,2) = 0.1, TRUE). It will return TRUE as expected. The reason is: 10.1 - 10 results in exactly 0.0999999999999996,4472863211994990706443786621093 75 , whereas the constant 0.1 is exactly 0.100000000000000,00555111512312578270211815834045 41015625 . In this case, you will see the difference if you format cells with those expressions as Number with 16 decimal places. When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True" As noted above, both produce TRUE in this case when we use the constants that you posted. However, in general, the difference might arise because Excel employs some (half-baked) heuristics to try to ameliorate the problem. Refer to the section titled "Example When a Value Reaches Zero" in http://support.microsoft.com/kb/78113/en-us . For example, suppose E1 is the constant 0.1, and E2 is =E1+2^-56. You will not see the difference using Excel; Excel displays 0.100000000000000 in both cases. Excel converts only the first 15 significant digits; that is, not counting the first 15 significant digits. After that, it simply appends zeros. But internally, the values are obviously different; to wit: E1: 0.100000000000000,00555111512312578270211815834045 41015625 E2: 0.100000000000000,01942890293094023945741355419158 935546875 Nonetheless, in Excel =E1=E2 displays TRUE. In VBA: Msgbox Range("E1") = Range("E2") displays FALSE because VBA does not implement any "remedial" heuristics. ----- original message ----- "Steve" wrote in message ... I have a problem in a macro where I'm comparing two values. They appear to be the same but when tested in the code, are different. I recreated the problem using this scenario: A1-D1 = .2003, .0542, .0257 and 0, respectively. A2-D2 have the same values. E1 = .2802 E2 = the formula sum(A2:D2) When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True": Sub testit() frst = Range("E1").Value Scnd = Range("E2").Value MsgBox frst MsgBox Scnd MsgBox (frst = Scnd) End Sub |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare values
PS#2....
I wrote: So it would be prudent to write =ROUND(SUM(A1:A4),4). And I suspect that will remedy your problem. Or if you would prefer to retain the exact values in E1 and E2, you could write: Excel: =ROUND(E1,4)=ROUND(E2,4) VBA: Msgbox WorksheetFunction.Round(Range("E1"),4) = _ WorksheetFunction.Round(Range("E2"),4) = _ Note that I use WorksheetFunction.Round, not the VBA Round() function. The reason is: the VBA implements "banker's rounding", which differs from "normal" (Excel) rounding. In VBA, compare Round(2.5,0) and WorksheetFunction.Round(2.5,0). Also, if you are mixing Excel and VBA arithmetic involving numbers with decimal fractions, one final comment.... There may be circumstances where a complex expression in Excel (more than two terms) does not compare exactly equal with the same complex expression in VBA. Here is one example. In VBA: Sub doit() Range("a2") = (2226 - 123.36 - 39.34 - 303 - 1600) * 0.05 End Sub In Excel: A1: =(2226 - 123.36 - 39.34 - 303 - 1600) * 0.05 A3: =A1=A2 A3 will display FALSE(!). The reason is: A1 is 8.01499999999998,63575794734060764312744140625 internally, whereas A2 is 8.01500000000000,05684341886080801486968994140625. You will see a difference even if you format A1 and A2 as Number with 14 decimal places. The reason that Excel and VBA have slightly different results has to do with the way each does arithmetic. Excel does pairwise operations (e.g. first 2226 minus 123.36, then that result minus 39.34, etc), converting to a 64-bit floating point representation for each operation. VBA does all the operations in the higher-precision 80-bit floating point representation provided by Intel-compatible CPUs, converting only the final result to 64-floating point. (Unless the expression contains function calls.) Which reminds me: I should mention that while these concepts are true for Excel and VBA on all binary CPUs, the specific examples and their results apply to Intel-compatible CPUs. I am not familiar with the Mac. Again, the proper and necessary workaround to all of these numerical "aberrations" is to use "fuzzy logic" when comparing floating points results (i.e. numbers with decimal fractions), no matter how benign the calculations might look. By "fuzzy logic", I mean either explicit rounding or something of the form ABS(A1-A2)<0.0001 to test for "equality". ----- original message ----- "JoeU2004" wrote in message ... I cannot explain your problem exactly because I cannot duplicate with the example numbers that you provide. But I can explain the concepts. First, numbers with decimal fractions usually cannot be represented exactly in the internal form that Excel and most applications use (binary floating point). For example, the following shows the constant entered on the left and the exact internal value on the right. (The comma is my way of demarcating the first 15 significant digits, which is all that Excel will format.) A1: 0.2003 0.200300000000000,00581756864903582027181982994079 58984375 A2: 0.0542 0.0541999999999999,9817923423961474327370524406433 10546875 A3: 0.0257 0.0257000000000000,0062172489379008766263723373413 0859375 A4: 0 0 A5: =sum(A1:A4) 0.2802 0.280200000000000,00461852778244065120816230773925 78125 A6: 0.2802 0.280200000000000,00461852778244065120816230773925 78125 As you can see, in this example, SUM(A1:A4) is indeed identical to the constant 0.2802 internally. We probably cannot duplicate your results because A1, A2, A3 and perhaps even A4 (A1:D1 and A2:D2 in your posting) are probably not constants that you entered. Instead, you are probably posting the displayed values that result from formulas. As is often the case then, displayed values are not exactly what they appear to be, even when increase the number of decimal places. So it would be prudent to write =ROUND(SUM(A1:A4),4). And I suspect that will remedy your problem. Just for fun, try =IF(10.1 - 10 = 0.1, TRUE). It will return FALSE(!). But try =IF(ROUND(10.1 - 10,2) = 0.1, TRUE). It will return TRUE as expected. The reason is: 10.1 - 10 results in exactly 0.0999999999999996,4472863211994990706443786621093 75 , whereas the constant 0.1 is exactly 0.100000000000000,00555111512312578270211815834045 41015625 . In this case, you will see the difference if you format cells with those expressions as Number with 16 decimal places. When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True" As noted above, both produce TRUE in this case when we use the constants that you posted. However, in general, the difference might arise because Excel employs some (half-baked) heuristics to try to ameliorate the problem. Refer to the section titled "Example When a Value Reaches Zero" in http://support.microsoft.com/kb/78113/en-us . For example, suppose E1 is the constant 0.1, and E2 is =E1+2^-56. You will not see the difference using Excel; Excel displays 0.100000000000000 in both cases. Excel converts only the first 15 significant digits; that is, not counting the first 15 significant digits. After that, it simply appends zeros. But internally, the values are obviously different; to wit: E1: 0.100000000000000,00555111512312578270211815834045 41015625 E2: 0.100000000000000,01942890293094023945741355419158 935546875 Nonetheless, in Excel =E1=E2 displays TRUE. In VBA: Msgbox Range("E1") = Range("E2") displays FALSE because VBA does not implement any "remedial" heuristics. ----- original message ----- "Steve" wrote in message ... I have a problem in a macro where I'm comparing two values. They appear to be the same but when tested in the code, are different. I recreated the problem using this scenario: A1-D1 = .2003, .0542, .0257 and 0, respectively. A2-D2 have the same values. E1 = .2802 E2 = the formula sum(A2:D2) When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True": Sub testit() frst = Range("E1").Value Scnd = Range("E2").Value MsgBox frst MsgBox Scnd MsgBox (frst = Scnd) End Sub |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare values
Errata (typo)....
I wrote: Excel converts only the first 15 significant digits; that is, not counting the first 15 significant digits. Huh?! I cannot tell you how many times I reread that sentence, but only now do I see the typo. It should be: Excel converts only the first 15 significant digits; that is, not counting leading zeros. Klunk! ----- original message ----- "JoeU2004" wrote in message ... I cannot explain your problem exactly because I cannot duplicate with the example numbers that you provide. But I can explain the concepts. First, numbers with decimal fractions usually cannot be represented exactly in the internal form that Excel and most applications use (binary floating point). For example, the following shows the constant entered on the left and the exact internal value on the right. (The comma is my way of demarcating the first 15 significant digits, which is all that Excel will format.) A1: 0.2003 0.200300000000000,00581756864903582027181982994079 58984375 A2: 0.0542 0.0541999999999999,9817923423961474327370524406433 10546875 A3: 0.0257 0.0257000000000000,0062172489379008766263723373413 0859375 A4: 0 0 A5: =sum(A1:A4) 0.2802 0.280200000000000,00461852778244065120816230773925 78125 A6: 0.2802 0.280200000000000,00461852778244065120816230773925 78125 As you can see, in this example, SUM(A1:A4) is indeed identical to the constant 0.2802 internally. We probably cannot duplicate your results because A1, A2, A3 and perhaps even A4 (A1:D1 and A2:D2 in your posting) are probably not constants that you entered. Instead, you are probably posting the displayed values that result from formulas. As is often the case then, displayed values are not exactly what they appear to be, even when increase the number of decimal places. So it would be prudent to write =ROUND(SUM(A1:A4),4). And I suspect that will remedy your problem. Just for fun, try =IF(10.1 - 10 = 0.1, TRUE). It will return FALSE(!). But try =IF(ROUND(10.1 - 10,2) = 0.1, TRUE). It will return TRUE as expected. The reason is: 10.1 - 10 results in exactly 0.0999999999999996,4472863211994990706443786621093 75 , whereas the constant 0.1 is exactly 0.100000000000000,00555111512312578270211815834045 41015625 . In this case, you will see the difference if you format cells with those expressions as Number with 16 decimal places. When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True" As noted above, both produce TRUE in this case when we use the constants that you posted. However, in general, the difference might arise because Excel employs some (half-baked) heuristics to try to ameliorate the problem. Refer to the section titled "Example When a Value Reaches Zero" in http://support.microsoft.com/kb/78113/en-us . For example, suppose E1 is the constant 0.1, and E2 is =E1+2^-56. You will not see the difference using Excel; Excel displays 0.100000000000000 in both cases. Excel converts only the first 15 significant digits; that is, not counting the first 15 significant digits. After that, it simply appends zeros. But internally, the values are obviously different; to wit: E1: 0.100000000000000,00555111512312578270211815834045 41015625 E2: 0.100000000000000,01942890293094023945741355419158 935546875 Nonetheless, in Excel =E1=E2 displays TRUE. In VBA: Msgbox Range("E1") = Range("E2") displays FALSE because VBA does not implement any "remedial" heuristics. ----- original message ----- "Steve" wrote in message ... I have a problem in a macro where I'm comparing two values. They appear to be the same but when tested in the code, are different. I recreated the problem using this scenario: A1-D1 = .2003, .0542, .0257 and 0, respectively. A2-D2 have the same values. E1 = .2802 E2 = the formula sum(A2:D2) When I run the following code I get the message box values ".2802", ".2802" and "False". The funny thing is...if I type a formula in the worksheet =E1=E2 I get "True": Sub testit() frst = Range("E1").Value Scnd = Range("E2").Value MsgBox frst MsgBox Scnd MsgBox (frst = Scnd) End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare values on sheet 1 to values on sheet2 | Excel Worksheet Functions | |||
compare values | Excel Worksheet Functions | |||
compare a few values | Excel Discussion (Misc queries) | |||
Excel Compare values in columns & display missing values in a new | Excel Discussion (Misc queries) |