Convoluted Sum Formula (formulas To Generate Commission Reporting Information On The Summary Tab )
Feb 27, 2009
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 have a 2 page excel book, 2003, that runs a vlookup off a list from the 2nd page of the workbook. It is a long listing of information. It returns successful info in most of the cases, but in some instances it returns #n/a in one instance where it returned the correct info in others as in:
12345 = dog 12346 = cat 12345 = #n/a
Some instances don't report the corerct info at all while others only report the correct info some of the time like above where 12345 = dog and in some cases it doesn't turn out dog as the anser to the vlookup.
I have a spreadsheet of website stats showing the number of visitors to all the domains and aliases we use for company websites. Each domain or alias has its own unique row of data. The data is in the order of most visitors. I have attached a simplified and anonymised example of the data in worksheet "stats". In real life this sheet runs to several hundred rows.
As you can see if look at the worksheet "domain key", each of our websites has more than one domain or alias pointing at it - these are reported separately by our stats package.
What I want to do is find an easily sustainable way of generating a summary report each month, such as you can see on the worksheet summary, which will give a total number of vistors for each site calculated from the visitors to the various adresses each site uses.
What I have done so far is use a very long SUMIF function, e.g. to find all visitors to the FR site the function reads:
This looks OK in the example above but in the real data we have in some cases over a dozen domains pointing to one site and its very messy and hard to maintain.
What I would prefer to do is something that would use a range of data for the criteria rather than a specified string e.g.:
=SUMIF(stats!A2:A16, domain_key!C2:C16, B2:B16)
Obviously the straight SUMIF function won't do this. The advantage to this approach is that it would make the ongoing management of which domains are counted for each country a lot simpler as I could just edit the data in the domain_key sheet rather than having update the functions.
Some issues to be aware of are: The order of data will change each month so youcan't guarantee that each address will be in the same row every monthThere isn't a pattern to the addresses that would allow you to use any kind of wildcard, e.g. you can't say all addresses containing "companyname" are the UK site and all addresses using othername are the French site. Similarly, you can't say all the french site addresses end in .fr - some countries use .com
The problem I have is I need to generate individual requests from a summary (which I can do as long as the max values do not fluctuate)
e.g. Number required each week Tool wk1 wk2 wk3 wk4
flogger 1 4 2 5 wrench 2 3 1 5 socket 6 10 2 8
so for the flogger I would need to write 8 tool requests in total: 1 for wk's 1-4, 2 for wk's 2-4, 2 for wk2 only, and 3 for week 4 only. There must be a quick way of doing this using VBA.
I have run into a problem trying to capture some summary information. Here is a brief description:
I have the following fields: Game ID Team ID Player ID Goals
I want to know, for each Game ID, how many goals were scored by the players within each team ID. A pivot table gets me close but I need this information in the following format: Game ID | Team ID | Goals.
I am guessing there is some sort of sumif function that can get me close but I am struggling with the correct calculation. Here is a data set of 1 game (keep in mind there are 700 games, otherwise I would do this manually)
Users copy and paste source data from a report into worksheet 1 each month. Data from last month is deleted and data for the current month copied into worksheet 1.
I am trying to write a formula within worksheet 1 to check that data for the current reporting period only is in worksheet 1. For example all data from last month's reporting period has been removed and the only data in worksheet 1 is the current reporting period.
Reporting period is shown in two columns Year and Period number (1 to 12).
I have a worksheet that contains 26 tabs all of which have the same format but contain different data based on that pay period. i would like to create a summary tab which will allow me to enter the pay period at the top (1,2,3 ect) and have excel reference that tabs information into the summary. Is this possible?
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 set up a datasheet with information to be used in generating several pivot tables:
Column A - Our Invoice Number Comunn B - Vendor's Invoice Date Column C - Type of Vendor (Labor, PM, Subcontractor, Material, or Equipment) Column D - Vendor Name Column E - Vendor Invoice Number/Time Classification (Column C is Labor, PM, this is either Regular Time or Overtime. If Column C is Subcontractor, Material, or Equipment, the Vendor's Invoice Number is enetered.) Column F - Labor Hours Worked (Column C is Labor or PM, a value is entered, if not, formula enters "N/A") Column G - Labor Rate ((Column C is Labor or PM, vlookup value is entered, if not, formula enters "N/A") Column H - Amount Billed ((Column C is Labor or PM, formula multiplies rate by hours, if not, enter the amount of the Vendor's invoice.) Column I - Mark Up Percentage Column J - Mark Up Amount Column K - Total Amount Billed (Column H + Column J)
I need to set up a daily job sheet (like and invoice) for each date listed under Column B. For each day, I need to generate a daily job sheet showing all of that day's information. The location of the information is based on the value in Column C - Subcontractors's data go in one spot on the sheet and Labor costs go in another place.
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 am currently working through a macro and got stuck about halfway. I have a number of files in a folder on my drive that I am pulling the first tab from into a Master workbook, and then I want to have a summary tab for all of those tabs(they are all identical). Some of the cells will be text(say range A5:C105), some will be SUM(E6:G105) and some will be AVERAGE(D6:D104) formulas needed. These formulas will not change, but will need to pull the data from all tabs that are pulled into the file.
So far I have this code that pulls all of the first tabs together:
Code:
Sub Staff_Plan_Update() Dim wbDst As Workbook Dim wbSrc As Workbook Dim wsSrc As Worksheet Dim MyPath As String Dim strFilename As String
Call TimeStamp
[Code] ......
I was going to record a macro that creates a summary table every time, but not sure if it is easier to create a blank template for the summary tab that will update every time all of the tabs are pulled into this file. The problem I ran across with that is that I will be taking the SUM of all tabs, but the number of tabs/name of tabs will be different.
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 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 am attempting to create a macro to generate emails based on data in a sheet. The goal is to run the Macro, and have it generate emails to send to contractors letting them know what they are going to be paid. For instance:
Name in Column J Email in Column L Memo in Column N Balance in Column T Due Date in Column P Week Ending Date in Column H
Now what I would like to happen, is to tie a macro into a button that will create the email as follows:
To Field: Email address from Column L Subject: "Company Payment Remittance Payment Date *Date from Column P*" Body: Hello *Name from Column J*, For *WE Date in Column H* you will be paid *Balance from Column T* for the time worked of *Memo in Column N*
Now the tricky part is that I want the email to contain all line items for each email address. So instead of sending one email per line, have the macro automatically put all of the information that needs to be sent to one email address into the message. I don't know if that is possible, but it sure would make my life easier if it was.
I have attached a sample workbook of the data that will be used
I want to extend a formula like so- =Sheet2!M3 =Sheet2!M60 =Sheet2!M117
Basically I want it to go up in increments of 57 when I copy the formula down. Is there an easier way to do this rather than typing it over and over again? I looked on an older post and saw some information about OFFSET and INDEX but couldn't figure out exactly how that worked.
I am trying to create a summary sheet from the matrix to do further analysis. I want to pick out the welds done everyday with weld inches as you will see in the summary sheet. How can summary sheet be automatically updated when I enter the inspection date rather than copying and pasting? I can use vlookup to get the weld dia once I get the weld numbers on that date. I have attached the file.
1. In whatever cell is selected when the macro is run, enter a new row.
2. Copy the information from the row directly above the new row and paste (values, formulas, formats, etc) into the new row.
3. Return to column P in the new row, i.e if the new row is row 11, then return to P11, for row 12 return to P12, etc.
I have tried recording the macro but because it is hard coded to specific rows, its not working. I have attached a sample copy of the sheet (had to zip due to the size of the file).
excel 2010. This workbook has 4 worksheet(Process Engineer,OSBL,OSA,Lab Operator) I want to know what is the best excel formula/function to summary this 4 worksheet.
Example:I want a formula/function to summary all the statement from 4 worksheets and total number of answer "1" per statement from 4 worksheet.
Sample Statement below
"Demonstrate Interpersonal (People-to-People-) Skills" Question:What is the formula if above statement contains this statement in 4 worksheet?As i checked the total is 4 then What is the formula to get all total answered ICC on this statement from 4 worksheet?
I have been trying to create a report that involves three conditions, but so far I have had no luck using SUM and IF conditions to do this.
I have attached a file with an example of what I would need. Basically, I would need the "Resolved" and "In-Progress" quantities filled in below the "Country Report" for each respective country.
we have salespeople in all 50 states and each state uses different rules for business days. Some states include Saturday as a business day, some don't, and Alaska uses 5 business days including Saturdays. We are open 7 days a week.
What I am trying to create is a worksheet that has each day of the month going down and in the cells next to it, a column for states that include Saturdays as business days; so a date 3 business days out would appear in the next cell. In the cell after, a date 3 business days out not including Saturdays. And in the last cell for Alaska, a date 5 business days out including Saturday. Of course federal holidays are all excluded.
Something like this:
March w/ Sat w/o Sat Alaska 1 5 5 7 2 5 5 7 3 6 6 8 4 7 7 10 5 8 10 11
Now Vermont has extra holidays certain months so I would have to adjust that in the formula for the middle column during those months.
So I've got a workbook with three main sheets: Pipe, Fittings, Report. In the pipe sheet I've got 8 charts that are all the same but they're for pricing different types of pipe. I want to assign each line in all charts a category and then in the reports tab have a chart that will add all the prices from all charts in each category. I've tried using the VLOOKUP function but I can't seem to get it to work. I can attach the spreadsheet here if that would make things easier.
