Example Of Nested If Conditions In Excel To Calculate Sales Commissions

Excel function tutorials

I’ve done a couple of similar examples but here is one question I got a few days ago:

I am trying to write a formula to pay a percentage up to a certain amount and then increase the percentage once a rep exceeds that amount. Ex: Rep brings in $2,000.
Pay 50% for the first $600
Pay 60% for the next $600
Pay 70% for the next $600
Pay 80% for everything over $1,800.

How will I get it done? Let’s start from the beginning. If my sales number is in cell C6, then I’d start off by checking if the sales is above $1800. If it is, then I’d simply add:

50% of $600+60% of $600 + 70% of $600 to the 80% of everything over $1800. In short it would be:

=IF(C6>=F9,1080+(C6-F9)*H9,1)

As you can see, it works well for that kind of number:

But would not work for a number under $1800:

So I will then work my way to that category:

=IF(C6>=F9,I6+I7+I8+(C6-F9)*H9,IF(C6>F8,I6+I7+H8*(C6-F8),2))

Of course, I must also adjust for numbers in the second category, I will do so with the following:

=IF(C6>=F9,I6+I7+I8+(C6-F9)*H9,IF(C6>F8,I6+I7+H8*(C6-F8),IF(C6>F7,I6+H7*(C6-F7),3)))

And finally, I can replace the “default” here. If the number is not in category 2, we can assume that it is in category 1 (less than $600 in this case) so I simply replace 3 by:

=IF(C6>=F9,I6+I7+I8+(C6-F9)*H9,IF(C6>F8,I6+I7+H8*(C6-F8),IF(C6>F7,I6+H7*(C6-F7),C6*H6)))

And here you go, it works and easy to adjust. You can modify the percentages, levels, etc.

You can download the spreadsheet here!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************