ExcelBanter - conditional formula with multiple conditions

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - conditional formula with multiple conditions (https://www.excelbanter.com/excel-discussion-misc-queries/141853-conditional-formula-multiple-conditions.html)

conditional formula with multiple conditions

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?

conditional formula with multiple conditions

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)

"Becksicle" wrote:

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?

conditional formula with multiple conditions

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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Becksicle" wrote in message
...
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?

conditional formula with multiple conditions

Try my "simpler" formula.

"Becksicle" wrote:

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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Becksicle" wrote in message
...
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?

conditional formula with multiple conditions

Thanks Toppers
This works

"Toppers" wrote:

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)

"Becksicle" wrote:

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?