Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conflicting results in formulas?
Hello,
I have something that is confusing me greatly. Here's the condensed version: I have a formula in L5. I want to test to see if the result is an integer. In W4, I have the formula =L5-INT(L5)<0 Result given is TRUE However, it should be an integer, so to test I put in cell W5 =L5-INT(L5) Result given is 0 To make sure that it wasn't something 10 or 15 decimal places out, I entered the following in W6 =W5=0 Result given is TRUE Just for giggles, in W7 I put =L5=INT(L5) Result given is TRUE So...what's wrong with the formula in W4 that it returns results that are in direct conflict with the rest of them? Or is that one potentially correct...? If anyone has any insight, I'd be grateful. XL03, WinXPPro SP2 Thanks, Mike Lee Coppell, TX |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conflicting results in formulas?
What is your formula in L5 and what values are used by that formula, not
formatted, but actual? Tyro "mikelee101" wrote in message ... Hello, I have something that is confusing me greatly. Here's the condensed version: I have a formula in L5. I want to test to see if the result is an integer. In W4, I have the formula =L5-INT(L5)<0 Result given is TRUE However, it should be an integer, so to test I put in cell W5 =L5-INT(L5) Result given is 0 To make sure that it wasn't something 10 or 15 decimal places out, I entered the following in W6 =W5=0 Result given is TRUE Just for giggles, in W7 I put =L5=INT(L5) Result given is TRUE So...what's wrong with the formula in W4 that it returns results that are in direct conflict with the rest of them? Or is that one potentially correct...? If anyone has any insight, I'd be grateful. XL03, WinXPPro SP2 Thanks, Mike Lee Coppell, TX |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conflicting results in formulas?
On Mar 21, 4:03*pm, "Tyro" wrote:
What is your formula in L5 and what values are used by that formula, not formatted, but actual? Tyro"mikelee101" wrote in message ... Hello, I have something that is confusing me greatly. *Here's the condensed version: I have a formula in L5. *I want to test to see if the result is an integer. In W4, I have the formula =L5-INT(L5)<0 * *Result given is TRUE However, it should be an integer, so to test I put in cell W5 =L5-INT(L5) * *Result given is 0 To make sure that it wasn't something 10 or 15 decimal places out, I entered the following in W6 =W5=0 *Result given is TRUE Just for giggles, in W7 I put =L5=INT(L5) * Result given is TRUE So...what's wrong with the formula in W4 that it returns results that are in direct conflict with the rest of them? *Or is that one potentially correct...? If anyone has any insight, I'd be grateful. XL03, WinXPPro SP2 Thanks, Mike Lee Coppell, TX- Hide quoted text - - Show quoted text - The formula in L5 is: =SUMPRODUCT(--(month=MONTH(DATEVALUE($A5&" 1, 2008"))),-- (agent=VLOOKUP(A1,name.table,2,FALSE)))/(SUMPRODUCT((datapoint<"")/ COUNTIF(datapoint,datapoint))-1) where month is a named range containing months, and it's compared against a text label in column A (January, February, etc.), agent is a named range containing names which is compared against a "preferred name" that's looked up from a table named name.table, and datapoint is a named range that contains a description of what each row of data contains (i.e. Calls Answered, Logged in Time, etc). the purpose of the formula is to calculate how many days an agent worked during the month. it accomplishes that by totaling up the number of times that an agent appears in a given month, then divides that by the number of unique datapoints. I'm not really sure how to go about showing you the actual data, since all the function really does is count things. as far as the result of the formula, this is it to 20 decimal places: 6.00000000000000000000 however, i still get True when it calculates L5-INT(L5)<0. after i posted this, i decided to try one more thing, so i went with =ROUND(L5,25)-INT(ROUND(L5,25))<0 and i got FALSE for that one, so it's clearly a rounding problem somewhere down the line. I just don't understand why those different formulas that should(?) be the same thing give different results... thanks, mike |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conflicting results in formulas?
Unfortunately, this inconsistency is intended by MS, and will occur anytime
that L5 is not an integer but differs from INT(L5) beyond the 15th significant figure (such as =1+2^-52). Under those circumstances expressions like =L5-INT(L5) and =L5=INT(L5) cannot be relied upon to accurately reflect the value in L5. Instead you need to structure the formula such that the subtraction or comparison is not the last operation, as in =(L5-INT(L5)) or =L5-INT(L5)<0. As documented, Excel will display no more than 15 significant figures. If you ask for more, you will get a result padded with meaningless zeros instead of an accurate reflection of the cell contents, but you can indirectly determine what is going on http://groups.google.com/group/micro...fb95785d1eaff5 Almost all software does math in finite precision (the only alternative would be symbolic math, which would be prohibitively slow in a large spreadsheet). Under some circumstances, finite precision math necessarily violates familiar mathematical rules such as x = a+x-a. For example, if you were doing 4-figure decimal math, then 1+1/3-1 = 1+0.3333-1 = 1.333-1 = 0.333 < 0.3333 Most people recognize that 1/3 has no finite decimal representation, and so are not surprised by this, but people who have not thought about the binary math underlying Excel and almost all other software are surprised when similar things happen involving numbers like 0.1 (which has no finite binary representation). In an effort to smooth this out (a technically impossible challenge) MS introduced an "optimization" in Excel 97 http://support.microsoft.com/kb/78113 such that final subtractions of numbers that are equal to 15 decimal digits will return zero, and final comparisons of numbers that are equal to 15 decimal digits will be declared equal. This will only be an improvement if the small real differences only exist because of finite precision approximations. The "optimization" cannot be extended beyond the final operation, because it would result in increasing inaccuracy. Depending on what calculations have taken place, this fuzz factor may be too much or may not be enough--one size does not fit all. A better approach would have been to educate users with guidelines on how to more effectively get the intended results in finite precision, but there have been 4 subsequent Excel versions with no indication that MS recognizes the need to rethink their approach, so I won't hold my breath. The classical discussion of the issues around finite precision floating point arithmetic is Goldberg's article "What every computer scientist should know about floating point arithmetic"; copies can be found many places on the internet, such as http://www.physics.ohio-state.edu/~d...point_math.pdf Jerry "mikelee101" wrote: Hello, I have something that is confusing me greatly. Here's the condensed version: I have a formula in L5. I want to test to see if the result is an integer. In W4, I have the formula =L5-INT(L5)<0 Result given is TRUE However, it should be an integer, so to test I put in cell W5 =L5-INT(L5) Result given is 0 To make sure that it wasn't something 10 or 15 decimal places out, I entered the following in W6 =W5=0 Result given is TRUE Just for giggles, in W7 I put =L5=INT(L5) Result given is TRUE So...what's wrong with the formula in W4 that it returns results that are in direct conflict with the rest of them? Or is that one potentially correct...? If anyone has any insight, I'd be grateful. XL03, WinXPPro SP2 Thanks, Mike Lee Coppell, TX |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conflicting results in formulas?
I worked on an old mainframe computer called UNIVAC, designed in the 1940's.
Although it did bit by bit arithmetic it could add, subtract, multiply and divide decimal numbers and produce the correct decimal answers. How far we have come! Or, have we? Tyro "Jerry W. Lewis" wrote in message ... Unfortunately, this inconsistency is intended by MS, and will occur anytime that L5 is not an integer but differs from INT(L5) beyond the 15th significant figure (such as =1+2^-52). Under those circumstances expressions like =L5-INT(L5) and =L5=INT(L5) cannot be relied upon to accurately reflect the value in L5. Instead you need to structure the formula such that the subtraction or comparison is not the last operation, as in =(L5-INT(L5)) or =L5-INT(L5)<0. As documented, Excel will display no more than 15 significant figures. If you ask for more, you will get a result padded with meaningless zeros instead of an accurate reflection of the cell contents, but you can indirectly determine what is going on http://groups.google.com/group/micro...fb95785d1eaff5 Almost all software does math in finite precision (the only alternative would be symbolic math, which would be prohibitively slow in a large spreadsheet). Under some circumstances, finite precision math necessarily violates familiar mathematical rules such as x = a+x-a. For example, if you were doing 4-figure decimal math, then 1+1/3-1 = 1+0.3333-1 = 1.333-1 = 0.333 < 0.3333 Most people recognize that 1/3 has no finite decimal representation, and so are not surprised by this, but people who have not thought about the binary math underlying Excel and almost all other software are surprised when similar things happen involving numbers like 0.1 (which has no finite binary representation). In an effort to smooth this out (a technically impossible challenge) MS introduced an "optimization" in Excel 97 http://support.microsoft.com/kb/78113 such that final subtractions of numbers that are equal to 15 decimal digits will return zero, and final comparisons of numbers that are equal to 15 decimal digits will be declared equal. This will only be an improvement if the small real differences only exist because of finite precision approximations. The "optimization" cannot be extended beyond the final operation, because it would result in increasing inaccuracy. Depending on what calculations have taken place, this fuzz factor may be too much or may not be enough--one size does not fit all. A better approach would have been to educate users with guidelines on how to more effectively get the intended results in finite precision, but there have been 4 subsequent Excel versions with no indication that MS recognizes the need to rethink their approach, so I won't hold my breath. The classical discussion of the issues around finite precision floating point arithmetic is Goldberg's article "What every computer scientist should know about floating point arithmetic"; copies can be found many places on the internet, such as http://www.physics.ohio-state.edu/~d...point_math.pdf Jerry "mikelee101" wrote: Hello, I have something that is confusing me greatly. Here's the condensed version: I have a formula in L5. I want to test to see if the result is an integer. In W4, I have the formula =L5-INT(L5)<0 Result given is TRUE However, it should be an integer, so to test I put in cell W5 =L5-INT(L5) Result given is 0 To make sure that it wasn't something 10 or 15 decimal places out, I entered the following in W6 =W5=0 Result given is TRUE Just for giggles, in W7 I put =L5=INT(L5) Result given is TRUE So...what's wrong with the formula in W4 that it returns results that are in direct conflict with the rest of them? Or is that one potentially correct...? If anyone has any insight, I'd be grateful. XL03, WinXPPro SP2 Thanks, Mike Lee Coppell, TX |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up Formulas with Results | Excel Discussion (Misc queries) | |||
conflicting users in shared worksheets. | Excel Discussion (Misc queries) | |||
Conflicting Conditional Formatting | Excel Discussion (Misc queries) | |||
Conflicting date ranges | Excel Worksheet Functions | |||
Formulas with If/ and results | Excel Worksheet Functions |