Calculate commission percentage based on a commission schedule in Microsoft Excel 2010

 

In this article we will learn how to calculate the commission percentage based on a commission schedule, we will use the IF function in Microsoft Excel 2010.     

IF: - Checks whether a condition is met and returns one value if True and another value if False.

The syntax of “IF” function =if(logical test,[value_if_true],[value_if_false])

The function will perform a logical test and give the result based on whether its true or false.

For example, Cells A2 and A3 contain the numbers 3 and 5. If the number is 3, the formula should display “Yes”, else “No”.

=IF (A1=3,”Yes”,”No”)

img1

Now let’s take an example to understand how to calculate the commission percentage based on a commission schedule.

We have sales data. Column A contains Agent’s city, Column B contains Agents, column C contains the sales amount. In column D, we need to calculatethe commission % age as per the criteria.

img2

The criteria is:-

1. If the sales amount is 1000,the commissionshouldbe 20%.
2. If the sales amount is 500,the commissionshould be 15%.
3. If the sales amount is 250,the commissionshould be 10%.
4. If the sales amount is 100,the commissionshould be 5%.
5. For sales less than 100,there should be no commission.

To calculatethe commission percentage based on the schedule, follow the below given steps:-

  • Select the cell D2.

Write the formula
=IF(C2>1000,20,IF(C2>500,15,IF(C2>250,10,IF(C2>100,5,0))))&”%”.

  • Press Enter on your keyboard.
  • The function will return the commission %age as per the criteria.

img3

  • To return the commission %age for all the agents, copy the same formula by pressing the key Ctrl+C and paste in the range D3:D11 by pressing the key Ctrl+V on your keyboard.

img4

This is the way we can calculate the commission percentage based on a commission schedule in Microsoft Excel.



2 thoughts on “Calculate commission percentage based on a commission schedule in Microsoft Excel 2010

  1. I need to calculate the same with following added condition. Please help.
    >1000, commission shall on prorate basis.
    Ex:
    If its 2000, it would be 40%
    if its 1500, commission would be 20+15=35% ans so on.
    Please provide formula

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>