IF Statement, with OR

[Iamaterp]

Hi. I am trying to do the following, but am not sure how IF statements work with EXCEL...

I have the following worksheet in columns A and B...as shown below

AA 100
BB 200
CC 300

IF column A = AA, then column B/10
OR
IF column A=BB, then column B/20
OR
IF column A=CC, then column B/30

Can anyone please help?? THANKS!

[malik641]

Are you looking for a formula or a VBA solution?

[mdmackillop]

This is a Nested If solution. You are allowed up to 7 levels of If.
=IF(A1="AA",B1/10,IF(A1="BB",B1/20,IF(A1="CC",B1/30,"")))

Alternatively, you can use a UDF such as the following. Copy it into a standard code module and enter =Test(A1,B1) on the worksheet


Function Test(Data1 As Range, Data2 As Range)
Select Case Data1
Case Is = "AA"
Test = Data2 / 10
Case Is = "BB"
Test = Data2 / 20
Case Is = "CC"
Test = Data2 / 30
End Select
End Function

[smc2911]

If you wanted something more scaleable (i.e. could easily extend to DD, EE, etc) you could put a table like this in, say, columns G-H (could just as easily be on another sheet):

AA 10
BB 20
CC 30

to specify what you want to divide by in each case, and then use the formula =B1/VLOOKUP(A1,G:H,2,FALSE). Note that the "FALSE" parameter demands an exact match, so if you have, say, BBB in column A and BBB does not appear in your G-H table of denominators, you'll get an error.

Sean.