| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional formula with multiple conditions and answers. | Excel Worksheet Functions | |||
| Multiple conditions for conditional formatting | Excel Worksheet Functions | |||
| Multiple conditions in Conditional Formatting | Excel Discussion (Misc queries) | |||
| Conditional Sum and multiple conditions across different sheets | Excel Worksheet Functions | |||
| Banding with Conditional Formatting with Multiple Conditions | Excel Worksheet Functions | |||
Hybrid Mode
