Excel cannot subtract???
 

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

Thanks.


--
blakrapter
------------------------------------------------------------------------
blakrapter's Profile: http://www.excelforum.com/member.php...o&userid=28926
View this thread: http://www.excelforum.com/showthread...hreadid=489873

Excel cannot subtract???
 

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

In article ,
blakrapter
wrote:

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

Thanks.

Excel cannot subtract???
 

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


--
blakrapter
------------------------------------------------------------------------
blakrapter's Profile: http://www.excelforum.com/member.php...o&userid=28926
View this thread: http://www.excelforum.com/showthread...hreadid=489873

Excel cannot subtract???
 

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 wrote:

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

Excel cannot subtract???
 

Got it. thanks for the info.


--
blakrapter
------------------------------------------------------------------------
blakrapter's Profile: http://www.excelforum.com/member.php...o&userid=28926
View this thread: http://www.excelforum.com/showthread...hreadid=489873

Excel cannot subtract???
 

You're welcome, glad it helped.

Jerry

blakrapter wrote:

Got it. thanks for the info.