Formula problem.

[Bill R]

The formula below works perfectly unless (AL5) is left blank. It then returns
an error message. I have tried modifing the formula using by using the
"IF(ISERROR" function but I could not get it to work. I also tried using the
"IF(ISBLANK" function but I am still doing something wrong. Any suggestions?

=IF(AND(ISBLANK($BU$28),NOT(ISBLANK($BU$31))),AH!$E$19,IF(AND(NOT(ISBLANK($BU$28)),$BX$28="Single",ISBLANK($BU$31)),SL2!$E$19,IF(AND(NOT(ISBLANK($BU$28)),$BX$28="Joint",ISBLANK($BU$31)),JL2!$E$19,IF(AND(NOT(ISBLANK($BU$28)),$BX$28="Single",NOT(ISBLANK($BU$31))),'SL&AH2'!$E$19,IF(AND(NOT(ISBLANK($BU$28)),$BX$28="Joint",NOT(ISBLANK($BU$31))),'JL&AH2'!$E$19,IF(AND(ISBLANK($BU$28),ISBLANK($BU$31)),NONE3!$E$19,""))))))

 

[Biff]

Hi!

The formula below works perfectly unless (AL5) is left blank.

"AL5" isn't even referenced in the formula. Is "AL5" a cell or a defined
name?

You can reduce the "complexity" and length of that formula significantly by
using logical operators rather than functions.

Replace all the ISBLANKS(...) with cell_ref=""

Replace all the NOT(ISBLANKS(...) with cell_ref<>""

Biff

 

[Bill R]

There are several worksheets in the workbook that reference cell AL5. The
sheet that has this formula on it references the other sheets that reference
AL5. The cell (AL5) is on the same sheet as the formula.

Also, I didn't understand what you were telling me to do to make the formula
less complex.

Thanks.

 

[Biff]

Hi!

Also, I didn't understand what you were telling me to do to make the
formula
less complex.

=IF(AND(ISBLANK($BU$28),NOT(ISBLANK($BU$31))),AH!$E$19,IF(AND(NOT(ISBLANK($BU$28)),$BX$28="Single",ISBLANK($BU$31)),SL2!$E$19,IF(AND(NOT(ISBLANK($BU$28)),$BX$28="Joint",ISBLANK($BU$31)),JL2!$E$19,IF(AND(NOT(ISBLANK($BU$28)),$BX$28="Single",NOT(ISBLANK($BU$31))),'SL&AH2'!$E$19,IF(AND(NOT(ISBLANK($BU$28)),$BX$28="Joint",NOT(ISBLANK($BU$31))),'JL&AH2'!$E$19,IF(AND(ISBLANK($BU$28),ISBLANK($BU$31)),NONE3!$E$19,""))))))


=IF(AND($BU$28="",$BU$31<>""),AH!$E$19,IF(AND($BU$28<>"",$BX$28="Single",$BU$31=""),SL2!$E$19,IF(AND($BU$28<>"",$BX$28="Joint",$BU$31=""),JL2!$E$19,IF(AND($BU$28<>"",$BX$28="Single",$BU$31<>""),'SL&AH2'!$E$19,IF(AND($BU$28<>"",$BX$28="Joint",$BU$31<>""),'JL&AH2'!$E$19,IF(AND($BU$28="",$BU$31=""),NONE3!$E$19,""))))))

What type of error are you getting? Is it a #REF! error?

These references look a little suspicious:

'SL&AH2'!$E$19
'JL&AH2'!$E$19

Are those the real sheet names or are you trying to concatenate and
"construct" a sheet name?

Not a whole lot I can do with a problem like this without seeing it for
myself.

Biff

 

[Bill R]

The cell referrences that you refer to are cells on a different sheet. Really
all I need to know is: What can I add to the formula to tell it to leave the
field blank if there is an error? Thank you.

Thanks for showing me the other way to write the formula.

 

[Bill R]

I am getting the #div/0! error if AL5 is left blank. There are times that
this field will be left blank and when it is I don't want the error message
to show.

 

[Biff]

In your formula which one of the nested IF's is returning the #DIV/0! error?

There are no math operations taking place in your formula so the #DIV/0!
error is happening somewhere else. You need to find the source of the error
and fix it there.

I can't do anymore without seeing the file. If you want me to take a look at
it I'd be glad to. Just let me know how to contact you.

Biff

 

[Bill R]

All of them. This is one of three formulas that returns a payment. Each
formula returns a payment at a different loan term based on what options a
customer chooses. What I am trying to do is solve one of the formulas so that
I can solve all three. In some cases I will not be able to offer all three
payment options, therefore the cell that the loan term is loaded into will be
blank causing the error to show in that payment field. AL5 is the field that
the term is loaded into for the first payment option. If AL5 is left blank,
how can I change the formula to keep it from showing an error message. I just
want the payment field to be left blank or maybe show "n/a".

 

[Biff]

Maybe this:

=IF(AL5="","",the_rest_of_the_formula_here)

Biff