Commission Formula Not Complete
Sep 21, 2006Looking 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.
View 2 Replies
ADVERTISEMENT
Commission Formula
Sep 15, 2009I 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?
IF / OR Formula For Calculating Commission
May 6, 2014I 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.
View 4 Replies View RelatedComputing Commission Formula
May 11, 2007determining 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
Formula / Function For Commission Calculation?
Jul 26, 2006if 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)
Sales Commission Formula Required
Jan 14, 2009I 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.
Formula/Function For Commission Calculation
Jul 26, 2006I 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)
Step Based Commission Formula
Sep 7, 2006I am having trouble with the code for this stepped scale commission structure.
Net Service
$1,1400
Step Scale Comm. %
$0-500 40%
$500-750 45%
$750-1000 47%
$1000-9999.99 50%
Final Commission Paid $ ?
Nested IF Formula: Calculate The Commission For My Employees
Feb 22, 2007I am trying to write a command to calculate the commission for my employees. There commission is based on the spread between sale price and cost. For example:
If Profit is between $1.00 and $2.00 - commission = 15%
If Profit is between $2.01 and $4.00 - commission = 20%
If Profit is between $4.01 and $6.00 - commission = 25%
If Profit is > than $6.00 then - commission = 30%
I am able to calculate the first level ex: =IF((C3-B3)<=2,"15%") It Displays the 15% in the formatted cell. (C3-B3 is the profit spread). How can I include the other 3 commission levels in the formula to display the correct commission % based on profit spread?
Formula For Range To Calculate Commission Paid In Month
Feb 26, 2014I have attached a spreadsheet which shows my desired result which often works.
Range C2:C13 is my desired result...
The problem /criteria is...
For every 140 meters sold the sales person receives £140 commission.
Meters (range B2:B13) can be carried to future months.
I am looking for a formula for range C2:C13 which calulates if commission should be paid in this month or not and if so, how much.
Convoluted Sum Formula (formulas To Generate Commission Reporting Information On The Summary Tab )
Feb 27, 2009I need 2 different formulas to generate commission reporting information on the Summary tab of the attached sample Excel file. The first is highlighted in green. For these cells, I need a sum formula that reports the total commissions (column H of the "Data" worksheet) for items Ordered in the month listed in column B of the "Summary" worksheet, but not invoiced until the month listed in the column D, E & F headers of the same worksheet. Date of item order can be found in column A of the "Data" worksheet. Date of invoice can be found in column E of the "Data" worksheet.
Now, the problem that I think I am going into is the way Excel handles dates and times. All columns and data highlighted in orange on the data sheet need to be maintained without being changed, as eventually I am going to have a report setup by our operating program drop in there so that it automates the information without any additional labor by our employees who have varying levels of Excel proficiency. Unfortunately, the report from our operating program cannot simply list a date without a time. Feel free to create any column or field to the right of the orange columns in order to complete formulas based on those orange columns. I will just lock those cells when finished so that coworkers don't accidentally blow the shizel up.
The second sum formula that I need is highlighted in yellow on the "Summary" worksheet. Basically, I need a formula that sums all commissions in column H of the "Data" worksheet for those items that are cancelled AFTER invoicing. Column D of the "Data" worksheet lists the cancellation date. There are explanations for each of these on the worksheets for quick referral.
Lookup Formula: Commission Calculation To Be Done Automatically Once Data Is Inputted In Cell
Mar 25, 2009I 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.
View 4 Replies View RelatedCopy And Paste (not Reading The Totals In Row 2 To Make The Formula Complete)
Jan 21, 2009I am trying to get the formulas to work on Sheet 3, the rows formated to percentages correctly, but they are not reading the totals in row 2 to make the formula complete. I also want this to run more than one number, I would prefer a range for example value >= 1.29 so that Sheet 3 shows all items less than 1.29.
View 4 Replies View RelatedExcel 2007 :: OFFSET Formula With Defined Name Not Selecting Complete Column
Jun 2, 2014I've created a search box for a price list, but some of the values come up with #REF. After many attempts I noticed that each of the formulas I created on the price list page came up with a different result for the values highlighted in each column. The formula is the same for each column, the column numbers being the
only difference - for instance:
Column A = code - the formula is =OFFSET(Pricelist!$A$3,0,0,COUNTA(Pricelist!$A:$A)-1).
Column D = colour - the formula is the same, but reads $D$3 and $D:$D.
When I go to Name Manager and check this formula, it only selects the first 105 entries in the column. There are about 380 entries and more may be added at various stages.
I have formulas for five other columns and they do exactly the same thing, but each column stops at a different entry. I'm working in Excel 2007, but it's a 2003 document.
Calculating Commission
Mar 24, 2007Sub 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, 2008Attached 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.
View 2 Replies View RelatedMultiple Commission Calculations?
Feb 12, 2014I 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.
Creating Commission Calculation?
May 15, 2014I'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%.
Sales Commission Calculation
Jan 4, 2010I 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
Calculate Conditional Commission
Aug 25, 2007The 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, 2007I 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, 2014How do you calculate the total commission of a particular person on a consecutive months ?
Attached is the excel file for reference : Total Commsn.xls‎
Real Estate Commission Schedule
Dec 3, 2009I receive a certain percentage of my broker's commission based on what type of house sale occurs. When one of my listings sell I receive the commission in A2:A7. When I sell a house to Company A I receive the commisions from B2:B7, company B C2:C7, and company C D2:D7.
My own personal commission percentages increase based on the income schedule E2:F7. For example, once I have earned $8137, my percentages for sales all jump to Row 3.
I have set up a chart below the commission schedule for each individual sale to calculate the commission for each type of sale. Each "x" represent a sale for each category (LISTING, COMP A, COMP B, COMP C). The broker's commission is always 3.5% of the total sales price. My commission will be a certain percentage of the broker's commission based on the scale above.
Multiple Variables In A Commission Structure
Jan 20, 2006I'm trying to create a worksheet to calculate ourcommsiion structure, but can't figure out a way to attack it. We have manyvariables (5) in our commission structure based on each order.
Here's how I set it up so far:
(In Cloumns)
A= Order Amount
B= "Y" is A-15%; "N"=A
C= "Y" is B*20%; "N" is B*10%
D= "Y" is B+2%; "N" is B
E= "Y" is B+2%; "N" is B
F="Y" is B+1%; "N" is B
G= SUM(A:F)
For example, if the order is $1000, and I answer y,y,y,y,y=$212.5
How do I create the formulas so I can just put in the order amount and the
appropriate letter to get the correct commission structure?
Variable Commission Rate Calculations?
Mar 29, 2012I need a way to calculate commission for my sales reps. The commission is based off a calculated value in H39 of a sheet that I have developed.
They get 7.5% of the value when between $1 - $100,000, example: sales of $50,000 = $3,750 in commission.
If the value in H39 is > $100,000 but $250,000 but $500,000 they get 7.5% on the first $100,000 plus 5% on the amount between $100,001 and $250,000 plus 3% on the remainder between $250,001 and $500,000 plus 2% on any amount >$500,000 example: sales of $800,000 = $7,500 (7.5% on first $100,00) plus $7,500 (5% on the next $150,000) plus $7,500 (3% on the next $250,000) plus $6,000 (2% on the sales >$500,000 for a total of $28,500.
Weekly Average Commission Calculation
May 5, 2009I 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)
Rewrite Commission Spreadsheet With A New Structure
Jan 20, 2010I'm trying to rewrite our commission spreadsheet with a new structure and as my excel knowledge is limited, I'm not really getting anywhere.
I'm looking for a few lines of formula however I'll just post one at a time otherwise I'm in danger of scaring you all off!
Firstly, I am trying to work out the formula for the following:
If the value in C7 is up to £14,999 = 1.5% of the whole value is given, if it's over £15,000 = 3.5% of whole value is given. I would like the total amount of commission to show in F7.
Setting Up A Vlookup Table For Sales Commission
Mar 12, 2014I'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%.
View 6 Replies View RelatedFind The Commission Rate Per Worker Using Lookup Functions
Dec 24, 2005I have a sheet listing comission rate eg. sales less than $200, the rate is
..5%, less than 300, the rate is 1%..etc.
Then i have another table showing different sales value of different workers. How do i find the commission rate per worker using lookup functions??
Percentage Complete
Jul 15, 2009I have an issue new client is asking for me to present my reports with a percent complete. We do survey work and all our date is in Ft. The way my file is set up right now i have H5-H100 as incomplete footage, Column I5-I100 has my completed footage and to keep it simple Row 13 A-k show total amount of footage. He wants to see everyday when i submit the % complete based of footage incomplete and footage completed.
Anyone able to help me out with a formula for this? I tried messing with it a little, wasn't really able to get it down and my Excel skills definitely aren't past beginner level.
