Multiple IF THEN ELSE statment
 

I have a list that has column for pricing and a column for price code. the
price code has 1 of 5 different letters for discounts. codes are as follows:
d,f,k,n,v. each code has a different vaule. Can I make a multiple IF THEN
ELSE statement that would choose the correct formula? ie.. IF(code=d, then
price*.8 else IF code=f then price*.85 else IF code=k then price*.9 else IF
code=n then price*.75 else IF code=v then price*.7) all in one function?
--
MsG

=A1*CHOOSE(MATCH(B1,{"d";"f";"k";"n";"v"},0),0.8,0 .85,0.9,0.75,0.7)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mark G" wrote in message
...
I have a list that has column for pricing and a column for price code. the
price code has 1 of 5 different letters for discounts. codes are as
follows:
d,f,k,n,v. each code has a different vaule. Can I make a multiple IF
THEN
ELSE statement that would choose the correct formula? ie.. IF(code=d,
then
price*.8 else IF code=f then price*.85 else IF code=k then price*.9 else
IF
code=n then price*.75 else IF code=v then price*.7) all in one function?
--
MsG

Hi

Another solution:
=A1*((B1="d")*0.8+(B1="f")*0.85+(B1="k")*0.9+(B1=" n")*0.75+(B1="v")*0.7)


Arvi Laanemets


"Mark G" wrote in message
...
I have a list that has column for pricing and a column for price code. the
price code has 1 of 5 different letters for discounts. codes are as
follows:
d,f,k,n,v. each code has a different vaule. Can I make a multiple IF
THEN
ELSE statement that would choose the correct formula? ie.. IF(code=d,
then
price*.8 else IF code=f then price*.85 else IF code=k then price*.9 else
IF
code=n then price*.75 else IF code=v then price*.7) all in one function?
--
MsG

And yet another approach would be to put the codes & discounts in a table,
then use a VLOOKUP() formula. The primary reason for doing it this way is if
you ever saw the need to add new price codes or modify the discounts. Much
easier to modifiy the table than grinding through the formulas

Duke

"Mark G" wrote:

I have a list that has column for pricing and a column for price code. the
price code has 1 of 5 different letters for discounts. codes are as follows:
d,f,k,n,v. each code has a different vaule. Can I make a multiple IF THEN
ELSE statement that would choose the correct formula? ie.. IF(code=d, then
price*.8 else IF code=f then price*.85 else IF code=k then price*.9 else IF
code=n then price*.75 else IF code=v then price*.7) all in one function?
--
MsG

Hi

I'm always keeping saying: "In Excel you can everything do at least i 3
different ways!". Lookup table isn't the only way to make this solution
dynamic, p.e. you can use (dynamic in current situation) names for this
instead :-)


Arvi Laanemets


"Duke Carey" wrote in message
...
And yet another approach would be to put the codes & discounts in a table,
then use a VLOOKUP() formula. The primary reason for doing it this way is
if
you ever saw the need to add new price codes or modify the discounts.
Much
easier to modifiy the table than grinding through the formulas

Duke

"Mark G" wrote:

I have a list that has column for pricing and a column for price code.
the
price code has 1 of 5 different letters for discounts. codes are as
follows:
d,f,k,n,v. each code has a different vaule. Can I make a multiple IF
THEN
ELSE statement that would choose the correct formula? ie.. IF(code=d,
then
price*.8 else IF code=f then price*.85 else IF code=k then price*.9 else
IF
code=n then price*.75 else IF code=v then price*.7) all in one function?
--
MsG

Thanks - worked great.

"Bob Phillips" wrote:


=A1*CHOOSE(MATCH(B1,{"d";"f";"k";"n";"v"},0),0.8,0 .85,0.9,0.75,0.7)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mark G" wrote in message
...
I have a list that has column for pricing and a column for price code. the
price code has 1 of 5 different letters for discounts. codes are as
follows:
d,f,k,n,v. each code has a different vaule. Can I make a multiple IF
THEN
ELSE statement that would choose the correct formula? ie.. IF(code=d,
then
price*.8 else IF code=f then price*.85 else IF code=k then price*.9 else
IF
code=n then price*.75 else IF code=v then price*.7) all in one function?
--
MsG