Problem with formula in Excel

[Bill R]

My form calculates a payment after adding all of the fields that affect the
total financed amount. Two of the fields that are added to the total financed
amount calculates the credit life and disability premium respectively. If the
credit life and/or the disability Insurance is not selected they return a
blank field.
As long as both Life and disability are both selected the payment
calculates fine. But if either one is not selected and therefore "blank"
then
the payment = "blank".
See the formula below.
How can I correct this problem? Thanks.

=IF(ISERROR(PMT(AX7/12,BA6,AX4+CE22*CB8+CD14+CC19+CE22+CE26+CE29,,0)),"",-PMT(AX7/12,BA6,AX4+CE22*CB8+CD14+CC19+CE22+CE26+CE29,,0))

CE26 and CE29 contain the formulas that are causing the problem.
Thanks.

 

[Max]

.. CE26 and CE29 contain the formulas

that are causing the problem ..

One guess is that the formulas in CE26 and CE29 are returning null strings
(these appear like "blanks") which causes the problem ..

One play to try is to replace:

CE26 with SUM(CE26)
CE29 with SUM(CE29)

viz. try instead:

=IF(ISERROR(PMT(AX7/12,BA6,AX4+CE22*CB8+CD14+CC19+CE22+SUM(CE26)+SUM(CE29),,
0)),"",-PMT(AX7/12,BA6,AX4+CE22*CB8+CD14+CC19+CE22+SUM(CE26)+SUM(CE29),,0))

[ It looks admittedly superfluous, but using SUM(CE26) and SUM(CE29) will
evaluate any null strings returned in the cells to zeros ]

Alternatively, we could try amending the error trap part of the formulas in
CE26 and CE29 to return zeros instead of "" (blanks), e.g.:

=IF(ISERROR( Formula involving CE26 or CE29 ),0,( Formula involving CE26 or
CE29 ))

 

[Bill R]

Sounds like it will work. I'll try it. Thanks!

.. CE26 and CE29 contain the formulas
that are causing the problem ..

One guess is that the formulas in CE26 and CE29 are returning null strings
(these appear like "blanks") which causes the problem ..

One play to try is to replace:

CE26 with SUM(CE26)
CE29 with SUM(CE29)

viz. try instead:

=IF(ISERROR(PMT(AX7/12,BA6,AX4+CE22*CB8+CD14+CC19+CE22+SUM(CE26)+SUM(CE29),,
0)),"",-PMT(AX7/12,BA6,AX4+CE22*CB8+CD14+CC19+CE22+SUM(CE26)+SUM(CE29),,0))

[ It looks admittedly superfluous, but using SUM(CE26) and SUM(CE29) will
evaluate any null strings returned in the cells to zeros ]

Alternatively, we could try amending the error trap part of the formulas in
CE26 and CE29 to return zeros instead of "" (blanks), e.g.:

=IF(ISERROR( Formula involving CE26 or CE29 ),0,( Formula involving CE26 or
CE29 ))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

My form calculates a payment after adding all of the fields that affect the
total financed amount. Two of the fields that are added to the total financed
amount calculates the credit life and disability premium respectively. If the
credit life and/or the disability Insurance is not selected they return a
blank field.
As long as both Life and disability are both selected the payment
calculates fine. But if either one is not selected and therefore "blank"
then
the payment = "blank".
See the formula below.
How can I correct this problem? Thanks.

=IF(ISERROR(PMT(AX7/12,BA6,AX4+CE22*CB8+CD14+CC19+CE22+CE26+CE29,,0)),"",-PM
T(AX7/12,BA6,AX4+CE22*CB8+CD14+CC19+CE22+CE26+CE29,,0))

CE26 and CE29 contain the formulas that are causing the problem.
Thanks.

 

[Max]

Hope it works for you, Bill !