Multiple IF THEN ELSE statment

[Mark G]

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?

 

[Bob Phillips]

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

 

[Arvi Laanemets]

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

 

[Duke Carey]

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

 

[Arvi Laanemets]

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

 

[Mark G]

Thanks - worked great.

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