# Sales Commission Calculation

Jan 4, 2010

I need to know what formulas to put into the cells in excel to make the following sales compensation example compute properly:

Time
Period Draw
Paid Actual
Commissions Owed to
Company Commission
Paid Total
Earnings Month 1\$3,000\$4,000-\$1,000\$4,000Month 2 \$3,000\$2,000\$1,000-\$3,000Month 3 \$3,000\$5,000(\$1,000)\$1,000\$4,000TOTALS\$9,000\$11,000-\$2,000\$11,000

## Sales Commission Formula Required

Jan 14, 2009

I have a new sale structure to put in place the commission is paid in the following way:

below 1500 zero commission
between 1501 and 3000, commission at 16%
between 3001 and 8000, commission at 23%
above 8001, commission paid at 30%
Ergo if you generate 5000 you would be paid 700 ie nothing for the first 1500, 16% of the second 1500 and 23% of the remaining 2000. ( I hope my maths is correct! )

I have tried to manipulate other solutions using sumproduct but my knowledge is poor, the formula I have tried manipulating is =SUMPRODUCT( (A2 > {0,1500,3000,8000}) * (A2 - {0,1500,3000,8000}) * {0,0.16,0.23,0.3}). I prefer single line formula rather than lookups as staff will not be able to see commission rates easily.

## Setting Up A Vlookup Table For Sales Commission

Mar 12, 2014

I'm trying to set up a Vlookup table for commission rates but all my answers are 0. What I'm trying to do is set up Vlookup to return total sales less than 700,000 have 0% commission, 700,000 to 749999 10%, 750,000-799,999 11%, etc. . The commission rate increases by 1% for each additional 50,000 in sales, with the highest commission rate 20%.

## Creating Commission Calculation?

May 15, 2014

I'm having trouble creating a commission calculation.

The commission schedule is paid on a tiered rate (below), and occasionally there will be recoveries due to client non-payment, which comes out of commission. There is also a budget variance every month. I'd like to have the Monthly Budget in cell A1, the Revenue in cell A2, Recoveries in cell A3 and the Commission (formula) in cell A4. I only need the formula for cell A4, everything else will be entered manually.

Revenue Schedule
\$0-\$14,000 = 0%
\$14,001 - \$18,000 = 7.5%
\$18,001 - \$BUDGET = 10%
\$102% BUDGET - Forever = 15%

Note that once you hit 18K, the 14-18K does not jump up to 10%, it stays at the 7.5%.

## Formula / Function For Commission Calculation?

Jul 26, 2006

if i could get a hand creating a commission calculation.. here is what i'm looking for and my brain hurts trying to make it... I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..

\$0 - \$10,000 - 60% commission
\$10,001 - \$15,000 - 65% commission
\$15,001+ - 70% commission..

i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is \$12,000, i don't know how to have it calculate the first \$10,000 at 60% and the last \$2,000 at 65%.

ps.. my excel sheet is set up as follows:
Rows a-g (stuff that is irrelivant)
row h, gross fees
row i, commission (in dollars)

## Weekly Average Commission Calculation

May 5, 2009

I hope this question has not been addressed but the closest I can find is in this link:
[url]

I am now a commission based contractor who started earlier this year & I want to be able to calculate my current average weekly income which should fluctuate greatly. I have a spreadsheet that works out what my current to date net income is but can't figure out how to break this down to a weekly avarage. My basic guess is that I want to take the figure provided and divide it by the number of weeks from "stated start date" to the current date (but on a divided by 7 basis?) to get my average weekly net income.

I am sure this is pretty simple for you all so I hope I am not wasting time it's just that if I try figure it out I am using something like WEEKNUM & that will fail after the next new year. -Although it would be better to base it all around the financial year if that can be done? (March 1st - April 31st in NZ)

## Formula/Function For Commission Calculation

Jul 26, 2006

I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..

\$0 - \$10,000 - 60% commission
\$10,001 - \$15,000 - 65% commission
\$15,001+ - 70% commission..

i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is \$12,000, i don't know how to have it calculate the first \$10,000 at 60% and the last \$2,000 at 65%. any help is greatly appreciated..

ps.. my excel sheet is set up as follows:
Rows a-g (stuff that is irrelivant)
row h, gross fees
row i, commission (in dollars)

## Lookup Formula: Commission Calculation To Be Done Automatically Once Data Is Inputted In Cell

Mar 25, 2009

I am trying to come up with a formula that will allow the commission calculation to be done automatically once data is inputted in cell A2 and E2. I have tried IF statements, but can not figure out how to make it work. I am not able to figure out how to get cells F9 and F19 to work with the proper formula.

## Sales Realization Calculation

Jan 24, 2009

I am looking for the formula for calculating Sales realization in installments i.e. if i am selling \$100 in 1st year i am receiving 50% in 1st year and balance in next 3 years same thing happens in 2 year.

## Sales Summary Sheet - Consolidate All Data In Sales Order Number Wise

Jan 5, 2012

I have an excel sheet which has 70 sheets in it...All of them are numbered but not in a sequential manner (eg : 210,211,201,202,215 etc..).The numbering is nothing but the sales order number....

All the sheets have same data (i.e Column A contains Product Sold, Column B contains Sale Value)...The thing is i want to consolidate all the data in sales order number wise. i.e.

Column A Column B
201 \$200
202 \$300
203 \$450

## Determining Top Contributors To 50% Of Sales Based On Cumulative Percent Of Sales

Jul 2, 2009

I am trying to determine the top contributors to 50% of sales based on cumulative percent of sales (see attached file). I can determine if percent of sales is less than 50%, but I need to include the person that pushes the group of top performers over the 50% mark.

## Sales Report From Sales Data Sheet If Select Date

Feb 4, 2013

I wanted to get the data from data sheet where i have entered the data manually for sales what have done

Now I wanted is when i enter date i should get the product and price in other sheet

Example:

If I enter date in a1 cell

I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet

And c1 - c10 price (if i sold 10 item @that price)

## Pivot Table: Calculate Percentage Of X Sales To To Total Sales

Feb 20, 2008

See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.

## Using Rolling 12 Month Sales And Minimum Monthly Sales?

Oct 17, 2013

I have a sales level that I need to track...My rolling 12 months' sales must be \$85,000 and my currently monthly sales must be \$7,000. I have a sheet that tracks the \$85,000 and tells me what I need to achieve that, but I haven't figured out how to include the \$7,000 monthly minimum....

The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the \$85,000 rolling 12, but I actually need to hit \$4821.79 to meet the \$7k minimum.

Actual Rolling 12 Goal
Sep 2012 5,367.24 73,663.30
Oct 2012 5,649.93 69,496.28
Nov 2012 14,163.38 73,451.30 [code]....

## Summarize Monthly Sales From Daily Sales

Jan 5, 2010

I have daily basis monthly sales. Now I want to summarize into monthly gross. Pls look attached file. I am looking for a formula to summarize January daily sales from date 1st to 31 st as of just January and and sum of each day gross.

## Formula To Calculate Sales Tax From Total Sales

Jan 4, 2005

I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and
filling in the chart on excel but I would like to create a formula that
automatically does it for me based on total sales.

## Excel 2010 :: Sales Daily Sales Data To Be Aggregated Into Monthly Data (not Using Pivot Table)

Mar 27, 2013

I cannot solve with Excel 2010 and I have searched all over for the answer.

I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.

For example, I would like to turn this:

3/2/2005 \$xxxx
3/5/2005 \$xxxx
3/20/2005 \$xxxx
4/2/2005 \$xxxx
4/10/2005 \$xxxx

Into this:
March 2005- \$xxxx (monthly average)
April 2005- \$xxxx (monthly average)

I have a feeling some 'countif' formula would work but I am not sure how to do this.

## Get 5 Top Sales People Names And Their Sales?

Oct 3, 2012

In cells A4 to A54, their names are displayed.
In cells I4 to I54, their total sales are displayed.

Starting in cell B59 and C59 I'd like to display their name and sales respectively.

Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.

## Copy Week Total In Weekly Sales Worksheet To Appropriate Week In Monthly Sales

Oct 14, 2009

I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).

Range I4:I28 to the monthly sales worksheet by date.

## Finding Current Month Total Sales Using Current Month To Date Sales In Formula / Macro?

Aug 20, 2013

Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.

The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.

## Commission Formula

Sep 15, 2009

I am trying to write a formula for my commissions spreadsheet, which calculates commission clawbacks based on a sliding scale. From my understanding I need a code that will calculate additions or deductions based on a range of probabilities.

For example, if I have a percentage figure that is below 8%, I would like to add 15% to the total commission earned.

Here are the ranges below:

8% or under+15%
8-13%+ 10%
13-17%0
17-22%-10%
22% or more-15%

If I say my % is in K5 and the monetary value is in I5, what formula would I type into L5 to calculate the amendment?

## Calculating Commission

Mar 24, 2007

Sub setcommission()
Sheet3.Select

Dim cellsNum As Integer
Dim commission As Single, rating As String
Dim sales As Single
Range("b2").Select

cellsNum = ActiveCell. CurrentRegion.Row.Count

For i = 1 To cellsNum
If ActiveCell.Value > 150000 Then
commission = ActiveCell.Value * 0.012
rating = "superior"
Else sales > 100000 And sales <150000 Then
commission = ActiveCell.Value * 0.08
rating = "satisfactory"
Else

I am trying to calculate the commission for the sales for sales>150000 a commision charge of 0.012 and rating of superior. for 150000> sales >100000 a commision charge of 0.08 and rating of satisfactory. for other sales a commision charge of 0.04 and rating of unsuperior.

i am finding a few problems with the code 1st a problem with 'cellsNum = ActiveCell.CurrentRegion.Row.Count' it says complie error: invalid qualifier 2nd a problem with 'Else sales > 100000 and sales <150000 then'

## Commission Calculator

Jan 22, 2008

Attached is sample data. Target payout is the amount of the base salary a salesman can earn if all goals are reached. Commissions are payed quarterly. Listed on the left are 7 goals, each with a weight of importance toward the target payout amount. The percentage achieved can only be 100% or 0%, nowhere in the middle, although they are allowed to make up a goal later if they do not make it at first. This is where I get stumped. For example, if they don't make a goal in quarter one but make the goal in quarter two plus what they missed in one, they get two quarters worth of commission in quarter 2 for that goal. I am having trouble figuring out the easiest way to go about this.

## IF / OR Formula For Calculating Commission

May 6, 2014

I am trying to create a formula that calculates multiple commissions based on profit margin. So here is what I'm looking to. If the profit margin is between 50 and 70% than there is an additional 2% commission, if it's between 70.01-100% profit margin, than it's an additional 5% here is the equation I have=IF(OR(E2>50,E2<70),D2*2%,(IF(OR(E2>70.01,E2<100),D2*5%)))but it's still calculating at the 2% even thought it's an 86% margin.

## Multiple Commission Calculations?

Feb 12, 2014

I want to pay different commission rates for different levels of sales...

IE nothing if sales are under 250,000 per year.
5% between 250,000 and 500,000,
8 % between 500,000 and 750,000
10% between 750,000 and 1,000,000
12.5 % between 1,000,000 and 1,250,000
and 15 % over 1,250,000

The issue that im having trouble with is that if the sales guy brings in 1,500,000 in yeary sales he would be paid some at 0%, some at 5% some at 8% some at 10% , so at 12.5 and some at 15%

How do i calculate that? I have included a excel spreadsheet.

## Commission Formula Not Complete

Sep 21, 2006

Looking for a formula for a zero based commission structure. I am having trouble with the formula. I have attached a breakout of what I need and an explanation of the end goal.

## Computing Commission Formula

May 11, 2007

determining a formula to compute a sales commission.

Here is a sales scenario.

A \$25 commission will be paid on sales between \$50 to \$150.
A \$50 commission will be paid on sales between \$151 to \$300
A \$75 commission will be paid on sales between \$301 to \$600

The sales person will enter the sale amount into column B. Column C should compute the total commission for multilple sales.

Example:

Column A Column B
Sales Commission
\$50 \$150 (which is the comm. for the combined sales)
\$175
\$360

## Calculate Conditional Commission

Aug 25, 2007

The following is assumed.

I have a base salary of \$5k/monthMy PROFIT on hardware sales must equal or exceed my monthly salary in order for me to earn a commission on hardware sales. If it does, my commission is then Profit beyond monthly salary X 25%I also earn commission on software sales. Total Commission is the sum of Software commission plus commission on hardware sales.

Example: HW Profit is \$6k. Software commission is \$3k.

\$6k - \$5k = \$1k.
\$1k * 25% = \$250.
\$250 + \$3k = \$3250

However, there are times (unfortunately) when my total hardware profit does not equal or exceed my monthly salary. When that happens, I am now in a negative status regarding hardware commission. The negative status is simply salary minus hardware profit.

Total commission is then that negative number plus software commission.

Example: HW Profit is \$3k. Software commission is \$3k.

\$5k - \$3k = \$-2k.
-\$2k + \$3k = \$1k

I need a formula in one cell to account for both possibilities.

## Calculate Bracket Commission

Sep 5, 2007

I have been thinking about this for a few days and have no idea where to start.
The commission scheme pays like this:

upto \$40,000 in sales pays 30%
\$40,001 to \$80,000 pays 40%
\$80,001 + pays 50%

Also, the sales person will only earn commission once they have invoiced 1/3 of their basic salary. Example

\$60,000 must invoice \$20,000 per month, therefore commission is actually 30% of the remaining \$20,000.

I want to create a spreadsheet that allows me to enter the basic salary for individual sales persons and their individual sales figures to calculate their gross commission and also their gross basic salary if I can.

## How To Calculate Total Commission In Excel

Apr 1, 2014

How do you calculate the total commission of a particular person on a consecutive months ?

Attached is the excel file for reference : Total Commsn.xls‎