Rewrite Commission Spreadsheet With A New Structure
Jan 20, 2010
I'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.
View 9 Replies
ADVERTISEMENT
Jan 20, 2006
I'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?
View 9 Replies
View Related
Aug 11, 2006
I previously had a query resolved in this forum when I was trying to write data to multiple sheets but the requirements have now changed and I need to update the script.
I have a multi-sheet workbook that calls a routine to rewrite sheet4 based on values in sheet1 each time sheet4 becomes the active sheet. This script makes single line entries or double line entries in sheet4 depending on whether data appears in one or both of a two specific data cells (col:J,K) in sheet1. However I now need to manually input extra data items into blank cells (col:B,C,J) in sheet4 and retain those data items after input, but each time I make sheet4 the active one it clears the contents of all the cells including the data that I have entered into cols B,C,J. Is there a way of fixing cols B,C,J on sheet4 so that they are not cleared when sheet4 is activated. Or is there a way of writing just the the additional lines added to sheet1 since the sheet4 was previously activated rather than clearing all the data lines written previously into sheet4 and rewriting them all from scratch each time the sheet4 becomes the active one.
I think you will need to see the workbook that I am working on to see how the sheets link and a zipped blank version is attached.
View 8 Replies
View Related
Aug 25, 2009
I need this code below to work in a macro on a hidden sheet.. FYI the sheet name is "Closed".
View 2 Replies
View Related
Aug 5, 2007
The following block of code is evaluating the term in the array, if it exists and the value in col. M is a non integer, (which what I am testing for are fractions) then format the cell to a fraction number format.
That is ok, except it is changing the numberformat on text terms. I want it only to change the numberformat on numeric values.
I added the function Isnumeric to this line:
Isnumeric(cells(i, "M").value) Int(cells(i, "M").value)
which I thought would only evaluate numeric cells only, but this was unsuccessful.
Original Code: Unedited.
For i = 4 To LRowf
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTG", "BOOTY", "HWRISR", "HWBLTS")
On Error Resume Next
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value Int(Cells(i, "M").Value) Then
Cells(i, "M").NumberFormat = "# ?/?"
On Error GoTo 0
Exit For
'End If
End If
Next Item
Next i
View 9 Replies
View Related
Feb 5, 2010
i want to browse a text file with 10 textual lines and rewrite the contents in the same file (starting from line number 11) from bottom to top.
View 9 Replies
View Related
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?
View 14 Replies
View Related
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'
View 7 Replies
View Related
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.
View 2 Replies
View Related
Oct 31, 2009
I know the Dictionary Object is confined to the Microsoft Word Object structure though can it be used in Excel. If not, is there a suitable replacement.
View 3 Replies
View Related
Nov 18, 2009
I want to find the folder structure, but walking down. I have try this code
View 2 Replies
View Related
Jun 18, 2009
I need to present dollar values to my boss for a proposed tiered bonus structure and am trying to figure out how to create the necessary formula.
The bonus structure is as follows:
Level 1$8,500 = 3% of fees up to $8,500
Level 2$10,500 = 5% of fees between $8,501 and $10,500
Level 3$15,000 = 7% of fees between $10,500 and $15,000
Tricky part is bonus amounts do not go back to dollar zero. The percentages are only for the stated range.
EX: Level 1 = $255, Level 2 = 99.95, Level 3 = 314.93
Therefore collecting $15,000 would net $669.88
View 3 Replies
View Related
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.
View 4 Replies
View Related
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.
View 6 Replies
View Related
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%.
View 7 Replies
View Related
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
View 9 Replies
View Related
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.
View 2 Replies
View Related
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
View 6 Replies
View Related
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.
View 9 Replies
View Related
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.
View 3 Replies
View Related
Dec 3, 2012
I have set a password under the permissions tab but all it does is protect the structure and not the content. I want all users to be able to view the file, but have to have the password to edit any of the sheets or data on the sheets. I can still protect the individual sheets, but that doesnt quite do what I want.
View 1 Replies
View Related
Mar 6, 2014
I am trying to create a sheet to resemble tree structure of rows. That is by default all the parent rows must be visible and click of + sign, the row should expand to show its child rows which in turn can have child rows but appear collapsed. When I choose two rows and click on Group and select rows, I see a grouping but when I try to collapse using - sign, both 2 rows gets disabled. I want to make the first row as parent and second row as child. How do I do it?
View 1 Replies
View Related
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)
View 9 Replies
View Related
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‎
View 1 Replies
View Related
Dec 3, 2009
I 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.
View 3 Replies
View Related
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.
View 4 Replies
View Related
Mar 29, 2012
I 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.
View 5 Replies
View Related
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)
View 10 Replies
View Related
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)
View 9 Replies
View Related
Sep 7, 2006
I 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 $ ?
View 7 Replies
View Related
