-
IF Statement, with OR
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!
-
-
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
[VBA]
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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules