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:


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:


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


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:


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