Excel cannot subtract???

[blakrapter]

I am working on a very simple spreadsheet to check a loan balance b
subtracting last months reported principal to this months reporte
principal and checking that against the amount of money paid toward th
principal. So, I have the function =IF(G4-G5=E5, "Yes", "No") where G
is last months principal balance, G5 is this months reported principa
balance, and E5 is the amount paid toward principal balance. If th
reduction in principal matches the amount paid, then everything i
good. It all checks out (in excel and hand calculations) that th
reduction and paid principal match, BUT, it keeps displaying No?????
Why?? I have tried different variations where I have G4-G5 in anothe
cell and compair that cell to E5 and have tried doing IF(E5-whateve
cell the diff is in=0, "Yes", "No") but that won't work. Why doesn'
excel see the if statement as true when it is?

Thanks

 

[JE McGimpsey]

You're undoubtedly using formulae which produce fractional numbers.

This may help explain, and give you an idea for how to correct the
problem:

http://www.mcgimpsey.com/excel/pennyoff.html

 

[blakrapter]

Thanks for the reply. I am using fractions to 2 decimal places, but I
am not multiplying or dividing, so it should always be no more than 2
decimal places with no rounding issues. Now, if the "IF" function has
issues with anything other than whole numbers, it could cause a
problem, BUT, the value to the subtraction is always 0.00, so even
though the subtraction deals with cents, the result is a whole number.

Also, I tried using a cell that contained the difference in two
numbers. It was something like $75.05 (difference in principals
mentioned eariler) The forumula was in say cell A5 and then I put
"=A5" in cell B5. Then when I used the IF function as IF(A5=B5, "yes",
"no") it worked... It really does sound like a rounding error with
something in the .00000001 place or something off, but i cannot be
because I am just adding and subtracting currency values that I put
in...

Any ideas?

Thanks

 

[Jerry W. Lewis]

Computers do math in binary, not decimal. The only 2-figure decimal
fractions that can be exactly represented in binary are 0.00, 0.25,
0.50, and 0.75. All other 2-figure decimal fractions must be
approximated in binary (just as 1/3 must be approximated in decimal).
When you do math with approximate inputs, it sould not be surprising
when the output is only approximate.

Instead of IF(A5=B5, ... use IF(ROUND(A5,2)=ROUND(B5,2), ... or
equivalently IF(ROUND(A5-B5,2)=0, ... to avoid detecting slight
differences due to accumulated discrepancies due to binary approximations.

Alternately, if you did your calculations in pennies (7505 instead of
75.05), then no approximations would be involved, and you would get the
results that you expected.

Jerry

 

[blakrapter]

Got it. thanks for the info.

 

[Jerry W. Lewis]

You're welcome, glad it helped.

Jerry