Commission Formula in Excel?

Question

I am trying to write a Commission Formula in Excel to calculate the Sales Commission based on this criteria:

Monthly Sales $0-50K => 3% Commission

Monthly Sales >$50K => 6%

However, when Accumulative Sales Adds up to $1000,000/ Year Cycle or over, then the Commission become flat ==> 8% Commission.

For example: in the 8th month the Monthly Sale = $120K, and by making this sale the Accumulative Sales adds up to $1280,000

How can I bulld an Commision Formula in Excel to Calculate all this possibilities.

thank you all, please write me back if you are sure of your answer.

Answer 1

You probably need a cell just for commission rate and the formula for that cell would be a compound  if -- then -- else statement.

If (Yearly accumulated sales cell) >= 1000000 then  = .08

else if (monthly sales cell) => 50000 then  = .06

else  = .03

Once that is set up use the value of commission rate cell in all calculations involving your commission.  No guarantees, I haven't done data base or spreadsheets in over 12 years.

Answer 2

There are a number of different ways you can do this.  Here's how to do it with nested IF formulas:

Assuming that your list of monthly sales is in column A, starting in A1:

This formula will check if the sum of the monthly sales column has reached 1000,000.  If it has, it results in 0.08.  Otherwise if A1 > 50000 it will return 0.06, or if A1 is over 0 it will result in 0.03.  If all else fails it will result in zero.

=IF(SUM($A$1:A1)>=1000000, 0.08, IF(A1>50000, 0.06, IF(A1>0, 0.03, 0)))

If the SUM() formula to find the accumulative sales won't work (say if the formula needs to be smart enough to start fresh each January or something) then message me when you add additional details and I'll see about tweaking the formula.

Answer 3

You are going to need multiple formulas and some visual basic programming to accomplish this. A simple formula typed into the formula bar will not do everything you need.

Good Luck!