Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula for conditional "ifs"?
Hi,
I have a cell say "C2" containing a dropdown list for "small biz", "corporate" and "enterprise". I have another cell say "C5" that refers to cell "C2". a. If user select "small biz" in C2, C5 will show a price of say $1000. b. If user select "corporate" in C2, C5 will show a price of say $2000. c. If user select "enterprise" in C2, C5 will show a price of say $3000. Now, i need to add another condition to the above. I have another cell A2 for user to enter a "Y" or leave as blank. d. If user select "small biz" in C2 AND also enter a "Y" in A2, C5 will show a price of say $1500. e. If user select "corporate" in C2 AND also enter a "Y" in A2, C5 will show a price of say $2500. f. If user select "enterprise" in C2 AND also enter a "Y" in A2, C5 will show a price of say $3500. Can anyone give advice on how to write a function to incorporate all 6 conditions namely a,b,c,d,e,f in cell "C5", so that with or without "Y" in A2 + the result user selected in cell C2 in order to produce 6 different prices of $1000,$1500,$2000,$2500,$3000,$3500? Thanks in advance. Ringo |
#2
|
|||
|
|||
Hi
I would create a lookup table on a separate sheet (e.g. called 'lookup') with the following format A B C 1 Y N 2 small 1500 1000 3 corp. 2500 2000 4 .... now use the following formula =INDEX('lookup'!A1:C20,MATCH(C2,'lookup'!A1:A20,0) ,MATCH(A2,'lookup'!A1 :C1,0)) -- Regards Frank Kabel Frankfurt, Germany "ringo tan" schrieb im Newsbeitrag ... Hi, I have a cell say "C2" containing a dropdown list for "small biz", "corporate" and "enterprise". I have another cell say "C5" that refers to cell "C2". a. If user select "small biz" in C2, C5 will show a price of say $1000. b. If user select "corporate" in C2, C5 will show a price of say $2000. c. If user select "enterprise" in C2, C5 will show a price of say $3000. Now, i need to add another condition to the above. I have another cell A2 for user to enter a "Y" or leave as blank. d. If user select "small biz" in C2 AND also enter a "Y" in A2, C5 will show a price of say $1500. e. If user select "corporate" in C2 AND also enter a "Y" in A2, C5 will show a price of say $2500. f. If user select "enterprise" in C2 AND also enter a "Y" in A2, C5 will show a price of say $3500. Can anyone give advice on how to write a function to incorporate all 6 conditions namely a,b,c,d,e,f in cell "C5", so that with or without "Y" in A2 + the result user selected in cell C2 in order to produce 6 different prices of $1000,$1500,$2000,$2500,$3000,$3500? Thanks in advance. Ringo |
#3
|
|||
|
|||
Your example is somewhat ambiguous - is it always the case that a "Y" in
A2 adds $500 to C5? In that case, here's one way: =LOOKUP(C2,{"corporate",2000;"enterprise",3000;"sm all biz",1000}) + 500 * (A2="Y") Or, if not, you could set up a table, say, on Sheet2: A B C 1 small biz 1000 1500 2 corporate 2000 2500 3 enterprise 3000 3500 and use =VLOOKUP(C2,Sheet2!A:C,2+(A2="Y"),FALSE) In article , ringo tan wrote: Hi, I have a cell say "C2" containing a dropdown list for "small biz", "corporate" and "enterprise". I have another cell say "C5" that refers to cell "C2". a. If user select "small biz" in C2, C5 will show a price of say $1000. b. If user select "corporate" in C2, C5 will show a price of say $2000. c. If user select "enterprise" in C2, C5 will show a price of say $3000. Now, i need to add another condition to the above. I have another cell A2 for user to enter a "Y" or leave as blank. d. If user select "small biz" in C2 AND also enter a "Y" in A2, C5 will show a price of say $1500. e. If user select "corporate" in C2 AND also enter a "Y" in A2, C5 will show a price of say $2500. f. If user select "enterprise" in C2 AND also enter a "Y" in A2, C5 will show a price of say $3500. Can anyone give advice on how to write a function to incorporate all 6 conditions namely a,b,c,d,e,f in cell "C5", so that with or without "Y" in A2 + the result user selected in cell C2 in order to produce 6 different prices of $1000,$1500,$2000,$2500,$3000,$3500? Thanks in advance. Ringo |
#4
|
|||
|
|||
Hi,
Sorry i think i need to write just a formula only in cell C5? Thanks and regards. Ringo "JE McGimpsey" wrote: Your example is somewhat ambiguous - is it always the case that a "Y" in A2 adds $500 to C5? In that case, here's one way: =LOOKUP(C2,{"corporate",2000;"enterprise",3000;"sm all biz",1000}) + 500 * (A2="Y") Or, if not, you could set up a table, say, on Sheet2: A B C 1 small biz 1000 1500 2 corporate 2000 2500 3 enterprise 3000 3500 and use =VLOOKUP(C2,Sheet2!A:C,2+(A2="Y"),FALSE) In article , ringo tan wrote: Hi, I have a cell say "C2" containing a dropdown list for "small biz", "corporate" and "enterprise". I have another cell say "C5" that refers to cell "C2". a. If user select "small biz" in C2, C5 will show a price of say $1000. b. If user select "corporate" in C2, C5 will show a price of say $2000. c. If user select "enterprise" in C2, C5 will show a price of say $3000. Now, i need to add another condition to the above. I have another cell A2 for user to enter a "Y" or leave as blank. d. If user select "small biz" in C2 AND also enter a "Y" in A2, C5 will show a price of say $1500. e. If user select "corporate" in C2 AND also enter a "Y" in A2, C5 will show a price of say $2500. f. If user select "enterprise" in C2 AND also enter a "Y" in A2, C5 will show a price of say $3500. Can anyone give advice on how to write a function to incorporate all 6 conditions namely a,b,c,d,e,f in cell "C5", so that with or without "Y" in A2 + the result user selected in cell C2 in order to produce 6 different prices of $1000,$1500,$2000,$2500,$3000,$3500? Thanks in advance. Ringo |
#5
|
|||
|
|||
That's where my first example should go...
In article , ringo tan wrote: Sorry i think i need to write just a formula only in cell C5? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot change a formula | Excel Discussion (Misc queries) | |||
Simple formula doesn't quite add up | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) | |||
I want the results of a formula to show in cell, NOT THE FORMULA! | Excel Discussion (Misc queries) |