Calculate Conditional Commission
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculate Bracket Commission
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 Replies!
View Related
Nested IF Formula: Calculate The Commission For My Employees
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((C3B3)<=2,"15%") It Displays the 15% in the formatted cell. (C3B3 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?
View Replies!
View Related
Conditional Format On Calculate
I manipulated some code found here on ozgrid which works perfectly using the SelectionChange event but I need it to reapply the formatting whenever the cell values are recalculated based on the values manually entered in cells AH20,AI20,AJ20. Simply using the Calculate event delivers an error, so there is something beyond my very basic knowledge that needs to be done. I have an example worksheet on my server here.
View Replies!
View Related
Conditional Formula And Calculate Automatically
I am creating a sales per day/week worksheet for my reps to keep track of their sales. I have created the sheet that they only have to enter in what they have sold and it automatically calculates what they have sold per hour in the day, and what that makes their weekly total. The problem is sometimes they will work 5 days, sometimes 4, sometimes 6 or 7. im trying to fix the weekly average formula to divide by 7.5 for 1 day worked, 15 for 2, 22.5 for 3, etc all through 7... this is the formula I have now, obviously it doesnt work =IF(COUNT(B7:H7)=1,SUM(E42,E75,E110,E143,E175,E207,E238)/7.5,IF(COUNT(B7:H7)=2,SUM(E42,E75,E110,E143,E175,E207,E238)/15,IF(COUNT(B7:H7)=3,SUM(E42,E75,E110,E143,E175,E207,E238)/22.5,IF(COUNT(B7:H7)=4,SUM(E42,E75,E110,E143,E175,E207,E238)/30,IF(COUNT(B7:H7)=5,SUM(E42,E75,E110,E143,E175,E207,E238)/37.5,IF(COUNT(B7:H7)=6,SUM(E42,E75,E110,E143,E175,E207,E238)/45,IF(COUNT(B7:H7)=7,SUM(E42,E75,E110,E143,E175,E207,E238)/52.5)))))))
View Replies!
View Related
Conditional Formula  Calculate Only If Net Unit Is Zero
Here's the table for one game DateBuy(Unit)Sell(Unit)Net UnitPrice($)Net Profit/Loss($) xx/xx2##2125 ## xx/xx##11150 ## xx/xx##10165 +65 xx/xx##4(4)170 ## xx/xx2##(2)165 ## xx/xx##1(3)180 ## xx/xx3##0180 (10) Number in bracket means negative. ## means empty cell. How can I type a formula, so that every time there's no stock left (ie zero net unit), it will calculate the net profit once.
View Replies!
View Related
Conditional Formula: Calculate The Difference In A Date Between Two Dates
I'm trying to make a formula to calculate the difference in a date between two dates. For exaple, in my business we have a due day for something. So the due date could be Feb 10, we need to know if it was done on time, late, or early. So I've gotten it to display late by doing this: =IF(B2>A2, "Late", (A2B2)) But then I can't figure out how to incorporate this: =IF(B2=A2, "On Time") or =IF(B2<A2, "Early"). I would like to displaly this all in one cell. So if the due date is Feb 10 and it's turned in Feb 8 it's early, turned in Feb 10 it says on time, turned in Feb 11 it's late. I also tried this but it didn't work: =OR(IF(B2>A2,"Late")*IF(B2=A2,"On Time")*IF(B2<A2,"Early"))
View Replies!
View Related
Conditional Count (calculate Number Of State Holiday Days)
I m trying to do is this. For every person in company there is a table 30 days (cells) long, and 3 shifts (cells) wide. Every shift leader fills in the data in the "calendar" so every day, for every person, he will fill in number of hours. I have to make excel process the whole calendar 30 x 3 = 90 cells, into final results for our payroll department. So the end product of the excel table should be Person name, total work days, total sick leave days, holiday days, and so on. specific problem i have is how to calculate number of State Holiday days. You can see my best attempt in the attached file. if there is only one number / mark entered each day (in only one of the 3 shifts) thansumproduct works ok.
View Replies!
View Related
Commission Formula
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% 813%+ 10% 1317%0 1722%10% 22% or more15% 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 Replies!
View Related
Calculating Commission
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 Replies!
View Related
Commission Calculator
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 Replies!
View Related
Sales Commission Calculation
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 Replies!
View Related
Computing Commission Formula
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 Replies!
View Related
Rewrite Commission Spreadsheet With A New Structure
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 Replies!
View Related
Multiple Variables In A Commission Structure
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 A15%; "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 Replies!
View Related
Formula/Function For Commission Calculation
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 ag (stuff that is irrelivant) row h, gross fees row i, commission (in dollars)
View Replies!
View Related
Weekly Average Commission Calculation
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 Replies!
View Related
Real Estate Commission Schedule
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 Replies!
View Related
Sales Commission Formula Required
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 Replies!
View Related
Convoluted Sum Formula (formulas To Generate Commission Reporting Information On The Summary Tab )
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.
View Replies!
View Related
Conditional Percentage Based On Conditional Sum
I have a list containing blocks of stock price values with each block representing a series of values at a given time e.g 1400 156 1400 99 1400 74 1400 86 1400 256 The number of entries may vary for each block but is never more than 60. the next block may be e.g. 14:05 15 14:05 42 etc and down a very long list of around 65000 entries. I'm looking for a routine that will add up all the values in each block and calculate the total for that time block. I then need to fill in an adjacent column with the perecentage that each value is of the total. A completed block may look like;..............
View Replies!
View Related
Conditional Format Based On A Conditional Format
I have five fields that have a conditional format applied to them: (see attached). Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met. Ex. In cell L4, his crunches were 25 (a automatic failure  red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
View Replies!
View Related
How To Calculate Combinations ?
I want to see how much combinations are possible when i got 6 numbers..  got this numbers 123456  want to calculate how much combinations of ( 2 ) numbers possible  want to calculate how much of ( 3 ) numbers possible  want to calculate how much of ( 4 ) numbers possible  want to calculate how much of ( 5) numbers possible  never 2 same numbers together (22) or (223) or (2234) may not be in list  How can i make the result visible in kind of list ? I would be nice if somebody knows a good solution..
View Replies!
View Related
Calculate Variances
Just wanted to find out if the formulas in the attached apreadsheet are correct. Formulas from E6 to E10. Also, if you multiply the "new daily target" with "working days" should'nt this give you the "remaining" figure? Currently it's not doing this
View Replies!
View Related
Calculate Totals
I have a spread sheet that is used to review calls placed by a call center. Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call. We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts? My initial thought was something like this: =SUMPRODUCT((A2:A5000=H2),(E2:E5000)) In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.
View Replies!
View Related
Calculate Ratio
I'm trying to create a quote... basically I need to find out about calculating ratios. We have 47 students going on a trip to different venues, we offer 1:10 teachers (i.e. one teacher free, for every ten students going), however some venues offer a different ratio e.g. 1:5 teachers free of charge. I need to somehow create a column where it will calculate the amount charged for the different ratios the venues offered.
View Replies!
View Related
Calculate The Variance
i have a dynamic list of numbers....currently 10 numbers in the list. how can i calculate the variance? i have the upper limit (=MIN(1,(mean+half width)) i have the lower limit (=MAX(0,meanhalf width) i have the mean (avg of all numbers) i have the t value (TINV(alpha, (n1))) i have the half width (t value * SQRT of Var/N) i just don't know how to get the VAR/N
View Replies!
View Related
Calculate If There Is Anything In The Cell
Im wanting to do a sum calculate if there is anything in the cell. Ive tried using the 'count' and 'count if' but both return values of zero. There are words in the cell, rather than numbers. I want it to return the value of cells with data in them, and exclude those with nothing ("") in them.
View Replies!
View Related
How To Calculate Age
to calculate the age from the format date of birth shown below. SQL Data S1Date of Birth2Jun 9 1947 12:00AM3Jan 1 1957 12:00AM4Jan 1 1958 12:00AM5Jan 1 1956 12:00AM6Jun 4 1951 12:00AM7Dec 10 1963 12:00AM8Jun 17 1958 12:00AM Excel tables to the web >> Excel Jeanie HTML 4
View Replies!
View Related
Calculate Hours
I am designing a simple time card. Column D=time in, Column E=time out. My formula in column F to calculate total hours is =TEXT(E2D2,"[h]hrs"). The result is not correct. Example: In at 9:30 out at 5:00 and the calculated total is 4 hours.
View Replies!
View Related
Calculate The Probabilities
How to calculate probabilities... say that I am buying pop bottles that say "1 in 6 wins" or drawing buttons from hats where 5 of them are numbered 15 and the other one is the winner. (Note that you would have to keep putting the buttons back after drawing so there is always 15 and a winner whenever you draw) The possible combininations are easy to figure out... just do 6 to the power of how ever many bottles you buy or how many times you draw a button. So obviously if you draw once, there is 6^1 possible... if you draw twice there are 6^2 (or 36) possible combinations. To figure out how many winning possibilities (win atleast once) there are it's not too hard at this level... you can just list the possibilities: First  Second 11 12 13 14 15 1W (1 winning combination) 21 22 23 24.................................
View Replies!
View Related
Calculate Overtime
I have a time sheet for my employee's that I need to calculate their overtime in 1.5x and 2x rates. Their overtime totals are done in individule columns from D33 to S33. The first 4 hours per day are charged at 1.5x and anything over that is 2x. I want to show the 1.5x in one box and the 2x in another. I do believe that I need two formulas one in each of the boxes where the final totals would go. Here's an example, in columns D37 to D41 the employee has worked 12, 14, 9, 16 and 14.5 hours. so that's 17 hours @ 1.5x and 8.5 @ 2x.
View Replies!
View Related
Calculate The Value In The NPV
I have two columns in a payment schedule (which adjusts according to certain user inputs) that I need to use in my NPV calculation. The first column is the Total Payment and the second is Inducements. Therefore each value in the NPV calc. needs to be the sum of a given period's payment and inducement (but i don't want/have a separate column which calculates the sums). The number of periods adjusts with the users input of Term. There also may be periods where there is a payment but no inducement.
View Replies!
View Related
Calculate The Percentage
I have attached a file with both a sample section of data on the first sheet and the outcome I would like on the second sheet... I would do it all manually but there are over 200,000 rows in the actual file. The macro needs to calculate the percentage of sale for each reference number within each part number and move down to the next part number and do the same until it reaches the bottom.
View Replies!
View Related
How To Calculate Time
WHen I was a beginner at Excel, I came up with the following formula to calculate the time difference between two "time" values. Since I was too lazy to add a ":" between my hour and minute, I had decided to simply enter the start (column B) and end time (column C) as a military time (e.g., 0100, 1230, 1500, 1930, 2300, 2359). The following formula would be in Column D. =IF(C7
View Replies!
View Related
How To Calculate Date
how to calculate the date for one year. If i have 24Jun08 (A1) i need to calculate 24Jun09. If i do =A1+365 it gives me an error (the little green thing in the corner of the box) is there a way to do this without the error showing up.
View Replies!
View Related
Calculate Total
I need a formula that will calculate the total of the items in column "W" when the row directly below it contains a "1" in column "V". It will need to calculate the totals from that row all the way up to the next row that contains a "1" in column V. Example: In X14 (where I will put the formula) it would total W9 through W14. However the formula still needs to be able to calculate the total if there are more or less cells to sum. So that the same formula could calculate that in X8 it will total W5 through W8.
View Replies!
View Related
