Trying To Create Calculation Based On Particular Criteria
Jan 29, 2014
I have 2 worksheets with data. One of them is a bench mark template or expected times to complete devices based on batch size and product. The other worksheet is a report generated that shows the actual time techs worked on batches of equipment by product type. I am trying to automate a report that I will need to run every week. I have most of it automated but need some calculations. I want to calculated the expected time to complete the work, based on the data provided in the benchmark worksheet.
I do not want to use an average, and I want to use the expected time of completion based on batch size and product relative to the tech report. Column T is highlighted and where I want this formula to be entered. So if the Total Batch items is 1, then the expected calculation uses that time relative to the product and then multiplies that number to the batch size. Or if the batch size is 3, then it uses the time relative to a batch size of 3 and then multiplies that number by the batch size on the tech report and is entered in the highlighted column.
I have attached the workbook I am working with : Production Report.xlsx‎
View 9 Replies
ADVERTISEMENT
Jul 25, 2006
I'm trying to calculate a price for any given mileage. The pricing strategy is as follows:
For any journey 2 miles or less, the price is £9
For any journey between 2 or 3 miles, the price is £10
For any journey above 3 miles, the price is £10 for the first 3 miles plus £2 for every additional mile.
So, for example, the price of a 1.5 mile journey is £9 and the price of a 3.5 mile journey is £11.
View 6 Replies
View Related
Feb 28, 2013
I have over 1000 of rows of data and I want a formula or a Macro to create 5 new records (rows) based on the following:
Below is how data in master sheet1 looks like...
STORE#ZIPCODEID #1ID #2ID #3ID #4ID #5
10134567A100B200C300D400E500
10245678A200B300C400D500E600
...
...
With the formula or a macro I want it to look like this in Sheet2 in the same file...
STORE#ZIPCODEID #
10134567A100
10134567B200
10134567C300
10134567D400
10134567E500
10245678A200
10245678B300
10245678C400
10245678D500
10245678E600
. . .
. . .
. . .
As you can see there are 5 records per STORE# listed in Master Sheet1.
View 3 Replies
View Related
Sep 21, 2012
Col A has list of order numbers, col D has list of corrisponding order status (open , closed etc etc)
I simply want a formula to display a list of all the 'Open' order numbers in a separate sheet.
Obviously to size of the list will vary depending on how many open orders there are.
View 1 Replies
View Related
Apr 13, 2014
I am trying to formulate a commission form which looks up the name of the sales person and then displays a list of the deal names withing Q1.
View 1 Replies
View Related
Aug 31, 2007
Our reservation system exports a spreasheet daily which contains hundreds of records relating to bookings. The worksheet has an ID column which is not unique, but is sorted alphabetically. I need to write some code to delete all records below the first 'set' of records relating to the first ID value found. i.e. i want to end up with only the set of records relating to the first ID found. So the basic logic would be to look at the value of the field in the ID column of the first record and then iterate down the rows until it came to a different value and then delete all rows below that. Its simple in concept, but im stuck on how to write this.
View 9 Replies
View Related
Jan 23, 2012
I have a workbook with many, many worksheets. On each worksheet there is a column called "Cost Code". I need a Macro to locate all like "Cost Code" values and extract data from several cells along that row. Macro would then tally the values from all those like cells on numerous worksheets and place sum on a seperate worksheet in the same workbook, on a "Totals" worksheet page.
For instance -The "Cost Code" value Macro to look for is 1000. This value is located on several worksheets. For every instance of "Cost Code" 1000 Macro is to find value of "Material Costs" in that row (always column M) and value of "Labor Hours" in that row (always column P). Macro would tally all "Material Costs" for "Cost Code" 1000 and put that sum on a "Totals" worksheet page in a specified cell. Macro would do the same for all "Labor Hours" for "Cost Code" 1000.
View 3 Replies
View Related
Nov 1, 2011
I have spent a good amount of time trying to create the below using botched IF functions, SUMIFS, SUMIF formulas etc.
Basically I have a sheet (called Sheet1) with the following columns:
A - Date (by individual day DD/MM/YYYY)
B - A catagory (so using the old example, Apples, Orange, Pears)
C - Price.
I would like to be able to create a formula, whereby I can sum the Total sales for each catagory across each month onto a summary sheet (sheet2).
I would use pivot tables, however am not sure how i can make all the require data appear permanently in the best way, especially if there is a one liner that can be done more easily. So far I have managed to create a sum dependent on month, but have been unable to encorporate the catagory.
View 1 Replies
View Related
Jan 15, 2014
I have a spreadsheet that lists dates in row 6, columns V through BE. We use these dates to log attendance for kids, so under each date there may be one of many symbols, such as "x" if the student was there. I would like to create a list of the dates that meet one of two criteria: either blank or containing "WR". So, let's say column Z is blank and Y has "WR" in it. In column FB I would like a list of all of the dates that were blank or WR, so I would want the dates contained in row 6 for Y and Z. Is there a function that will 1) look for a blank or WR across a row, 2) find the associated date for that blank in row 6, and 3) write the in another column in the form of a list, each date separated by commas? Here is an example of my information. I would like for a function to automatically make the list in column FB for any dates that are blank or contain "WR".
V
W
X
Y
Z
AA
AB
AC
...
FB
[code]....
List of Dates Absent
x
x
x
WR
x
x
x
1/23, 1/24
[code]....
View 2 Replies
View Related
Aug 26, 2012
Excel 2007, Windows 7.
Most details are shown in the spreadsheet below. I would like it to be dynamic because the quarterly and annual data dumps I'm working with are are hundreds to thousands of lines.
Have the list be sorted, which is a part of the first attempt, would be nice but is not necessary. At this point, just being able to generate the dynamic list would be fantastic.
Excel 2007ABCDE1product lines:consist of these product subtypes2Widget series:Widget.type1Widget.type23Fidget series:Fidget.type4Gidget series:Gidget.type1Gidget.type2Gidget.type356data dump of parts sold or used in repairs7product subtype repairedpart number
[code]......
View 5 Replies
View Related
Aug 31, 2006
Hi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.
View 9 Replies
View Related
Jun 1, 2009
i have a data in excel
In cell B6 , I have an amount . and in Cell C6 I have inserted the date at which that amount has been kept . Now what i want is I want the Data from D6 To O6 to calculate as to how many Days that 100000.00 Rs or Dollar have been kept each month . Each month has its own Days like January has 31 Days . So i want each Days to appear accurately. Like for example if i insert the 4-8-2009 from January till August 4-8-2009 , how many days that amount has been kept each month .
View 9 Replies
View Related
Aug 2, 2013
I am trying to find expected proportion of code per country by looking at current values. I have a list of countries and associated classifications (0-5) with counts, similar to as follows:
Country
code
count
USA
1
65465
USA
2
54651
USA
3
65411
[code]...
I am interested in creating a pivot table with the average of each code as a proportion of each country. The final table would be expected proportion of codes. The pivot table for this set would look like this:
Row Labels
Average
1
5.4%
2
3.9%
3
4.7%
[code]...
Mean per code of the proportion of code per country
View 1 Replies
View Related
Nov 15, 2013
I have to write a calculation that sums a range of values. Times that sum by a given amount but only if the total of the original sum is greater than a specific criteria -
A
B
C
D
E
F
G
H
I
J
K
L
M
PRODUCT
[Code] ........
In the above example it calculates the total units sold of Product A, by Month, by 5.00.
In Column M, It totals the number of sales units and then also totals the number sales units by that given value 5.00. So 426.5 x 5 = 2132.50
I need a sum that only totals the value over a given criteria.
EG. Total 426.5
Criteria > 300 = 126.5
Sum would equal 126.5 * 5
View 1 Replies
View Related
Mar 10, 2014
Attempting to calculate assignment hours based on three fields -
A - Date
B - Txt
c- Txt
D - Time [Start]
E - Time [END]
F - Number [Off]
G - Number [Travel]
H- Number (Calculated Total Project Hours)
I HAVE THE CALCULATION WORKING WITH THIS FORMULA
=(E5-D5)*1440/60-F5+G5
My goal [If Activity is NOT NULL and Start Time is NULL] Time would Default to 8.0 - ELSE use the formula to calculate [ Some projects I don't have a set start/end - and default to 8.0 hrs.
View 2 Replies
View Related
Jun 11, 2009
I have a worksheet that contains investment security purchases, including purchase date, purchase amount and maturity date, each in its own row. I need to develop a formula that will sum the purchase amount based upon the term in days (maturity date - purchase date) of the investment. The respective rows do not contain the term in days so the formula will need to calculate the days to determine the aggregating criteria described below.
One cell should contain the aggregate security purchase amount whose term is 1-90 days and another cell should contain the aggregate security purchase amount whose term is over 90 days. It seems that SUMPRODUCT with a condition is the best way to handle this; however, I cannot get the correct syntax of this formula.
View 8 Replies
View Related
Jul 9, 2009
I have 5 cells with drop down validation lists for the different types of window that we offer. From this list I will select the options and then input some dimensions in cell dimension fields. I then have a cell that gathers the text string from the 5 drop down cells.
My aim is to be able to calculate glass sizes from the text string and the dimensions. Each of the possible text strings will require different calculations and will define the sizes that I need to order. I know the equations that I will need for each of the options but cannot think of how to make them dependable on the text string cell.
I am looking for some way of using IF function to say if the text string returns as certain text then there is a certain calculation to calculate from the dimensions for the glass sizes that I need. I dont know if I've explained it too well nor even if its possible but if it is possible I will find a way as I have done some pretty exceiting things with excel in the past.
View 11 Replies
View Related
Jun 29, 2013
Criteria
Column B
Constant
Variable0
Variable1
Variable2
Variable3
2
[Variable2/Constant]
5
123
43
45
76
[Code] ..
This is my table. What formula should I use so that Column B shows the calculation depending on the Criteria column?
View 9 Replies
View Related
Apr 4, 2014
I require calculating difference between two columns. (Actual and Estimated).
Difference between these two numbers are calculated in third column, but it is reset every time actual number is non-zero.
Scenario:-
-Estimated Numbers are always followed by an actual number.
-After actual number is obtained, estimated numbers till that date will be added and deducted from actual number.
-After this, again estimated number will be added till next actual number is obtained for deduction.
If estimated number is not followed by actual number, then no need for difference calculation.
View 3 Replies
View Related
Aug 7, 2006
Trying to create an excel chart to create totals based upon different keys. I
need to be able to calculate how many customers there are by Manager and then
By Rep. Then to figure out how many were New, Current, Total # of RSVP and
attended for that Rep. Below is how I have started but I am having some
problems getting certain parts. I know when I get one the rest will fall into
place. I can calculate how many total customers by manager and by rep just by
doing a Countif command but how do I determine the # of New, Current etc. Is
there a If Than command? Managers Totals are simply his reps totals.
Example..
A B C D E
F G
1 Manager Rep Customer New Biz Current # RSVP # Actual Attend
Need totals to look something like this....
A B C D E
F G
1 # of Cust # New # Current #RSVP #
Actual Attend
2 Manager
3 Rep 1
4 Rep 2
View 15 Replies
View Related
Jul 16, 2013
How calculation can be made based on the date. I have 2 sheet (MATTER and REPORT). All productions made within sheet MATTER. Then I will produce report to be printed in sheet REPORT. I only include the date and calculation every matter will be automatically made based on date and item.
View 6 Replies
View Related
Oct 29, 2008
I need a formula that I can place in, lets say, Cell A1 that will reference Cells B1, B2, B3 and C1, C2, C3. These all have numbers & %'s in them. However, I want to "weight" the B cells by the percentages in C cells. The problem I am having is that when I change the %'s nothing is changing in the output...almost like I was doing nothing but averaging B1:B3 without the % to adjust.
This is how I envision it, but my calculation may be wrong of course :
A1 = average(sum(B1,B2,B3)*C1, sum(B1,B2,B3)*C2, sum(B1,B2,B3)*C3)
The Goal:
This would allow me to change the output depending on the percentage. So if I am looking at days to an appointment and the B cells represent areas of a city: B1 = 10, B2 = 5, B3 = 2 the average would be 5.6, however, I know that B1 city has more "weight" to it with our customer base so I want B1 to be closer to what will show as days out (which is obviously important as 10 days is not good). So I would assign C1 = 60%, C2 = 30%, C3 = 10%. This should pull the average more to 7 or 8 as it would lean more towards the B1 value. I hope this makes sense.
I did try going more of a route where I did something like =average(B1*C1,B2*C2,B3*C3) but that lowered all the original values instead and averaged them instead of pushing the number more towards the higher weighted % of B1 cell value. I can send an example spreadsheet if I am losing anyone.
View 8 Replies
View Related
Jun 28, 2006
Im trying to access data from 2 different cells (say A1, B1), its for calculating transformer hotspots. The cooling method of the transformer is naturaly air cooled oil.. until the winding temp reaches 75C ( I have winding temp data laid out on a column) then the fans come on and some constants in the formula change (need to read from the other cell, say B1) so it switches, but the fans stay on UNTIL the winding temp is 50C and then go off, so the characteristics change back to the previous one, so I need to switch back. How do I go about doing this... my current method starts working when it reaches 50 instead of waiting the temp to go to 75 then fall to 50 then do it... im a bit confused.. maybe i shouldnt be doing this on excel... ow well
View 9 Replies
View Related
Dec 17, 2007
I tried all those search and have no idea what to look for, sorry.
I have attached a single sheet with Footpath Budget for you to see.
In J6, we put in Y or N for Yes or No if there's a nature strip along the footpath.
Now, in cell G49 gives us the total square meters of nature strip.
What I want to do is if I put N in cell J6, cell G49 should be empty or zero because N means there's no nature strip. But if I put Y in J6, then cell G49 can give us total squares from cell C43.
So what formulas can I put in cell G49?
View 3 Replies
View Related
Feb 21, 2014
I have the following spreadsheet as an example:
A
B
C
D
E
F
G
H
1
Article/hours
10
27
33
15
33
40
40
[Code] ........
Column A contains codes for certain work activities.Row 1, from Column B and beyond contains number of worked hours.The matrix is populated with letter "D" to mark activity for particular work code for particular amount of worked hours.
My goal is to have a formula/script that analyzes this matrix, and returns a total amount of worked hours per work code. In other words, if a cell contains the letter "D", the amount of hours in Row1 for that particular column should be added to the total amount for that particular code, let's say code "501" as an example. For this example, the result should be a cell, lets say "A8", that contains the total amount of hours spent on code 501: in this example - 213 hours.
View 5 Replies
View Related
Dec 4, 2008
i have a sheet which has name of students (A2:A10), B1:E1 has project name. and the projects are catagorised by color red=high,green=medium, yello=low. i have given weightage to these color red=80%, green=50%, yello=20%.
similarly the involvment of the students in each project is shown by these three color in respective cell. like B2 is red, C2 is red,D2 is blank, E2 is green. (the weightage is same as above). in column F i want the total utilisation of each student, which is simply sum of product of weightage given to these colors.
e.g.
project 1project 2project 3project 4utilisationsam1.38joe0.54nick0.72
what i want is to calculate this utilisation number, but i want my sheet to be shown in the same color format.
i am sure that there is some solution for this.
View 9 Replies
View Related
Apr 13, 2007
I have been trying to put this together but am confused since I am new and this seems complex to me.
This is what I am trying to accomplish:
IE.
$F6 = 100
If $I6 is >1 AND $I7 is = 1 AND $I8 is =1 AND $I9 is =1 then $J6 =$F6
If $I6 is >1 AND $I7 >1 AND $I8 is =1 AND $I9 is =1 then $J6 =.5*$F6
If $I6 is >1 AND $I7 >1 AND $I8 >1 AND $I9 is =1 then $J6 =.33*$F6
If $I6 is >1 AND $I7 >1 AND $I8 >1 AND $I9 >1 then $J6 =.25*$F6
I am trying to divide a dollar amount equally between "Officers" if they were part of a transaction. By selecting the officers it would divide the total amount equally.
Is this even possible?
View 9 Replies
View Related
Jan 23, 2008
I'm trying to create an if statement to check to see if a cell has a value in it and return a math function. My problem is that some of the values contain more that just the name searched for. This is a basic example
A B C
10 Williams DXT if(B1="Williams",A1*.8,if(B1="Johnson",A1*.85,1))
20 Williams DPX if(B2="Williams",A2*.8,if(B1="Johnson",A2*.85,1))
30 Johnson AMV if(B3="Williams",A3*.8,if(B1="Johnson",A3*.85,1))
40 Johnson ACS if(B4="Williams",A4*.8,if(B1="Johnson",A4*.85,1))
50 Smith TWN if(B5="Williams",A5*.8,if(B1="Johnson",A5*.85,1))
View 4 Replies
View Related
Aug 27, 2009
in cell A1 the user will select from a drop down list 5.50, 11,22, 33, 55. in cell B1 user selects from the drop down list a number 1 - 9. now in cell c1 I wont it to do some calculation based on the selections.
If b1=3, then A1*.20, if B1=2, then A1*.30, If B1=1, then A1*.50 and if B1 is greater then 3, post 0, if B1 is empty, then leave c1 empty.
View 5 Replies
View Related
Dec 18, 2007
I need an excel function for this...
Say,
Cell A2= 8
Cell A3= 1
If A2 < (9*A3), then Cell A4 will say "Plastic", otherwise it will look to the next function, which is If A2 < (10*A3), then A4 will say "Compact", otherwise, it will look to the next one which is If A2 < (15*A3), then A4 will say "Semi-Compact" otherwise If A2> (15*A2), it'll say "Slender". so at the end, Cell A4 can only be one of these: 'Plastic' or 'Compact' or 'Semi-compact' or 'Slender'. How can i combine all the functions together?
View 2 Replies
View Related
