Is there a way for excel to recognise that €0.00 = 0 for the purposes IF function ?

[CmTaz]

Hi everyone - first time poster here and as probably most posts here are
I am looking for help.

Excel does not recognise €0.00 as the value 0. I need this for the
following. Basicaly I have created a loan amortisation sheet breaking
down each monthly payment into repayment amount / principal / interest
etc etc. I am using the PMT formula to calculate each monthly payment
as it goes.

=-PMT(D11/12,$I$4*12-A10,C11)
D11 being a cell referencing the interest rate / 12 to make monthly.
$I$4*12-A10 is calulating the payments left I4 is the original loan
term less the the number of payments made A10 to give how many are left
) and C11 is the balance left on the loan.

I hope this is easy enough to follow
It works a dream when I create exactly enough rows for the amount of
payments. My problem occurs when I change the original loan term in I4.
I would like to be able to change the loan term and have the appropriate
rows of calculations disappear / go blank when not needed. ie if I
change it from a 35 year term to a 30 year one that the last 60 rows of
calculations disappear. If I decrease this term I get a #Div/0! error
across the formulas for the time period past the new lesser loan time.
I am assuming this is within the PMT formula when the principle is at 0
as the loan has been paid off.

I tried to find a function that would check wether or not a value was
at 0 elsewhere and if so to return 0 itself but if the value elsewhere
was not at 0 to include that figure within a function of the original
cell.

I tried to use the IF function and insert the pmt function within it.
ie to see if C11 was equal to 0 and if it was to return 0 as a value
for the function but if it did not equal 0 to calculate the original
PMT function. =-PMT(D11/12,$I$4*12-A10,C11). but I cannot make the PMT
functtion work within one of the IF true or false options.

Furthermore it also appears that even in a simple IF function excel
does not recognise €0.00 as 0. eg if I put in =IF(A!=0,"yes","no") and
A1 contains €0.00 it gives the value of no. If I put it as
=IF(A!=€0.00,"yes","no") it is invalid and finally for
=IF(A!="€0.00","yes","no") it again shows no.

I am looking for any way to either get excel to recognise a zero in
currency to be the same as 0 or to get a PMT function active within IF
( preferably both as I suspect I need both of these at the same time to
do this for me )

I really hope that you can follow the above - it makes more sense in
your own mind than when you try to explain it.

Many thanks in advance for any advice.

 

[davesexcel]

I don't know if this helps at all,
but to format a cell into european curency select the cell, go to
format cells and select the numbers tab
select curency, then hit the symbol menu, and select the curency you
want,
hopefully then your formula will be recognized

 

[CmTaz]

Thanks for the reply but this is not the problem. I am trying to get
excel to treat €0.00 as if it was 0. ( not turning off the currency but
treating it as if it wasn't a currency )

 

[Tom Ogilvy]

It probably doesn't equal zero if it is calculated with a formula. Try this
(assume the value is in B9)

=if(abs(b9)<.0001,"Zero","Not Zero")

 

[Ron Rosenfeld]

Furthermore it also appears that even in a simple IF function excel
does not recognise ¤0.00 as 0. eg if I put in =IF(A!=0,"yes","no") and
A1 contains ¤0.00 it gives the value of no. If I put it as
=IF(A!=¤0.00,"yes","no") it is invalid and finally for
=IF(A!="¤0.00","yes","no") it again shows no.

I assume where you've typed A! above you really mean A1.

What, exactly, is in A1?

In other words, what shows in the formula bar when you select A1?

If A1 truly contains a value of zero, then your first formula will return
"yes".

The usual problem with the sort of result you're describing is that what you
think is zero really is not. Usually that's because the value is derived from
a formula, and the inherent problems with decimal-binary conversions and 15
decimal digit precision (inherent in most every computer spreadsheet program)
result in a number that's off a little bit from zero.

Some errors are due to mixing text and numeric values, which may look the same,
but are not. You must ensure that the values you are comparing are both
numeric (or both text). Else you will get unexpected results.

There are a number of solutions. One way is to check the rounded (to two
decimal) value to see if it's equal to zero.

e.g. =IF(ROUND(A1,2)=0,"Yes","No")


--ron