"I want to Calculate the Commission Amount for those persons present based on a several Criteria- how can I do that in Excel?" Follow along with Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen as they offer different methods of achieving the Commission Totals.
Dueling Excel Podcast #117...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"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"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #117...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"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"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill: Hey, welcome back.
It's time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
We will be joined by Mike Girvin from Excel Is Fun.
This is our episode 118: IF formula for Commission.
Alright, here's the question sent in from Pakistan.
“I want to calculate the Commission, COMM AMT to be distributed based on who's here” So this is A for absent, P for present.
If both people are here we split the commission 50/50.
If only one person is here, then they get a 100% of the Commission.
If no one is here, if there were no sales reps present then no commission paid that day.
Alright, so when we think about this, what it really comes down to is an IF statement.
If you're here, if there's a P then you're going to share in this commission here, the sum of this commission divided by the number of people that were here.
So let's go IF E5=Present then we're going to split the money so we're going to say the sum of these two numbers.
Now I want to lock down just the G and the H and so I'm going to press F4 three times, one, two, three times.
You see now I have a $ sign before the G and before the H and that range will continue to point to G to H even when I copy it to the right but the five is allowed change to six, seven, eight, nine The formula should look like this at the moment =IF(E5=”P”,SUM($G5:$H5) All right, so everything's good there.
So I’m take that sum divided by the COUNTIF.
we're going look through the As and Ps over here.
Again I'll press F4 one, two, three times and see if it's equal to present and that has to be caps, P in quotes there we go okay so the sum of total Commission for that day divided by the count of the number of people that were there.
Need a quote there and so now we're done with the value of true and if you were not here then you get zero, zip.
The formula should look like this at the moment =IF(E5=”P”,SUM($G5:$H5)COUNTIF($E5:$F5,”P”),0) All right, now what's going to happen when both people are absent, right here the COUNTIF is going to be 0 and something divided by 0 is going to be an error but here's the beautiful thing we'll never get there unless one person is present.
If you're not present and no one is present, this part of the formula will never be evaluated and we won't have to worry about it.
So there's our formula.
We copy it throughout and here where one person is present they get the whole Commission.
Where both people are present they split the Commission.
Where the other person is present; and we can even test it here, make both people be present they get 450 each and then make both people absent and they get nothing.
If it’s one person, they get the whole thing or the other person—works.
There you go.
So I just kind of calculate the total Commission divided by the number of people that are there but only pay it to the people that are there and that avoids the whole division by zero error.
All right Mike let's see what you have.
Mike: Just a moment of silence to appreciate the beauty and efficiency of this formula.
Absolutely beautiful.
MrExcel, why did they call him MrExcel?
Because he can come up with amazing formulas like this and do VBA.
You're not going believe what I tried to do with this problem.
I saw this as a two value LOOKUP problem.
So we had an A and A, a P and A, a P and P and an A and P. Those are all the possibilities and so look at this ridiculous formula.
I tried to use the choose and look up one two three or four and then run four different either numbers or calculations for each one of these values in essence The formula was =CHOOSE(VLOOKUP($E5&$F5,{“AA”,1;”AP”,2;”PA”,3;”PP”,4},2,0),0,CHOOSE(COLUMNS($I12:$I12,0,SUM($G5:$H5)),CHOOSE(COLUMNS($I12:$I12),SUM($G5:$H5),0),SUM($G5:$H5)/2) What happened was I got stuck in a two LOOKUP value conceptual channel and didn't think outside the box.
Absolutely beautiful.
Hey I can't really improve upon that maybe we could look at the same form but what if we didn't have these summary columns here.
We'd have to basically do the same formula except for we'd have to check, calculate that Commission right inside the formula, so we're going do our same test.
Hey is relative cell reference equal to P?
if it is or we're going have to add up these total sale figures and lock that column reference but not the row and multiply times the Commission so that's the only difference with this formula right and we can divide by COUNTIF and I'm going say COUNTIF.
Lock the column one, two, three times on the F4 and then we'll count how many Ps there are.
Now by the way we don't need to put parentheses around this because this will work left to right the order of operations will work perfectly, comma the value of false, hey I'll put a null text string this time double quote double quote instead of a zero.
Ctrl+enter, copied over and down.
The Formula should look like this =IF(E5=”P”,SUM($G5:$H5)*$B5/COUNTIF($E5:$F5,”P”),””) All right so it does the same thing there without that summary column.
10 points to Mr.Excel.
All right, throw it back to MrExcel Bill: hey Mike all right that's good.
Thanks for improving on my formula, getting rid of the intermediate calculations.
I felt so bad when you said you were stuck in a two LOOKUP conceptual channel.
It sounds like being stuck in quicksand, that's quite a formula you had there.
I do want to point out that I've been watching this for the last couple of weeks if you notice in Mike's ribbon; the ribbon tabs, you know Home, Insert, Page layout, Formulas, somehow his data is not in all caps people hate these all caps here.
That's cool Mike that you I managed to change data back to upper and lower case but you didn't change them all to upper and lower case just to just to say hey I know how to do this but I'm not going be obnoxious in doing everywhere.
For those of you who want to get in Excel 2013 back to upper and lower case you have to customize the ribbon and rename this tab to be data with a space.
Cool trick Mike, I've noticed the last couple of times but I didn't have time to mention it.
All right, well I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel and ExcelIsFun.
It's time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
We will be joined by Mike Girvin from Excel Is Fun.
This is our episode 118: IF formula for Commission.
Alright, here's the question sent in from Pakistan.
“I want to calculate the Commission, COMM AMT to be distributed based on who's here” So this is A for absent, P for present.
If both people are here we split the commission 50/50.
If only one person is here, then they get a 100% of the Commission.
If no one is here, if there were no sales reps present then no commission paid that day.
Alright, so when we think about this, what it really comes down to is an IF statement.
If you're here, if there's a P then you're going to share in this commission here, the sum of this commission divided by the number of people that were here.
So let's go IF E5=Present then we're going to split the money so we're going to say the sum of these two numbers.
Now I want to lock down just the G and the H and so I'm going to press F4 three times, one, two, three times.
You see now I have a $ sign before the G and before the H and that range will continue to point to G to H even when I copy it to the right but the five is allowed change to six, seven, eight, nine The formula should look like this at the moment =IF(E5=”P”,SUM($G5:$H5) All right, so everything's good there.
So I’m take that sum divided by the COUNTIF.
we're going look through the As and Ps over here.
Again I'll press F4 one, two, three times and see if it's equal to present and that has to be caps, P in quotes there we go okay so the sum of total Commission for that day divided by the count of the number of people that were there.
Need a quote there and so now we're done with the value of true and if you were not here then you get zero, zip.
The formula should look like this at the moment =IF(E5=”P”,SUM($G5:$H5)COUNTIF($E5:$F5,”P”),0) All right, now what's going to happen when both people are absent, right here the COUNTIF is going to be 0 and something divided by 0 is going to be an error but here's the beautiful thing we'll never get there unless one person is present.
If you're not present and no one is present, this part of the formula will never be evaluated and we won't have to worry about it.
So there's our formula.
We copy it throughout and here where one person is present they get the whole Commission.
Where both people are present they split the Commission.
Where the other person is present; and we can even test it here, make both people be present they get 450 each and then make both people absent and they get nothing.
If it’s one person, they get the whole thing or the other person—works.
There you go.
So I just kind of calculate the total Commission divided by the number of people that are there but only pay it to the people that are there and that avoids the whole division by zero error.
All right Mike let's see what you have.
Mike: Just a moment of silence to appreciate the beauty and efficiency of this formula.
Absolutely beautiful.
MrExcel, why did they call him MrExcel?
Because he can come up with amazing formulas like this and do VBA.
You're not going believe what I tried to do with this problem.
I saw this as a two value LOOKUP problem.
So we had an A and A, a P and A, a P and P and an A and P. Those are all the possibilities and so look at this ridiculous formula.
I tried to use the choose and look up one two three or four and then run four different either numbers or calculations for each one of these values in essence The formula was =CHOOSE(VLOOKUP($E5&$F5,{“AA”,1;”AP”,2;”PA”,3;”PP”,4},2,0),0,CHOOSE(COLUMNS($I12:$I12,0,SUM($G5:$H5)),CHOOSE(COLUMNS($I12:$I12),SUM($G5:$H5),0),SUM($G5:$H5)/2) What happened was I got stuck in a two LOOKUP value conceptual channel and didn't think outside the box.
Absolutely beautiful.
Hey I can't really improve upon that maybe we could look at the same form but what if we didn't have these summary columns here.
We'd have to basically do the same formula except for we'd have to check, calculate that Commission right inside the formula, so we're going do our same test.
Hey is relative cell reference equal to P?
if it is or we're going have to add up these total sale figures and lock that column reference but not the row and multiply times the Commission so that's the only difference with this formula right and we can divide by COUNTIF and I'm going say COUNTIF.
Lock the column one, two, three times on the F4 and then we'll count how many Ps there are.
Now by the way we don't need to put parentheses around this because this will work left to right the order of operations will work perfectly, comma the value of false, hey I'll put a null text string this time double quote double quote instead of a zero.
Ctrl+enter, copied over and down.
The Formula should look like this =IF(E5=”P”,SUM($G5:$H5)*$B5/COUNTIF($E5:$F5,”P”),””) All right so it does the same thing there without that summary column.
10 points to Mr.Excel.
All right, throw it back to MrExcel Bill: hey Mike all right that's good.
Thanks for improving on my formula, getting rid of the intermediate calculations.
I felt so bad when you said you were stuck in a two LOOKUP conceptual channel.
It sounds like being stuck in quicksand, that's quite a formula you had there.
I do want to point out that I've been watching this for the last couple of weeks if you notice in Mike's ribbon; the ribbon tabs, you know Home, Insert, Page layout, Formulas, somehow his data is not in all caps people hate these all caps here.
That's cool Mike that you I managed to change data back to upper and lower case but you didn't change them all to upper and lower case just to just to say hey I know how to do this but I'm not going be obnoxious in doing everywhere.
For those of you who want to get in Excel 2013 back to upper and lower case you have to customize the ribbon and rename this tab to be data with a space.
Cool trick Mike, I've noticed the last couple of times but I didn't have time to mention it.
All right, well I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel and ExcelIsFun.
