| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Why is XL lying to me??
Hi Guy's,
I don't know if I've done something wrong or what, but, I have a sum in one book and a different sum in another. I am using the IF function to basically tell me when the two results differ. Now the results are the same e.g. "$70.65" but the function =IF(H22=[book1]jun!D22," ","check book1jun") is showing false when I know that both cells equal "$70.65". I double checked by removing the sum from cell H22 and typing 70.65 (formatted as currency of course) and the function then says true. I am using IF function to match other sum results and no problem. Pease help I am really freaked about this!! -- KRAMER |
|
#2
|
|||
|
|||
|
On Mon, 20 Jun 2005 19:11:02 -0700, KRAMER
wrote: Hi Guy's, I don't know if I've done something wrong or what, but, I have a sum in one book and a different sum in another. I am using the IF function to basically tell me when the two results differ. Now the results are the same e.g. "$70.65" but the function =IF(H22=[book1]jun!D22," ","check book1jun") is showing false when I know that both cells equal "$70.65". I double checked by removing the sum from cell H22 and typing 70.65 (formatted as currency of course) and the function then says true. I am using IF function to match other sum results and no problem. Pease help I am really freaked about this!! Well, both cells really do NOT equal $70.65 Excel (as well as other spreadsheet programs) does NOT have infinite precision. Also, many numbers cannot be expressed exactly in binary (much like the fraction 1/3 cannot be expressed exactly in decimal, but rather is equal to 0.3333333...) The fix for this normal Excel behavior is to round your results at some point prior to your testing. For a more in depth discussion, see http://www.cpearson.com/excel/rounding.htm or search the archives. The issue appears in this NG at least once or twice a week. --ron |
|
#3
|
|||
|
|||
|
It may be simpler than decimal/binary issues. Since H22 is a sum, the
question is what is it summing? If the cells being summed are themselves formulas, then there may be more decimal places in the result than the OP is displaying (formatting does not impact the underlying value). Regardless, the solution is to round, as you suggested. Jerry Ron Rosenfeld wrote: On Mon, 20 Jun 2005 19:11:02 -0700, KRAMER wrote: Hi Guy's, I don't know if I've done something wrong or what, but, I have a sum in one book and a different sum in another. I am using the IF function to basically tell me when the two results differ. Now the results are the same e.g. "$70.65" but the function =IF(H22=[book1]jun!D22," ","check book1jun") is showing false when I know that both cells equal "$70.65". I double checked by removing the sum from cell H22 and typing 70.65 (formatted as currency of course) and the function then says true. I am using IF function to match other sum results and no problem. Pease help I am really freaked about this!! Well, both cells really do NOT equal $70.65 Excel (as well as other spreadsheet programs) does NOT have infinite precision. Also, many numbers cannot be expressed exactly in binary (much like the fraction 1/3 cannot be expressed exactly in decimal, but rather is equal to 0.3333333...) The fix for this normal Excel behavior is to round your results at some point prior to your testing. For a more in depth discussion, see http://www.cpearson.com/excel/rounding.htm or search the archives. The issue appears in this NG at least once or twice a week. --ron |
|
#4
|
|||
|
|||
|
On Tue, 21 Jun 2005 08:24:11 -0400, "Jerry W. Lewis"
wrote: It may be simpler than decimal/binary issues. Since H22 is a sum, the question is what is it summing? If the cells being summed are themselves formulas, then there may be more decimal places in the result than the OP is displaying (formatting does not impact the underlying value). Regardless, the solution is to round, as you suggested. Jerry That is a more complete explanation. Thank you. --ron |
|
#5
|
|||
|
|||
|
Change the format of the two cells to 'General'. Now, what do the 2
cells contain? Double the column width. What do the cells contain? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Guy's, I don't know if I've done something wrong or what, but, I have a sum in one book and a different sum in another. I am using the IF function to basically tell me when the two results differ. Now the results are the same e.g. "$70.65" but the function =IF(H22=[book1]jun!D22," ","check book1jun") is showing false when I know that both cells equal "$70.65". I double checked by removing the sum from cell H22 and typing 70.65 (formatted as currency of course) and the function then says true. I am using IF function to match other sum results and no problem. Pease help I am really freaked about this!! |
|
#6
|
|||
|
|||
|
Jerry & Ron,
Thank you, I do feel stupid, I should have thought of rounding but never expected that summing of cells formatted as currency with only 2 decimal places could possibly get out of sink. Still learning I guess. No matter it works now so thank you again. -- KRAMER "Jerry W. Lewis" wrote: It may be simpler than decimal/binary issues. Since H22 is a sum, the question is what is it summing? If the cells being summed are themselves formulas, then there may be more decimal places in the result than the OP is displaying (formatting does not impact the underlying value). Regardless, the solution is to round, as you suggested. Jerry Ron Rosenfeld wrote: On Mon, 20 Jun 2005 19:11:02 -0700, KRAMER wrote: Hi Guy's, I don't know if I've done something wrong or what, but, I have a sum in one book and a different sum in another. I am using the IF function to basically tell me when the two results differ. Now the results are the same e.g. "$70.65" but the function =IF(H22=[book1]jun!D22," ","check book1jun") is showing false when I know that both cells equal "$70.65". I double checked by removing the sum from cell H22 and typing 70.65 (formatted as currency of course) and the function then says true. I am using IF function to match other sum results and no problem. Pease help I am really freaked about this!! Well, both cells really do NOT equal $70.65 Excel (as well as other spreadsheet programs) does NOT have infinite precision. Also, many numbers cannot be expressed exactly in binary (much like the fraction 1/3 cannot be expressed exactly in decimal, but rather is equal to 0.3333333...) The fix for this normal Excel behavior is to round your results at some point prior to your testing. For a more in depth discussion, see http://www.cpearson.com/excel/rounding.htm or search the archives. The issue appears in this NG at least once or twice a week. --ron |
|
#8
|
|||
|
|||
|
On Tue, 21 Jun 2005 16:27:02 -0700, KRAMER
wrote: never expected that summing of cells formatted as currency with only 2 decimal places In Excel, formatting only affects how the contents of the cell are *displayed*. But unless you have checked the option "Precision as Displayed", the numbers themselves are stored at the full precision of the program. This frequently leads to penny off results in calculating sums of currency. --ron |
|
#9
|
|||
|
|||
|
KRAMER wrote...
I don't know if I've done something wrong or what, but, I have a sum in one book and a different sum in another. I am using the IF function to basically tell me when the two results differ. Now the results are the same e.g. "$70.65" but the function =IF(H22=[book1]jun!D22," ","check book1jun") is showing false when I know that both cells equal "$70.65". I double checked by removing the sum from cell H22 and typing 70.65 (formatted as currency of course) and the function then says true. I am using IF function to match other sum results and no problem. .... More expedient to change the approach and not worry about what these cells evaluate to. Change the formula to =IF(ABS(H22-[book1]jun!D22)<0.001,"","check book1jun") Using ABS(x-y)<z rather than x=y means that when x and y are within z of each other, treat them as effectively equal. For currency amounts, values differing by less than 0.001 should be considered equal. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
