Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF formula doesn't work with certain numbers
I have 2 columns, both summed. If the sums equal, the 3rd column is to say
"ok". If they don't, it says "check bal". We have been doing this for months. Today I entered "197.14" in both columns. The sum total is the same. The IF column says "check bal". I can put in "197" and it works, "197.12" works, "197.13" & "197.15" work. "197.10" also doesn't work. We finally added 197.14 to a total in another cell & it worked fine. We reformatted & made sure everything was numbers & that there was no rounding in the total numbers. Any ideas? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF formula doesn't work with certain numbers
Start out by by displaying the size of the difference.
-- Gary''s Student "kaywe44" wrote: I have 2 columns, both summed. If the sums equal, the 3rd column is to say "ok". If they don't, it says "check bal". We have been doing this for months. Today I entered "197.14" in both columns. The sum total is the same. The IF column says "check bal". I can put in "197" and it works, "197.12" works, "197.13" & "197.15" work. "197.10" also doesn't work. We finally added 197.14 to a total in another cell & it worked fine. We reformatted & made sure everything was numbers & that there was no rounding in the total numbers. Any ideas? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF formula doesn't work with certain numbers
If you are saying that you typed 197.14 in two cells and an IF statement
declared them different, then I would expect that =COUNT(cell1,cell2) would return 1, indicating that one cell contains text and the other a number. If you are saying that two different calculations both arrived at answers of 197.14, yet an IF statement declared them different, then I would expect that the formula =(cell1-cell2) would return something other than zero (note that the parentheses are needed here). Almost all software (including Excel) does binary math. Most terminating decimal fractions (including .14) are nonterminating binary fractions that can only be approximated in binary. When you do math with approximate inputs, it should not be surprising when the final answer is only approximately correct. Assuming that you are only adding and subtracting numbers that each have at most 2 decimal places, then If you would round the results of calculations to 2-decimal places, you would do no violence to the calculation and avoid surprises caused by accumulated approximations to the inputs. Alternately, if you did your accounting in pennies instead of dollars, then you would avoid the issue altogether, since all integers with no more than 15 digits are exactly representable in the IEEE standard binary format that Excel uses. Jerry "kaywe44" wrote: I have 2 columns, both summed. If the sums equal, the 3rd column is to say "ok". If they don't, it says "check bal". We have been doing this for months. Today I entered "197.14" in both columns. The sum total is the same. The IF column says "check bal". I can put in "197" and it works, "197.12" works, "197.13" & "197.15" work. "197.10" also doesn't work. We finally added 197.14 to a total in another cell & it worked fine. We reformatted & made sure everything was numbers & that there was no rounding in the total numbers. Any ideas? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
IF formula - evaluation shows it should work but... | Excel Worksheet Functions | |||
Auto Extend Formulas doesn't work for a particular formula | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions |