conditional formula with multiple conditions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Can someone please help? I need to multiply quantiity by a list price
dependent upon product (column A) and currency (column C) only if B contains
"SS"

Products a, e & p have the same list price, as do currencies e & u

Column A can contain a, e, m or p
Column B can contain ss or a number of other irrelevant values
Column C can contain e, g or u
Column D is a quantity column

IF Column A = "a", "e" OR "p" AND Column B = "ss" AND Column C = "e" OR "u"
then quantity * 299, IF Column C = "g" then quantity * 199
IF Column A = "m" AND Column B = "ss" AND C = "e" or "u", Q*650, IF C="g",
Q*439

Any ideas?
 
It's a matter of nesting the AND & OR within the IF. If this doesn't work,
play with it.
ONE line
=IF(AND(B12="ss",OR(A12={"a","e","p"})),299,IF(C12="g",199,IF(AND(A12="m",B12="ss",OR(C12={"e","u"})),IF(C12="g",439,0))))
 
Try:

=IF(B2="ss",IF(AND(A2="m",C2="g"),D2*439,IF(A2="m",D2*650,IF(C2="g",D2*199,D2*299))),0)
 
Thanks Don, this really helped.

I am at a point that I thought was correct, but it doesn't return the
correct answer when currency = g and product = m, it returns 199 and should
return 439. My value column contains the formula.

Product Type Currency Quantity Value
m ss g 1 199
a ss g 1 199
e ss e 1 299
m ss e 1 650


This is my current formula

=IF(AND(B2="ss",OR(A2={"a","e","p"}),OR(C2={"e","u"})),(299*D2),IF(C2="g",(199*D2),IF(AND(A2="m",B2="ss",OR(C2={"e","u"})),(650*D2),IF(AND(B2="ss",A2="m",C2="g"),(439*D2),0))))

I have tried playing with the last bit of the formula but with no joy.
Can you please take another look?

Thanks Becki
 
Try my "simpler" formula.

Becksicle said:
Thanks Don, this really helped.

I am at a point that I thought was correct, but it doesn't return the
correct answer when currency = g and product = m, it returns 199 and should
return 439. My value column contains the formula.

Product Type Currency Quantity Value
m ss g 1 199
a ss g 1 199
e ss e 1 299
m ss e 1 650


This is my current formula

=IF(AND(B2="ss",OR(A2={"a","e","p"}),OR(C2={"e","u"})),(299*D2),IF(C2="g",(199*D2),IF(AND(A2="m",B2="ss",OR(C2={"e","u"})),(650*D2),IF(AND(B2="ss",A2="m",C2="g"),(439*D2),0))))

I have tried playing with the last bit of the formula but with no joy.
Can you please take another look?

Thanks Becki
Click to expand...
 
Back
Top