IF, AND, OR Function help needed

[THE BIG O]

I have an existing function that works.
=IF(AND(AL18="Y",OR(AM18="Y",AM18=0),AN18="Y")=TRUE,"Standard Pricing",
$AJ$18).

I need to add to it that if AL18, AM18, and AM19 are N but if D5 is greater
than 10000 then "Call for Std Pricing Quote". I need to show "Standard
Pricing if AL18, AM18, AN19, and D5<10000. If any of the AL18, AM18, AN18
are yes then $aJ$18.

I tried to add another IF statement but got a Value error.

Help.

Thanks

Michael

[Bob Phillips]

=IF(AND(AL18="Y",OR(AM18="Y",AM18=0),AN18="Y"),"Standard Pricing",
IF(OR(AL18="Y",AM18="Y",AN18="Y"),$AJ$18,IF(D5>10000,"Call for Std Pricing
Quote","STandard Pricing")))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"THE BIG O" <[email protected]> wrote in message
news:[email protected]...
> I have an existing function that works.
> =IF(AND(AL18="Y",OR(AM18="Y",AM18=0),AN18="Y")=TRUE,"Standard Pricing",
> $AJ$18).
>
> I need to add to it that if AL18, AM18, and AM19 are N but if D5 is
greater
> than 10000 then "Call for Std Pricing Quote". I need to show "Standard
> Pricing if AL18, AM18, AN19, and D5<10000. If any of the AL18, AM18, AN18
> are yes then $aJ$18.
>
> I tried to add another IF statement but got a Value error.
>
> Help.
>
> Thanks
>
> Michael

[THE BIG O]

Hmmm, that did not work. I put in 15000 in D5 and the result still shows
Standard pricing. I need to clarify something I said earlier, If any of
the AL18, AM18, and AN18 say N, then it is to give me $AJ$18. If all are yes
and then if D5 is over 10000 then "Call for Std Pricing Quote:, if all are Y
and D5 is less than 10000 then give me "Standard Pricing".

Thanks for your help.

Michael



"Bob Phillips" wrote:

> =IF(AND(AL18="Y",OR(AM18="Y",AM18=0),AN18="Y"),"Standard Pricing",
> IF(OR(AL18="Y",AM18="Y",AN18="Y"),$AJ$18,IF(D5>10000,"Call for Std Pricing
> Quote","STandard Pricing")))
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "THE BIG O" <[email protected]> wrote in message
> news:[email protected]...
> > I have an existing function that works.
> > =IF(AND(AL18="Y",OR(AM18="Y",AM18=0),AN18="Y")=TRUE,"Standard Pricing",
> > $AJ$18).
> >
> > I need to add to it that if AL18, AM18, and AM19 are N but if D5 is
> greater
> > than 10000 then "Call for Std Pricing Quote". I need to show "Standard
> > Pricing if AL18, AM18, AN19, and D5<10000. If any of the AL18, AM18, AN18
> > are yes then $aJ$18.
> >
> > I tried to add another IF statement but got a Value error.
> >
> > Help.
> >
> > Thanks
> >
> > Michael
>
>
>

[Duke Carey]

Are the only possible values Ys, Ns and a zero for AM18? If so,

=if(SUMPRODUCT(--(AL18:AN18="N"))>0,$AJ$18,IF(D5>10000,"Call for
","")&"Standard Pricing")



"THE BIG O" wrote:

> Hmmm, that did not work. I put in 15000 in D5 and the result still shows
> Standard pricing. I need to clarify something I said earlier, If any of
> the AL18, AM18, and AN18 say N, then it is to give me $AJ$18. If all are yes
> and then if D5 is over 10000 then "Call for Std Pricing Quote:, if all are Y
> and D5 is less than 10000 then give me "Standard Pricing".
>
> Thanks for your help.
>
> Michael
>
>
>
> "Bob Phillips" wrote:
>
> > =IF(AND(AL18="Y",OR(AM18="Y",AM18=0),AN18="Y"),"Standard Pricing",
> > IF(OR(AL18="Y",AM18="Y",AN18="Y"),$AJ$18,IF(D5>10000,"Call for Std Pricing
> > Quote","STandard Pricing")))
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "THE BIG O" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have an existing function that works.
> > > =IF(AND(AL18="Y",OR(AM18="Y",AM18=0),AN18="Y")=TRUE,"Standard Pricing",
> > > $AJ$18).
> > >
> > > I need to add to it that if AL18, AM18, and AM19 are N but if D5 is
> > greater
> > > than 10000 then "Call for Std Pricing Quote". I need to show "Standard
> > > Pricing if AL18, AM18, AN19, and D5<10000. If any of the AL18, AM18, AN18
> > > are yes then $aJ$18.
> > >
> > > I tried to add another IF statement but got a Value error.
> > >
> > > Help.
> > >
> > > Thanks
> > >
> > > Michael
> >
> >
> >

[THE BIG O]

After making a change to the second IF statement it worked. Before the
change I kept getting a False and realized that it was missing the answer to
give if the formula was false. I think this works. I am going to have
several of my office mates throw different scenarios against my spreadsheet
to make sure the answers are correct.

Thank you very much for your help.

Michael

"Duke Carey" wrote:

> Are the only possible values Ys, Ns and a zero for AM18? If so,
>
> =if(SUMPRODUCT(--(AL18:AN18="N"))>0,$AJ$18,IF(D5>10000,"Call for
> ","")&"Standard Pricing")
>
>
>
> "THE BIG O" wrote:
>
> > Hmmm, that did not work. I put in 15000 in D5 and the result still shows
> > Standard pricing. I need to clarify something I said earlier, If any of
> > the AL18, AM18, and AN18 say N, then it is to give me $AJ$18. If all are yes
> > and then if D5 is over 10000 then "Call for Std Pricing Quote:, if all are Y
> > and D5 is less than 10000 then give me "Standard Pricing".
> >
> > Thanks for your help.
> >
> > Michael
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > =IF(AND(AL18="Y",OR(AM18="Y",AM18=0),AN18="Y"),"Standard Pricing",
> > > IF(OR(AL18="Y",AM18="Y",AN18="Y"),$AJ$18,IF(D5>10000,"Call for Std Pricing
> > > Quote","STandard Pricing")))
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from the email address if mailing direct)
> > >
> > > "THE BIG O" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have an existing function that works.
> > > > =IF(AND(AL18="Y",OR(AM18="Y",AM18=0),AN18="Y")=TRUE,"Standard Pricing",
> > > > $AJ$18).
> > > >
> > > > I need to add to it that if AL18, AM18, and AM19 are N but if D5 is
> > > greater
> > > > than 10000 then "Call for Std Pricing Quote". I need to show "Standard
> > > > Pricing if AL18, AM18, AN19, and D5<10000. If any of the AL18, AM18, AN18
> > > > are yes then $aJ$18.
> > > >
> > > > I tried to add another IF statement but got a Value error.
> > > >
> > > > Help.
> > > >
> > > > Thanks
> > > >
> > > > Michael
> > >
> > >
> > >