IF/PMT problem 1

[LittleRedHat]

I need to display £0.00 if the cells (B7, B8, B9) in the following = 0 ……

=IF(-PMT(B8,B9,B7)/12=0,"£0.00",-PMT(B8,B9,B7)/12) gives the #DIV/0! error,

I have the same problem using IF with =-((B7*B8)*((1+B8)^B9)/(1-((1+B8)^(B9)))/12)

Please does anyone know a way round this?

Thanks

(|>

"A day without sunshine is like ... night"

B-) B-) B-) B-) B-) B-) B-) B-) B-) B-) B-) B-) B-) B-) B-)

 

[JVFriederick]

Hi LittleRed,
I tested both of the formulas . . .
When the "period" cell in B9 is zero, the formula evalutates to #DIV/0!
If you enter a non zero number, it works fine.

The "loan amount" cell and the "interest rate" cell do not need to be non zero.

Give it a try.

 

[LittleRedHat]

Many thanks JV. I'd gone round in so many circles I'd never even thought to check out which cell was causing the problem. Wrists slapped!!! s-)

I've now got round the problem by using 1 instead of zero. Ideally though I need all the inputs and the repayment to show as zeros for the template. I've tried a bundle of different functions ... solo, combined and permutated!!! ... but none so far are resolving the dividing by zero issue. Am I trying to do the impossible?

Hope you've got sunshine your end too ...

B-) B-) B-) B-) B-) B-) B-) B-) B-) B-) B-) B-)

 

[dneufarth]

Try

=IF(OR(b8=0,b9=0,b7=0),"£0.00",-PMT(B8,B9,B7)/12)

Dave

 

[LittleRedHat]

Hi Dave,

Thanks so much for getting me zero'd! If I'd put IF and OR and all the other bits in the right order I wouldn't have need to bother you good folks!!!

Hope all your problems are little ones ...

Maureen

"Nothing is foolproof to a well-practised fool" (|>