How to streamline calculations that include multiple variables.
I am trying to calculate results against weighted objectives for multiple associates. Here is a very trimmed down version of the table I have currently:
Associate
Role
Function 1 Count
Function 1 Time
Function 1 Learning Curve
Function 2 Count
Function 2 Time
Function 2 Learning Curve
Function 3 Count
Function 3 Time
Function 3 Learning Curve
[Code] .....
There is an acceptable range (floor & ceiling) of performance for each Role/Function/LearningCurve. In the example above, let's say those ranges are as follows:
I currently run a calculation for that associate determining what % of total time was spent on each function. I multiply that by the floor & ceiling of each range, summing the floors & ceilings to produce a weighted range. Then, I compare their production total to that range. I have all this figured out. What I'm running into, though, is that this table is duplicated on a separate worksheet for each month of the year. Beyond that, each member of management maintains their unique workbook listing this information for their associates only. What I would like to do is publish/maintain only one "master" workbook for all associates, regardless of manager.
I tried creating a master table that inserted columns for Month & Manager in front of the columns above. I tried to use a PivotTable (brand new to that process), but I think that because I have certain variables (i.e., Role & LearningCurve) that are "stuck in the midst" of my data, I can't make heads or tails of the PT.
Currently, I have 3 columns per function (Count, Time & LC) as only one LC would exist in a given month for a given function. I figure I can set up the master sheet to have 8 columns per function (account for Count & Time per each of the 4 LC's possible), but that begins to become more cumbersome than what I have already.
In the end, I want to have a table that I can filter by month and/or manager and/or associate to which I can then apply the appropriate calculations. This would allow calculation for a single month or range of months as well as being able to calculate results for an associate regardless of potential reassignment to a different manager, and conversely being able to aggregate results for a given manager using data for whichever associates reported to them each month.
I'm using one spreadsheet "Take Off", to pull data from a cell which dictates an objects shape, based on this shape I have a very large IF statement that pulls data from an adjacent cell where an objects dimensions are entered, the statement than proceeds to use these dimensions to calculate the weight of the object.
I've run into the problem with only 7 IF statements, I have gone online and tried to figure out how to avoid this since it is a common problem, but I can't figure out how to do it, most of the examples show very simple and basic formulas. I am unable to figure out how to translate this into my IF statement where I make reference to many cells as well as us differant formulas within the IF statement.
My latest solution was to breakdown the IF statements and try to my my own functions but I think I am too green to have done this properly. Any guidance would be greatly appreciated.
The following is the code I'm working with followed by how I tried to remedy the situation:
and so on until hCalc, but when I put them together in a cell like this: =aCalc()+bCalc()+cCalc()+dCalc()+eCalc().... I get an error #REF.
I realize a problem might be that I'm referencing a specific cell within my formulas, and many examples online show cells referenced in the form =A$1$, is there a differant way for referencing a cell, because currently I rely on Excel to automatically do this by way of relative reference.
ADDED: Ok I scrapped making the functions because I'm not sure and I did some research on VBA so I tried this one:
Function Weight(Shape As String, Amount As Double, Dimension As String, Optional Length As Variant) If (Shape = "BPLT" Or Shape = "L") Then If (Amount > 5) Then Weight = Get_Word(Dimension, "First") / 1000 + Get_Word(Dimension, 3) / 1000 + Get_Word(Dimension, 5) / 1000 * Get_Word(Dimension, "Last") * 7850 Else Weight = Get_Word(Dimension, "First") / 1000 + Get_Word(Dimension, 3) / 1000 * Get_Word(Dimension, "Last") * 7850 End If
But when I try to put Weight(C6,D6,F6,E6) I run into the error "Sub or Function Not Defined"
What I do at my job is schedule production runs for bakery items. these items consist of many ingredients that go into them (Flour, Sugar, etc.) The products go into the run at certain percentages (20% of the run is flour, 30% is eggs, 15% is Sugar, etc) Many times we will have two or more types of a product, sugar for example. between the different types they can all have a different cost per pound, so what we try to do is use the cheapest stuff first to save money. So for part of this excel sheet what I would like it to do is to automate what percentage I should use the sugar based on cost. the total percentage for the sugar would add up to 100%.
for the second half of this sheet, there is another variable to account for. for this product we also need to hit target specifications so the product will be ok to sell. The example is Protein in this situation. we have a target protein that we need to hit per serving of 24.2 grams. I have the file set up to convert the protein percentage to protein per serving in grams, so that is already done.
my problem with this sheet is I can only seem to do one or the other. I can set it up to use the cheapest product first and add up to 100%, but it won't hit the target protein. Or I can have it set up to hit the target protein, but then I won't hit the 100%, it's be above or below. I need it to account for both variables and then give me the suggested percentages.
So is there a way that I can have it formulate what percentage to use something at to hit a target and equal 100%?
tl;dr I need to account for two variables. hit target protein, and have the total percentage equal 100%
I'm trying to automate a process where I get a list of checks and money orders purchased then manipulate it to make auditors happy. I need to eliminate all transactions less than $3000. The fly in the ointment, however, is NOT to eliminate daily transactions by the same purchaser that may be less than $3000, but when added together are greater than $3000.
I've created a helper column where I've inserted the formula ....
I am trying to create a s/sheet whereby one can calculate how the concentration of an isotope changes (via an exponential decay) with respect to 2 variables:
1. its half-life (arbitrarily shown as 3 'units' in cell E2), and 2. its initial concentration or dose (arbitrarily shown as 100 units) AND subsequent additional exposures (arbitrarily shown as exposure to 50 units every 3 days) (shown in column E).
Thus for example, with an initial dose of 100 units (cell E6), after 3 days, the substance would have decayed by one half-life to 50 units. However, because there was an ADDITIONAL exposure of 50 concentration units on day 3, the EFFECTIVE dose on day 3 = 100 units.
I have a spreadsheet that I am trying to have automatically calculate a total based on certain criteria:
I want the amount under the per diem amount column W to return a value based on if P is entered in column V. If this is for partial it will depend on departure and arrival times. If departure is after 6:00 AM no breakfast per diem is paid, if departure is after 11:00 AM no lunch is paid. If arrival is before 2:00 PM no lunch. If arrival is before 7:00 PM no dinner. If the user enters a 1 or 2 in column U, 1 uses out of state per diem breakdown located in cells I38:I40. In state uses a 2 and is located in cells E38:E40.
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.
I am creating a userform that has multiple calculations in it. I understand how to do this in Excel but I have no idea what the order of operations would be with a user form. (UoM Cost) will = Unit Price / Quantity). The (Ext Cost) field would equal [Quantity x Waste% x UoM Cost (that needs to be calculated before) + Unit Price. (Cost Per Each) would = the "Ext Cost"....that needs to be calculated before / Quantity - Waste%.
Once I have these calculations, then need to click a button to either reset the user for for another record (or if I mess up) and a button to save the record to the excel spreadsheet in the background.
I want to be able to sum across a worksheet the products in individual rows of a $ rate and a qty, without doing this for each column and then adding them.
for example: I might have in B1 '$100' and in B2 '3', then in C1 '$200' and C2 '6' and so on. What formula can I put in A3 to sum B1*B2 plus C1*C2 and so on?
I have a start time and a stop time, then a difference is calculated in cell L53. I want to subtract the time value of a break but it only works if the entered value is greater than 1:00. I tried K2-I2-L55 and It works but It wont work for times less than 1:00. I need to be able to subtract :15, :30 or any other value in cell L53.
I am trying to track the time the various activities take. I need this in hours:minutes - Monday through Friday. Attached is the spreadsheet that does NOT work. An "x" in the column creates the timestamp in the adjacent column. Some of my formulas are not correct as the calculations do not create the correct data.
I'm working on this excel tool that I would like to make as user-friendly as possible. It would act like a survey almost where the user would choose responses or input values for specific questions and then on other worksheets it would have the actual calculation and math.
I see that if one were to add a value onto worksheet 1 and which would be a variable that is essential to a calculation on worksheet 2 it does not run automatically.
If there is a combo box w text values that also needs to have a calculation on another page, can i just do an IF statement on worksheet 2 with combo boxes?
I have one spreadsheet that contains employee overtime info per pay period which is bi-weekly, another spread that has their monthly production numbers. So...
Employee OT PayEndDate John Smith 5 01/13/12 John Smith 7 01/27/12 Jack Jones 8 01/13/12 Jack Jones 9 01/27/12 John Smith 6 02/10/12 John Smith 3 02/24/12 Jack Jones 8 02/10/12 Jack Jones 10 02/24/12 And so on
On the other sheet Employee Production Month Jonh Smith 53 Jan Jack Jones 75 Jan John Smith 45 Feb Jack Jones 80 Feb And so on,
What I need to do is see the overtime hours per month, and the production numbers for the same month. Something like...
Employee OT Production Month Jonh Smith 12 53 Jan Jack Jones 17 75 Jan John Smith 9 45 Feb Jack Jones 18 80 Feb
I have a pivot table the sums up the OT by PayEnd Date but need to change PayEndDate to month, which I know how to do, but then add in the production any thoughts on how I could do this?
Is there any way to combine all the following calculations? Using the first as an example - If the word Resigned does not appear in P28 and if S28 = Q then the sum is 6 - 6*O28
having a different calculations performed within a Textbox on a Userform depending on the selection made in a Combo Box. This was answered here.
Perform Calculation In Textbox On Userform
I have another question regarding this problem, however the thread is closed so I have posted a new thread.
The original question was answered and the solution works very well,however I now need to be able to use the selection from two (2) comboboxes to initiate the various calculations in a similar vain.
have a look at the original thread to get an idea of what I am looking for here.
I have tried many configurations of various code, all of which works to an extent buts which falls over in certain circumstances.
I realised that the screenshot attachment makes much more sense than trying to show within post.
EXPENSE MONTHLY ANNUALLY MONTHLY COST ANNUAL COST
BILL £4.00 £4.00 £48.00 BILL £120.00 £10.00 £120.00 BILL £260.00 £21.67 £260.00 BILL £12.00 £12.00 £144.00 BILL £19.00 £19.00 £228.00 BILL £14.63 £14.63 £175.56 BILL £550.00 £45.83 £550.00 BILL £94.00 £94.00 £1,128.00
For my bills I want to work out first the monthly cost e.g. monthly(B) figure OR annual (C) /12 - and give the result in column (D) And then work out the annual cost e.g. monthly (B) *12 OR annual figure - and give the result in column (E)
So if there is no figure in the monthly column (B), the calculation will be dividing the annual column figure by 12 and vice versa, if there is no figure in the annual column (C) then the calculation will be multiplying the figure by 12
This is probably a very simple calculation to do, but I am struggling to make sense of calculating results from different columns to give a result in the same cell.
I have a need to populate a summary worksheet using two variables to find data in two or more other worksheets.
I find writing out what I want helps some times so let me try it here.
So my variables are:
Product (there are 22 products) Supply Less than (inset number)
These are the two criteria I want to use to produce a result.
The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand.
What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below.
The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.
I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.
I'm looking to find a formula to calculate the red cell (actually all the cells in the "type" fields) by determining:
1) If the Product in the table matches the one in row 3 2) If the dates in table columns H, J, L, N and P took place in the month before the billing date 3) If those two conditions are met, sum the matching columns in I, K, M, O and Q.
For example for the red cell would currently equal $11,380.02 as the only revenue collection dates in range would be cells I1 through I3.
Can I put two variables into a SumIF? forexample I want to sum Column C if Column A is equal to Apples and Column B is equal to Oranges, then sum Column C. Is there a quick formula?
I am looking for a syntax to ask a question with three variables
As an example If Blade size = 114 (only 3 blade size choices) and the Panel Size is between 500 and 700 ( This will also be dependant on the blade size) and the tilt bar is clearview (Three choices) then the result will be x = 182
The answers will depend on the above variables: 154, 159, 179, 177, 182 and 202
I need to count up the scores from a questionnaire I have developed, which I am having trouble coming up with a formula for.
I have 5 different types of sales channels in column A. In columns C, E and G there is the call method they use (inbound, outbound or both) with a 'Y' / 'N' option next to it. Basically, I need a formula that if a sales channel has a Y next to Inbound, then the value 'Inbound' to be returned to the destination sale and if it has a Y next to Outbound then the value 'Outbound' returned to the destination cell and so on.
I have attached a spreadsheet to make my problem easier to understand.
I have in cell c40 a data validation list with source equal Royal (=Royal) has been created.
Royal is a namebox that has a defined list of 7 options (6 actual options plus one that says "select via drop down") :
FA4 = Select via Drop Down FA5 = option 1 FA6 = option 2 FA7 = option 3 FA8 = option 4 FA9 = option 5 FA10 = option 6..............
The concept is that FA5 is associated with a value defined in FB5, FA6 is associated with FB6, so on
What I want to happen is when I select a value via the drop down selection in C40 eg. "Option 1" that in cell D40 the value associated with Option 1 (found in FA5) returns the value found in FB5 ($10).
How do I do this? I have tried a number of "if" statements but no luck.
I'm trying to count a range but I have multiple criteria which I would like to use, I have attempted to use countif/sumproduct with no avail. The data is spread over two columns, one contains the rank of various employees and the other is the number of hours they have worked. I am trying to count how many employees fall in to set hour ranges.
