Nested IF Formula: Calculate The Commission For My Employees
Feb 22, 2007
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 representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
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.
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.
I am a part of an construction company, I am looking to calculate Rig Pay, Truck pay and Perdiem for each employee based on an Excel Spreadsheet pulled from the Time Card Lines out of our accounting software. I am struggling terribly, and need assistance. The Calculations are as follows
Every Employee has multiple lines in the Time Cards sheet. Rig Pay, Truck Pay, and Perdiem come from the Employee file in our system
If employee id is the same, job number is the same, cost code is the same, calc rig pay * hrs If employee id is the same, job number is the same, cost code is the same, calc truck pay * hrs If employee id is the same, job number is the same,Data, and cost code is the same, calc Perdiem /day
The final result i should see how much each employee charges rig pay, truck pay, and perdiem per job, and cost code.
In a sheet, like the example, I have monthly shift schedules for employees. What I want is to count the total shifts for Saturday and Sunday, separately Night (N) and Morning (M)/Afternoon (A) shifts, as in columns with red.
ABCDEMNOPQRAGAHAIAJ1JUNE 20132DATE1234121314151617.... HOW MANY "N" HOW MANY "M" AND "A"3SATSUNMONTUEWEDTHUFRISATSUNMON.... AT SAT-SUNAT SAT-SUN4Employee1NNNNNMM--A205Employee2MMMM-NNNNN246 Employee3AAAAM--MMM067Employee4--MM-AANNM408............................................9EXAMPLE
I have to calculate employees work hours for overtime.
in the timesheets:
A B C D E 1 ID Date Code Hours PayMethod 2 A123 4/14/14 TRN 20:00 Regular 3 A123 4/14/14 TTT 15:00 Regular 4 A123 4/14/14 TRN 13:00 <----- total for cell D2,D3, and D4 is more than 40
A B C D E 1 ID Date Code Hours PayMethod 2 A123 4/14/14 TRN 20:00 Regular 3 A123 4/14/14 TTT 15:00 Regular 4 A123 4/14/14 TRN 5:00 Regular <----- to make 40 5 A123 4/14/14 TRT 8:00 Overtime <----- 13-5=8, so i have to write down 8 here for overtime
How can I do this?
I want to make a command button for macro to perform this.
I need to calculate the employees incentives based on target achievement in different scales. In attachment sheet i filled the output manually but i need the actual formula .
Im having a hard time creating the formula to calculate the min number of employees to complete a number of jobs that come in each day and need to be finished in 18 days or less.
Data that I have:
Forecast of the number of jobs for 251 days ( jobs range from 10 -80 per day) Each job needs to be finished in 18 days or less we need to staff a fixed number of employees for the whole year each employee can complete 4 jobs per day no weekends
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 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.
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.
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
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..
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..
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 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.
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.
I'm writing a spreadsheet where I need to calculate a nested IF function (I think), and I am unsure of how to do it.
The problem is this: The total $ value of the quote is entered, then depending on two variables, a rebate may be deducted from this total value. The first variable is eligibility (a Y/N response), the second is the type of project - there are two types of project and each attract a different rebate amount.
So I have:
A: total quote $ B: Eligibility - 'Y' or 'N' (if 'Y', then value of 'C' is deducted from 'A'), (if 'N', then no value is deducted from 'A') C: Type - '1' or '2' (if '1', then 'C' = $1000), (if '2', then 'C' = $1600) D: final result
I had tried ranking formula to rank employees on some cirteria it was working, but if there is any tie between two employees then the next rank is not displaying for example in Ach Rank : rank 5 is missing because there is tie is between two employees is rank 4. Request your suggestion and help on this for overcoming the issue.
Formula used for ranking : =IF(B2>=75%,RANK(B2,$B$2:$B$41,1)-COUNTIF($B$2:$B$41,"
The range B7:K7 contains columns of "dates" and "characters" like "A", "B" and "C" . Range D2 = Today's date. If Range C7 is blank then it should calculate Today's date minus Column B7 (i.e D2-B7) so on:
B7 = "01-Apr-2009", C7 = "02-Apr-2009", D=7 = Status as "(A)" B7 = Submitted Date, C7 = Received Date, D7 = Status Difference Between Today's Date and "B7" date is required if "C7" is Blank date. Otherwise it should search next group (E, F)
I am putting together a procedure to calculate a value based on all of the active cells in column "A" and a couple other conditions. "k" is my variable to put a value into (k,8) until all the active cells in column A have a corresponding value in (k,8)
I have my scenarios all built out into If statements and the code runs....but no value is being added to cell (k,8) for any of the rows with entries in column "A" when the procedure is finished running. Depending on the values in (k,2) and (k,3) there are four different ways the value in (k,8) needs to calculate as I have built out below. Why my values aren't being added to (k,8)?
Code: Sub CalcSpecialFee() Dim k As Integer Dim cell As Range k = 4 Application.ScreenUpdating = False Sheets("Summary").Select
I have a sheet in which in Coloum A I have manager names, then in Colum B I have list of Employees reporting to that manager, then in Column C, I have total login time of each employee which should be => 8 hours. In a new sheet, I want to get the count of employees next to the each manager's name who login time is less then 8 hours.
I'm trying to hone a quoting tool to generate an amount based on a given range and minimum charges for specified subsects of the range. If you reference the attachment I'm trying to calculate a charge in cell K15 based on values in B9 and C10 referencing the table I8:R9. What I cant figure out is how to create a minimum charge of 50 for J8:P9 (points A-G) and a charge of 95 for Q8:R9 (points H-I).
I have a rather large formula that I put together and it's shown in column 'C' in the small sample file I have attached. I'd like to add one more criteria such that I avoid duplicate solutions in column 'C'. i.e. I only want the first occurrence of a group.
The formula works fine 'bucketing' the first entry. If I have an solution in C I want the formula to not give me another answer if the cell immediately above has an answer. In other words, rows 11 and 15 would be blank because rows 10 and 14 have an answer.
I've tried inserting another criteria in the AND statement, like ISBLANK for the cell above, but I can't get it to work. What am I doing wrong, OR is there a simpler solution?
