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!

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!

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!