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]

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

 

[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]

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