Todays Excel Question goes like this... We want to figure out a Tier One bonus, a Tier two bonus and they are tied to a lot of rules; how can we sort this out? And since Bill recognizes immediately that this question is only good for one person's use, this turns into a great lesson on Bonus Calculation, Formula Structure and more. Follow along with Episode #1639 to learn a lot in a short "Learn Excel from MrExcel" Podcast! Thank you Bill!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1639: bonus formula, truth table, AND(), NOT().
Okay, well, you know, I get questions sent in and so, the question is, “Hey, we're trying to figure out a tier 1 bonus and a tier 2 bonus and there's all these rules.
We pay the tier 1 bonus…” and maybe before I even start this-- all right, this question can only possibly help one person, right?
But it's not that you are going to have this exact same bonus program.
I want to talk about the way to solve these types of questions in general.
All right, so, we have to go from words to a formula.
So, here are the words: tier 1 bonus is payable if A is between 20 and 30% and B is between 2 and 3% or A is between 20 and 30 % and B is over 3% or A is over 30% and B is between 2 and 3%.
Then there's a tier 2 bonus if A is greater than 30 and B is greater than 3.
This is all very confusing.
Anytime I get this, I'm put on-- I started out as a-- in school as an electrical engineer.
I didn't last long but I learned about truth tables and Boolean logic and I set one of these up.
So, we have A and B. Here going down the side is A: if it's less than 20%, 20-30%, greater than 30%.
B: less than 2%, 2-3%, greater than 3%.
So, there's nine possible conditions that we're testing for in these formulas.
It looks like tier 2 is only paid in the case where they beat everything.
So, if they're greater than 3% on B and greater than 30% on A, then we are tier 2.
Tier 1 is if A is 20-30% and B is 2-3%, so, there's tier 1.
It's also if A is between 20 and 30% and B is over 3%, that's tier 1.
Then if A is over 30 and B is between 2 and 3, tier 1.
All right, and then the rest of these are no bonus, so, I'll put no there, no, Ctrl + Enter.
All right, and the beautiful thing about a truth table is we're looking for rectangles.
We're looking for rectangles that have something in common and there's a rectangle right here, these four cells are getting some sort of a bonus, so, that is a-- that's one rectangle.
Then, another rectangle is this one here where we qualify for tier 2.
All right, so we just have to build formulas to represent those rectangles.
We don't have to worry about all the ones where nothing is happening.
All right, so, here's how I think about this.
First, the tier 2 rectangle is smaller; so, I'm going to-- I'm going to solve that one first.
Equal-- two things have to happen: A has to be greater than—I’m going to say or equal to .3, which is 30%.
Also, B has to be greater than or equal to .03, which is 3%.
All right, so, in that one case, A is greater than 30, B is greater than 3, we’re paying tier 2.
All right, so, I've now handled the green rectangle.
That one's done.
All right, now, for the blue rectangle, tier 1.
Really, what it comes down to is we have to be above 20% on A and we have to be above 2% on B and I'm assuming we're not paying both bonuses.
It can't have already qualified for tier 2.
All right, so, all of those rules that we had and all of that-- those English sentences, it really comes down to let's make sure we're above 20%, let's make sure we're above 2%, and let's make sure that we didn't already pay tier 2.
So, again, the AND function.
Make sure that A is greater than or equal to .2 comma, make sure that B is greater than or equal to .02 comma, and then not that we paid tier 2.
All right, so, there we go.
Copy that one down and you'll see the cases where we are paying a tier 1 commission.
All right, so, these two formulas happen to solve this particular problem of the person who sent this in.
The bigger lesson here is, how do we go from words like these-- this is how the VP of Sales wrote the commission plan, to a truth table where we look for the rectangles and then once we have the rectangles, to be able to write the actual, in this case, AND functions to figure out the first rectangle, the green rectangle, tier 2 and then the second rectangle, tier 1.
There we have our formulas.
So, once you get to the truth table, I think life is a little easier.
Hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1639: bonus formula, truth table, AND(), NOT().
Okay, well, you know, I get questions sent in and so, the question is, “Hey, we're trying to figure out a tier 1 bonus and a tier 2 bonus and there's all these rules.
We pay the tier 1 bonus…” and maybe before I even start this-- all right, this question can only possibly help one person, right?
But it's not that you are going to have this exact same bonus program.
I want to talk about the way to solve these types of questions in general.
All right, so, we have to go from words to a formula.
So, here are the words: tier 1 bonus is payable if A is between 20 and 30% and B is between 2 and 3% or A is between 20 and 30 % and B is over 3% or A is over 30% and B is between 2 and 3%.
Then there's a tier 2 bonus if A is greater than 30 and B is greater than 3.
This is all very confusing.
Anytime I get this, I'm put on-- I started out as a-- in school as an electrical engineer.
I didn't last long but I learned about truth tables and Boolean logic and I set one of these up.
So, we have A and B. Here going down the side is A: if it's less than 20%, 20-30%, greater than 30%.
B: less than 2%, 2-3%, greater than 3%.
So, there's nine possible conditions that we're testing for in these formulas.
It looks like tier 2 is only paid in the case where they beat everything.
So, if they're greater than 3% on B and greater than 30% on A, then we are tier 2.
Tier 1 is if A is 20-30% and B is 2-3%, so, there's tier 1.
It's also if A is between 20 and 30% and B is over 3%, that's tier 1.
Then if A is over 30 and B is between 2 and 3, tier 1.
All right, and then the rest of these are no bonus, so, I'll put no there, no, Ctrl + Enter.
All right, and the beautiful thing about a truth table is we're looking for rectangles.
We're looking for rectangles that have something in common and there's a rectangle right here, these four cells are getting some sort of a bonus, so, that is a-- that's one rectangle.
Then, another rectangle is this one here where we qualify for tier 2.
All right, so we just have to build formulas to represent those rectangles.
We don't have to worry about all the ones where nothing is happening.
All right, so, here's how I think about this.
First, the tier 2 rectangle is smaller; so, I'm going to-- I'm going to solve that one first.
Equal-- two things have to happen: A has to be greater than—I’m going to say or equal to .3, which is 30%.
Also, B has to be greater than or equal to .03, which is 3%.
All right, so, in that one case, A is greater than 30, B is greater than 3, we’re paying tier 2.
All right, so, I've now handled the green rectangle.
That one's done.
All right, now, for the blue rectangle, tier 1.
Really, what it comes down to is we have to be above 20% on A and we have to be above 2% on B and I'm assuming we're not paying both bonuses.
It can't have already qualified for tier 2.
All right, so, all of those rules that we had and all of that-- those English sentences, it really comes down to let's make sure we're above 20%, let's make sure we're above 2%, and let's make sure that we didn't already pay tier 2.
So, again, the AND function.
Make sure that A is greater than or equal to .2 comma, make sure that B is greater than or equal to .02 comma, and then not that we paid tier 2.
All right, so, there we go.
Copy that one down and you'll see the cases where we are paying a tier 1 commission.
All right, so, these two formulas happen to solve this particular problem of the person who sent this in.
The bigger lesson here is, how do we go from words like these-- this is how the VP of Sales wrote the commission plan, to a truth table where we look for the rectangles and then once we have the rectangles, to be able to write the actual, in this case, AND functions to figure out the first rectangle, the green rectangle, tier 2 and then the second rectangle, tier 1.
There we have our formulas.
So, once you get to the truth table, I think life is a little easier.
Hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
