Formula problem.

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,""))))))

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" <BillR@discussions.microsoft.com> wrote in message
news:CD85C786-0951-407D-8661-4ABEA9DA0778@microsoft.com...
> 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,""))))))

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

> 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" <BillR@discussions.microsoft.com> wrote in message
> news:CD85C786-0951-407D-8661-4ABEA9DA0778@microsoft.com...
> > 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,""))))))
>
>
>

Hi!

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

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


=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" <BillR@discussions.microsoft.com> wrote in message
news:A01277A0-901A-411C-A4D7-E77D3508617E@microsoft.com...
> 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" wrote:
>
>> 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" <BillR@discussions.microsoft.com> wrote in message
>> news:CD85C786-0951-407D-8661-4ABEA9DA0778@microsoft.com...
>> > 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,""))))))
>>
>>
>>

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.



"Biff" wrote:

> Hi!
>
> >Also, I didn't understand what you were telling me to do to make the
> >formula
> >less complex.
>
> >> Replace all the ISBLANKS(...) with cell_ref=""
> >> Replace all the NOT(ISBLANKS(...) with cell_ref<>""
>
>
> =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" <BillR@discussions.microsoft.com> wrote in message
> news:A01277A0-901A-411C-A4D7-E77D3508617E@microsoft.com...
> > 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" wrote:
> >
> >> 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" <BillR@discussions.microsoft.com> wrote in message
> >> news:CD85C786-0951-407D-8661-4ABEA9DA0778@microsoft.com...
> >> > 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,""))))))
> >>
> >>
> >>
>
>
>

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.

"Bill R" wrote:

> 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.
>
>
>
> "Biff" wrote:
>
> > Hi!
> >
> > >Also, I didn't understand what you were telling me to do to make the
> > >formula
> > >less complex.
> >
> > >> Replace all the ISBLANKS(...) with cell_ref=""
> > >> Replace all the NOT(ISBLANKS(...) with cell_ref<>""
> >
> >
> > =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" <BillR@discussions.microsoft.com> wrote in message
> > news:A01277A0-901A-411C-A4D7-E77D3508617E@microsoft.com...
> > > 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" wrote:
> > >
> > >> 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" <BillR@discussions.microsoft.com> wrote in message
> > >> news:CD85C786-0951-407D-8661-4ABEA9DA0778@microsoft.com...
> > >> > 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,""))))))
> > >>
> > >>
> > >>
> >
> >
> >

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" <BillR@discussions.microsoft.com> wrote in message
news:62DD4C98-1262-4933-9500-12AC07155582@microsoft.com...
>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.
>
> "Bill R" wrote:
>
>> 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.
>>
>>
>>
>> "Biff" wrote:
>>
>> > Hi!
>> >
>> > >Also, I didn't understand what you were telling me to do to make the
>> > >formula
>> > >less complex.
>> >
>> > >> Replace all the ISBLANKS(...) with cell_ref=""
>> > >> Replace all the NOT(ISBLANKS(...) with cell_ref<>""
>> >
>> >
>> > =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" <BillR@discussions.microsoft.com> wrote in message
>> > news:A01277A0-901A-411C-A4D7-E77D3508617E@microsoft.com...
>> > > 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" wrote:
>> > >
>> > >> 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" <BillR@discussions.microsoft.com> wrote in message
>> > >> news:CD85C786-0951-407D-8661-4ABEA9DA0778@microsoft.com...
>> > >> > 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,""))))))
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

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

> 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" <BillR@discussions.microsoft.com> wrote in message
> news:62DD4C98-1262-4933-9500-12AC07155582@microsoft.com...
> >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.
> >
> > "Bill R" wrote:
> >
> >> 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.
> >>
> >>
> >>
> >> "Biff" wrote:
> >>
> >> > Hi!
> >> >
> >> > >Also, I didn't understand what you were telling me to do to make the
> >> > >formula
> >> > >less complex.
> >> >
> >> > >> Replace all the ISBLANKS(...) with cell_ref=""
> >> > >> Replace all the NOT(ISBLANKS(...) with cell_ref<>""
> >> >
> >> >
> >> > =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" <BillR@discussions.microsoft.com> wrote in message
> >> > news:A01277A0-901A-411C-A4D7-E77D3508617E@microsoft.com...
> >> > > 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" wrote:
> >> > >
> >> > >> 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" <BillR@discussions.microsoft.com> wrote in message
> >> > >> news:CD85C786-0951-407D-8661-4ABEA9DA0778@microsoft.com...
> >> > >> > 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,""))))))
> >> > >>
> >> > >>
> >> > >>
> >> >
> >> >
> >> >
>
>
>

Maybe this:

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

Biff

"Bill R" <BillR@discussions.microsoft.com> wrote in message
news:3EED151E-42B0-4C29-ADC7-F14550C23A68@microsoft.com...
> 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" wrote:
>
>> 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" <BillR@discussions.microsoft.com> wrote in message
>> news:62DD4C98-1262-4933-9500-12AC07155582@microsoft.com...
>> >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.
>> >
>> > "Bill R" wrote:
>> >
>> >> 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.
>> >>
>> >>
>> >>
>> >> "Biff" wrote:
>> >>
>> >> > Hi!
>> >> >
>> >> > >Also, I didn't understand what you were telling me to do to make
>> >> > >the
>> >> > >formula
>> >> > >less complex.
>> >> >
>> >> > >> Replace all the ISBLANKS(...) with cell_ref=""
>> >> > >> Replace all the NOT(ISBLANKS(...) with cell_ref<>""
>> >> >
>> >> >
>> >> > =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" <BillR@discussions.microsoft.com> wrote in message
>> >> > news:A01277A0-901A-411C-A4D7-E77D3508617E@microsoft.com...
>> >> > > 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" wrote:
>> >> > >
>> >> > >> 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" <BillR@discussions.microsoft.com> wrote in message
>> >> > >> news:CD85C786-0951-407D-8661-4ABEA9DA0778@microsoft.com...
>> >> > >> > 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,""))))))
>> >> > >>
>> >> > >>
>> >> > >>
>> >> >
>> >> >
>> >> >
>>
>>
>>