Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL 2000 - IF function returns wrong result from logical test
I've tried the following simple IF test as a check on totals in a sheet where
I'm compiling accounts: =IF(C24=SUM(C25:C50),"","!") bizarrely, C24 and the sum of C25 to C50 are the same number, but I'm getting a FALSE result and "!" is appearing in the check cell In another cell i've summed C25:C50 to prove to myself that it really is the same number - yes, it is! the number in C24 is from another sheet in the same workbook and is in turn the result of a SUM in that sheet - I've double checked that value and it is correct also. the values in each of the cells C25 to C50 are each from a a different sheet in the same workbook Can anyone suggest why I'm getting this false "FALSE" result? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL 2000 - IF function returns wrong result from logical test
What are your numbers? Are they floating point values by any chance?
-- Rick (MVP - Excel) "Sean Duffy" <Sean wrote in message ... I've tried the following simple IF test as a check on totals in a sheet where I'm compiling accounts: =IF(C24=SUM(C25:C50),"","!") bizarrely, C24 and the sum of C25 to C50 are the same number, but I'm getting a FALSE result and "!" is appearing in the check cell In another cell i've summed C25:C50 to prove to myself that it really is the same number - yes, it is! the number in C24 is from another sheet in the same workbook and is in turn the result of a SUM in that sheet - I've double checked that value and it is correct also. the values in each of the cells C25 to C50 are each from a a different sheet in the same workbook Can anyone suggest why I'm getting this false "FALSE" result? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL 2000 - IF function returns wrong result from logical test
If any of the values are calculated, there could be rounding errors.
i.e.: C24 could really be $45.000003 and SUM(C25:C50) might really be $45.0007 but if both are formatted as $45.00, then they look equal, but really aren't. -- John C "Sean Duffy" wrote: I've tried the following simple IF test as a check on totals in a sheet where I'm compiling accounts: =IF(C24=SUM(C25:C50),"","!") bizarrely, C24 and the sum of C25 to C50 are the same number, but I'm getting a FALSE result and "!" is appearing in the check cell In another cell i've summed C25:C50 to prove to myself that it really is the same number - yes, it is! the number in C24 is from another sheet in the same workbook and is in turn the result of a SUM in that sheet - I've double checked that value and it is correct also. the values in each of the cells C25 to C50 are each from a a different sheet in the same workbook Can anyone suggest why I'm getting this false "FALSE" result? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL 2000 - IF function returns wrong result from logical tes
Thanks for this suggestion - I've checked but no luck
"John C" wrote: If any of the values are calculated, there could be rounding errors. i.e.: C24 could really be $45.000003 and SUM(C25:C50) might really be $45.0007 but if both are formatted as $45.00, then they look equal, but really aren't. -- John C "Sean Duffy" wrote: I've tried the following simple IF test as a check on totals in a sheet where I'm compiling accounts: =IF(C24=SUM(C25:C50),"","!") bizarrely, C24 and the sum of C25 to C50 are the same number, but I'm getting a FALSE result and "!" is appearing in the check cell In another cell i've summed C25:C50 to prove to myself that it really is the same number - yes, it is! the number in C24 is from another sheet in the same workbook and is in turn the result of a SUM in that sheet - I've double checked that value and it is correct also. the values in each of the cells C25 to C50 are each from a a different sheet in the same workbook Can anyone suggest why I'm getting this false "FALSE" result? Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL 2000 - IF function returns wrong result from logical tes
Well, sometimes Excel adds extra digits in the 10,000ths place, because it
can. You still might try modifying your formula as such: =IF(ROUND(C24,2)=ROUND(SUM(C25:C50),2),"","!") -- John C "Sean Duffy" wrote: Thanks for this suggestion - I've checked but no luck "John C" wrote: If any of the values are calculated, there could be rounding errors. i.e.: C24 could really be $45.000003 and SUM(C25:C50) might really be $45.0007 but if both are formatted as $45.00, then they look equal, but really aren't. -- John C "Sean Duffy" wrote: I've tried the following simple IF test as a check on totals in a sheet where I'm compiling accounts: =IF(C24=SUM(C25:C50),"","!") bizarrely, C24 and the sum of C25 to C50 are the same number, but I'm getting a FALSE result and "!" is appearing in the check cell In another cell i've summed C25:C50 to prove to myself that it really is the same number - yes, it is! the number in C24 is from another sheet in the same workbook and is in turn the result of a SUM in that sheet - I've double checked that value and it is correct also. the values in each of the cells C25 to C50 are each from a a different sheet in the same workbook Can anyone suggest why I'm getting this false "FALSE" result? Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL 2000 - IF function returns wrong result from logical tes
John C, Thanks a million for your suggestion - it works!
"John C" wrote: Well, sometimes Excel adds extra digits in the 10,000ths place, because it can. You still might try modifying your formula as such: =IF(ROUND(C24,2)=ROUND(SUM(C25:C50),2),"","!") -- John C "Sean Duffy" wrote: Thanks for this suggestion - I've checked but no luck "John C" wrote: If any of the values are calculated, there could be rounding errors. i.e.: C24 could really be $45.000003 and SUM(C25:C50) might really be $45.0007 but if both are formatted as $45.00, then they look equal, but really aren't. -- John C "Sean Duffy" wrote: I've tried the following simple IF test as a check on totals in a sheet where I'm compiling accounts: =IF(C24=SUM(C25:C50),"","!") bizarrely, C24 and the sum of C25 to C50 are the same number, but I'm getting a FALSE result and "!" is appearing in the check cell In another cell i've summed C25:C50 to prove to myself that it really is the same number - yes, it is! the number in C24 is from another sheet in the same workbook and is in turn the result of a SUM in that sheet - I've double checked that value and it is correct also. the values in each of the cells C25 to C50 are each from a a different sheet in the same workbook Can anyone suggest why I'm getting this false "FALSE" result? Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL 2000 - IF function returns wrong result from logical tes
You are welcome, and thanks for the feedback.
-- John C "Sean Duffy" wrote: John C, Thanks a million for your suggestion - it works! "John C" wrote: Well, sometimes Excel adds extra digits in the 10,000ths place, because it can. You still might try modifying your formula as such: =IF(ROUND(C24,2)=ROUND(SUM(C25:C50),2),"","!") -- John C "Sean Duffy" wrote: Thanks for this suggestion - I've checked but no luck "John C" wrote: If any of the values are calculated, there could be rounding errors. i.e.: C24 could really be $45.000003 and SUM(C25:C50) might really be $45.0007 but if both are formatted as $45.00, then they look equal, but really aren't. -- John C "Sean Duffy" wrote: I've tried the following simple IF test as a check on totals in a sheet where I'm compiling accounts: =IF(C24=SUM(C25:C50),"","!") bizarrely, C24 and the sum of C25 to C50 are the same number, but I'm getting a FALSE result and "!" is appearing in the check cell In another cell i've summed C25:C50 to prove to myself that it really is the same number - yes, it is! the number in C24 is from another sheet in the same workbook and is in turn the result of a SUM in that sheet - I've double checked that value and it is correct also. the values in each of the cells C25 to C50 are each from a a different sheet in the same workbook Can anyone suggest why I'm getting this false "FALSE" result? Thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL 2000 - IF function returns wrong result from logical test
Hi,
Of course there is the core question of why this happens. Excel is run on a computer which works in binary, which means all numbers are stored and calculated in binary. Each number is represented as a series of ones and zeros, for example: 1010101011. This number is not always an exact equivalent of the decimal equivalent, hense there may be a small error in the last decimal places. That is enough of an error to cause the Excel not to see the two numbers as equal. As suggested, if this is critical you would resort to a round function. In many cases formatting the cell to 0 or 2 decimals will "hide" the issue. But in your case Excel is checking all digits out to 15 decimals so hiding isn't a solution. -- Thanks, Shane Devenshire "Sean Duffy" wrote: I've tried the following simple IF test as a check on totals in a sheet where I'm compiling accounts: =IF(C24=SUM(C25:C50),"","!") bizarrely, C24 and the sum of C25 to C50 are the same number, but I'm getting a FALSE result and "!" is appearing in the check cell In another cell i've summed C25:C50 to prove to myself that it really is the same number - yes, it is! the number in C24 is from another sheet in the same workbook and is in turn the result of a SUM in that sheet - I've double checked that value and it is correct also. the values in each of the cells C25 to C50 are each from a a different sheet in the same workbook Can anyone suggest why I'm getting this false "FALSE" result? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cosine Function Returns Wrong Answer! | Excel Discussion (Misc queries) | |||
Logical test in IF statement returning incorrect result | Excel Worksheet Functions | |||
Row() function returns wrong row and more.. | Excel Worksheet Functions | |||
month worksheet function returns wrong value | Excel Worksheet Functions | |||
SUM returns wrong result | Excel Worksheet Functions |