I 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.
I 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?
I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).
Jan-09
Feb-09
Mar-09
Apr-09........
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.
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 do you calculate the total commission of a particular person on a consecutive months ?
Attached is the excel file for reference : Total Commsn.xls‎
I am trying to build a spreadsheet to calculate referral fees paid to our agents. These are sliding scale fees and I need the formula to consider both the high and low range of the project value. For example we pay a certain percentages for sales in the following ranges:
Up to $5,000,000 (I got this one to work)
$5,000,001 to $7,500,000
$7,500,001 to $10,000,000
$10,000,000+
I assume the best way to do this is with an IF command but I only know how to use it for a single value, not a range. Also, if the statement is not true, then return $0.
Part of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message.
give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount?
attached is the start of the assesment question.
You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid.
Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received:
• If students pay the course fee less than 7 days prior to the course commencing then they receive no discount.
• If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%.
• If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%.
• If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.
I have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536
I have a large data sheet with dates in column B - in column A, I'm trying to write a formula that will determine what fiscal month it should be mapped to.
I have the calendar listed on a different tab.
Fiscal Month
Start Date
End Date
January
1/1/2012
1/31/2012
[Code] .........
The formula I tried using is: =LOOKUP(B2,Calendar!$B$2:$C$25)
In cell A1 = 15/06/2006 (dd/mm/yyyy)
What is the formula to find how many no. of days in month provided in cell A1.
I have a worksheet where the current months date is entered in cell E1 (format dd/mm/yyyy) , using an input box.
I need a formula the will calculate the previous months month end date in cell A6 and the month end prior to the date calculated in A6 in cell A13.
where the dates start with 31 for eg 31/01/2008 , then my formula works, but if it starts with 30, 29 or 28, then obviously it does not work.
I would like a formula that will calculate these previous month end dates correctly.
See example below ....
Formula which can calculate how many days in a month and create a column for each day. Columns for day 28, 29, 30 or 31 also need the formulas in columns 1 to 27 copied over automatically. [sheet name "LARSheet1 to 1" ]
I'm trying to build "Leave, Attendance Management" system using Excel. I am about 40% there so far.
The basic structure of the workbook is as follows:
12x main monthly "Leave Attendance Record" worksheets (i.emonths April 2012 through to March 2013). [I will refer to these sheets asLARSheet1 to LARSheet12 ].
1 x worksheet which holds "data validation" list andvlook up info. ["FormulaListSheet"]
1 x worksheet acting as a database of staff details andpay numbers. ["StaffdBaseSheet"]
1 x summary page which shows staff reaming leaveallocation. ["SummaryLeaveSheet"]
What I'm having problems with at the moment is as follows:
A formula or a macro which works out how many days are present in a given month [starting date of month located in CELL B10].
Days 1-27 will be already set up on the sheet. Based upon the monthly value I would like a solution which can calculate how many days are in a month and create a column for days 28, 29, 30 or 31 (depending on the month).
I would like it to work like this:
When the user opens a new worksheet, they first entre the month start date in CELL B10. Hopefully this will trigger columns E13:AH22 to update with the relevant week day & date.
I also need the formulas and formatting in cells AH13:AH100 (column for day 27) to copied over automatically.
Sample of sheet setup : screenshot_LARSheet1.PNG
set a formula to auto calculate the staggered rent for the month. When I change the date, it will tell me for this month I should charge according to the rates for the year.
Rent for the month
Start Date Year 1 Year 2 Year 3 01/07/14 Explanation
01/08/13 10 20 30 10 < 1 yr = 10
01/07/13 40 50 60 50 enter 2nd yr = 50
16/07/13 70 80 90 76.29 (15/31*70)+(16/31*80)
16/07/13 10 20 30 15.16 (15/31*10)+(16/31*20)
formula or vba using Excel 2003.
I need to write a formula that will look at a number in a cell and compute an amount at 1% up to 55,000. Then anything above the 55,000 compute an amount at 3.25%. Then add the two together.
So a baseline of 55,000 at 1%. Then above the baseline 3.25%.
I am currently trying to create an excel spreadsheet where I would know how much interest I will pay in the next six months, since the balance changes every month I am not sure how to formulate the total interest paid with in a specified time period.
I currently can calculate the interest and fill in the below rows with each month but I would like to simplify the report so that I can add the Balance, Rate, Payment(2% of balance), and Time period and in another cell it will tell me how much interest I will pay in that time period. Bascially I would like to keep all the calculations in one row.
For example
Balance | Rate | Payment | Time Period | Interest Paid
$1,000 | 10.00%|$20.00 | 6 | $48.56
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?
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
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 RelatedLooking 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 Relateddetermining 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
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)
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.
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)
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 $ ?
.............................24............30............36
300014999..........9.00%.......11.00%.....12.00%
1500099999........9.50%.......11.50%.....12.50%
100000249999.....9.00%......11.00%.....13.00%
I need an all inclusive formula (all in one cell) that would look at the above table and depending upon the price paid (3000-14999 or 15000-99999 or 100000-249999) and depending upon what monthly term they choose (24, 30, or 36), the appropriate finance charge would be used to calculate a total cost (9-13%). The only way I know to do this is by using IF's and AND's, but there are simply too many arguments and I cannot properly write the formula.
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
I have a table that looks like this (its basically a historical data of a stock exchange):
Date
Index
January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
I 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.