EXCEL IF THEN

[KN3M3SIS]

Hello,

I have a column (H:19,H:76) that assigns the variables AVE, AO, AE, BVE, BO, BE to various people.

1) I want to assign a value to each of the 6 variables
2) Then I want to have a column that does something like:

IF (CELL H# = AO)
THEN (CELL R# = BONUS * .045)

ELSE IF (CELL H# = AE)
THEN (CELL R# = BONUS * .043)

ELSE IF (ETC..............)

END

3) This formula should apply to all the tables in row R.

So it reads from H and then puts the data into R.

That's just how the logic works, I know I'm not typing it out right, can someone please help.

Thanks in advance

 

[xlbo]

Have a lookup table which looks like:
AVE 0.48
AO 0.45
AE 0.43
BVE
etc
etc

and name the table range as LkupTbl
Then in col R, you can use
= BONUS * vlookup(H#,LkupTbl,2,false)
where BONUS is picked up from a cell ref presumably

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Dreamboat]

Make a table on another worksheet of each variable and its corresponding value.

Use VLOOKUPs to grab the values you want.

See MyVlookup download at

http://www.theofficeexperts.com/downloads.htm

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[Zathras]

Ok, if you absolutely must do it with IF/THEN you can use this formula (not recommended - see below):
[blue]

=BONUS*IF(H19="AVE",0.47,IF(H19="AO",0.45,IF(H19="AE",0.43,IF(H19="BVE",0.41,IF(H19="BO",0.39,IF(H19="BE",0.37))))))

[/color]
But if the percentages ever change, or you add more categories you will have a horrible time making the changes.

Here is a slightly better way if you absolutely insist on having the values all in a single formula (not recommended - see below):
[blue]

=BONUS*CHOOSE(MATCH(H19,{"AVE","AO","AE","BVE","BO","BE"},0),0.47,0.45,0.43,0.41,0.39,0.37)

[/color]

But I would recommend using a separate table to contain the codes and percentages and using VLOOKUP with that table:

Set up the table this way (use 2 columns):
[blue]

AVE   0.47
AO    0.45
AE    0.43
BVE   0.41
BO    0.39
BE    0.37

[/color]
Then assign the range name BONUSTABLE to the 12 cells, and use this formula:
[blue]

=BONUS*VLOOKUP(H19,BONUSTABLE,2,0)

[/color]
If the percentages change, the changes only affect the BONUSTABLE range. If more categories are added, just extend the BONUSTABLE range and add them. You will never have to modify the formulas in column "R" again.