Share via

Excel Formula for working out Commission

Anonymous
2016-04-07T02:02:38+00:00

I'm trying to calculate my annual commissions in a schedule. Everytime I think I've go it I seem to get an error. I've retought and am starting to confuse myself. I was trying to us SUMIF.

I get 50% commission on call sales up until $75,000.  I then get 60% on sales to $105,000, 70% for sales to $125000 and the 80% above.

I enter each total commission into a column and then the intention was that the next column would caluculate my commission.

The formula would ask the follow questions:

  1. Is the figure greater that $75,000. I no, then it calculates the final figure 50%
  2. If the figure is above $75,000 it would calculate 50% of $75,000 and then add 60% of the amount above $75,000 but below $105,000.
  3. Obviously it would then do the same calculation from $105,000 to $125000 at 70% and then above $125000 at 80%

In my mind I can understand it, but the formula gets too complicated once I start trying to add additional conditions.

Guidance on the correct formula format or formula type would be appreciated.

It would look something like :

  • If A1<75,000 then (A1*0.5)
  • If A1>75,000 but less than 105,000 then (37500+((A1-75000)*0.6)
  • If A1>105,000 but less than 125,000 then ((55500)+((A1-105000)*0.7) 
  • If A1>125000 then (69500+((A1-125000)*0.8)

Cheers

Mike

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2016-04-08T09:31:05+00:00

I selected I2:I5 and named this range Tiers by typing this name in the Name box on the left hand side of the formula bar.

Similarly, I selected K2:K5 and named it Rates.

You can use named ranges in formulas the same way you can use cell references. It sometimes makes formulas more readable.

Was this answer helpful?

0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2016-04-07T22:28:35+00:00

I don't understand how you arrive at the commission when the cumulative sales pass $75,000.

I have uploaded a sample workbook to DropBox with what I think it should be, using the idea from my previous reply:

https://www.dropbox.com/s/71hwqi6bmmq0m2w/Commission.xlsx?dl=1

Was this answer helpful?

0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-04-07T22:07:09+00:00

    Hi Amit

    Your answer seems right, but Excel rejects it as an error.

    Below is the table I am trying to install the formula into, to avoid manually calculating the figures each time, particularly when the is a change over between scales, such as Row '8', the commission cuts across the $75,000 split line, which I currently manually calculate by entering the formula: =((A8-6925)*0.5)+(6295*0.6).

    Essentially each company commission is in column A.  Column B is the adding/running total and column C is the cells in which I tried to put in your formula, changing 'A1' to 'B2' in the first occurrence and 'A1' to 'A2' in the second.

     Thoughts? 

    Cheers

    Mike

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-04-07T06:55:05+00:00

    To get the calculation in a single cell, you may use below formula:

    =SUMPRODUCT((A1>{0,75000,105000,125000})*(A1-{0,75000,105000,125000}),{0.5,0.1,0.1,0.1})

    Regards,

    Amit Tandon

    Was this answer helpful?

    0 comments
  3. HansV 462.6K Reputation points
    2016-04-07T05:48:17+00:00

    Create a table as in the screenshot below, with the differential commission (the difference with the rate for the previous tier) in column C.

    With sales in B8, the formula for the total commission is

    =SUMPRODUCT((B8>A2:A5)*(B8-A2:A5),C2:C5)

    Was this answer helpful?

    0 comments