I'm currently new in excel. I'm wondering how can I create multiple IF statement in one column
i have 2 separate if statements at the moment:
=IF(AND(B2>500,B2<600),B2*0.2,"Not Late")
=IF(AND(B2>400, B2<500), B2*0.15, "No late deliveries")
I would like ot add these 2 if statements and more if statements into one column..
These appear to be mutually exclusive.Originally Posted by lienlee
In the first formula (let's call this one Case A) as I read it, when I find if B2 is greater than 500 and less than 600 then I'll calculate and enter B2*0.2 (20% of B2) otherwise I will enter "Not Late".
The second formula conflicts with the first, because this second one, I'll call Case B, now it says if B2 is greater than 400 and less than 500, I'll calculate and enter B2*0.15 (15% of B2) otherwise I will enter "No late deliveries").
I suspect that you may really want to set 2 cells as the result of each test. So, when B2>500 and B2<600 then C2 = B2*.2 and D2 = "Not Late," and similarly when B2>400 and B2<500 then C2=B2*.15 and D2="No late deliveries".
Rather than nesting IF statements, this can probably be solved more easily with a Select Case / End Select construction.
[vba]
Select Case B2
Case >500 and <600
C2 = B2 * 0.20
D2 = "Not late"
Case >400 and <500
C2 = B2 * 0.15
D2 = "Not late"
Case Else
C2 = B2
D2 = "Houston, we have a problem."
End Select
[/vba]
One of the questions you need to ask yourself: what do you do when B2 is exactly equal 600, 500, 400? You need to include those possibilities in one range or another but not both. So, perhaps, Case >500 and <=600. Including Case Else assures that any value that escapes for case logic will be flagged. Watch those boundary conditions.
Happy coding,
Ron
Windermere, FL
Posting Permissions
Reply With Quote