MrExcel's Learn Excel #768 - Multiple IF

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 30, 2009.
How do you deal with many levels in an IF statement? Episode 768 discusses nesting IF statements.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
maxresdefault.jpg


Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Someone asked a question about using IF when we have multiple conditions and there's 2 different ways to do this.
We're going to cover 1 way today and then the other way tomorrow.
Typically, the IF function works out where we pass a logical test, something is going to be either true or false, and then what to do if it's true and what to do if it's false.
So, let’s say that we have a VP of Sales who comes up with a plan that says, hey, we're going to pay a 1% bonus any time you sell a deal larger than $20,000.
So, in that case, the logical test is F2 > 20,000, put a , and then what to do if it's true?
Well, if it's true, we're paying 0.01 times F2 and then a , and what to do if it's false?
Well, if it's false, then we pay 0.
Double click the fill handle and we'll copy that down, and you see that only the deals above 20,000 get a bonus.
[ =IF(F2>20000,0.01*F2,0) ] Okay. Well, now, what if there's a second option? A second option.
They say if it's > $20,000, we're going to pay a 1% bonus, but if it's > $15,000, then we're going to pay a quarter of a % bonus.
So, in this case, it's relatively simple in that we just replace this final 0 with a brand new IF statement.
So, IF F2 is > $15,000, then 0.0025 times F2, otherwise 0.
Make sure to put the ). Copy that down and you'll see that now, here in this cell, we're getting a quarter of a % bonus.
[ =IF(F2>20000,0.01*F2,IF(F2>15000,0.0025*F2,0)) ] Now, that's the common use for having to add more conditions.
The one “got you” that you have to be aware of is that you cannot nest more than 7 IF statements in a single formula.
In Excel 2007, you can go up to 32 but, frankly, I don't think you should do that.
If you get beyond 7, you really should switch over to VLOOKUP.
Now, in tomorrow's netcast, we're going to take a look at taking the original IF statement and changing it around using either an AND or an OR function in order to really have 2 conditions for that bonus to be paid.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top