Formula for conditional "ifs"?

[Guest]

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

 

[Frank Kabel]

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

 

[JE McGimpsey]

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;"small 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)

 

[Guest]

Hi,

Sorry i think i need to write just a formula only in cell C5?
Thanks and regards.


Ringo

 

[JE McGimpsey]

That's where my first example should go...