Formula for conditional "ifs"?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ringo tan - 01 Dec 2004 05:09 GMT

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

Reply to this Message

Frank Kabel - 01 Dec 2004 05:43 GMT

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

Signature

--
Regards
Frank Kabel
Frankfurt, Germany

> Hi,
>
[quoted text clipped - 24 lines]
>
> Ringo

Reply to this Message

JE McGimpsey - 01 Dec 2004 05:48 GMT

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)

> Hi,
>
[quoted text clipped - 24 lines]
>
> Ringo

Reply to this Message

ringo tan - 01 Dec 2004 06:31 GMT

Hi,

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

Ringo

> Your example is somewhat ambiguous - is it always the case that a "Y" in
> A2 adds $500 to C5?
[quoted text clipped - 43 lines]
> >
> > Ringo

Reply to this Message

JE McGimpsey - 01 Dec 2004 13:42 GMT

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

> Sorry i think i need to write just a formula only in cell C5?

Reply to this Message