How Do I Set Up A Formula For Parts (or Units) Per Hour
Apr 12, 2006
I'm trying to set up a spreadsheet that tracks total hours worked and total
units produced. Then I need to have a column that shows how many units per
hour were produced.
Currently, I have something like this:
Column A is in elapsed time [h]:mm
Column B is a Number with two decimal places
Column C divides Column B by Column A
However, I get strange results. For example:
Column A is 6:24:00
Column B is 13
Column C shows 120.00
13 parts in 6:24 hours should be something like 2.1666 parts per hour!
View 10 Replies
ADVERTISEMENT
Nov 12, 2013
Is it possible to take data in format dd:hh:mm and convert to working days - ie. 8 hour days?
For example:
02:12:15 (60 hours and 15 minutes .. or 3615 minutes)
to be converted to:
07:04:15 (7 working days: 4 hours: 15 minutes)
View 3 Replies
View Related
Feb 19, 2009
i need a formula that will return the total value of units which is greater than 20000 in col b between 5 and 30 days
AgeUnits272,472,215.29134,354.83112,714,300.007-112,566.006-239,853.006-105,938.006-36,792.006-608,695.001827,632.007-21.00
View 9 Replies
View Related
Nov 27, 2013
We are trying to work out a pricing model based on how many units we will produce. In production the price per unit will be really high when you produce a low quanity, and the price per unit will be lower with the more units you produce (but never reach zero). What we are trying to accomplish is to create a formula to build a pricing structure that accounts for this curve without having to price every possible scenario.
For example, if we have the following data where for 1 unit we charge $3 per unit to produce, for 5 units we charge $2 per units to produce, etc...:
1 units @ $3 per unit
5 units @ $2 per unit
10 units @ $1 per unit
What I need to figure out is how much it would cost per unit if we were to produce 3 units. I'm guessing it would be somewhere around the $2.40 mark give or take. Or say 8 units, would be something like $1.50 per unit.
Is there a formula that can calculate this? I've tried thinking of it as a moving average, or prorating the pricing.
View 2 Replies
View Related
Dec 21, 2006
I have a document needed to be printed with some pages in the middle in landscape page type, the rest in portrait. If using Word it would be easier, but in Excel I cant find the section break to chage page setup separately. Is there anyway to do it. Currently I'm printing the document separately in portrait and then landscape with some page break added and page number modified. However it's quite troublesome and easy to make mistake.
View 3 Replies
View Related
Dec 10, 2008
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.
I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".
View 3 Replies
View Related
Sep 4, 2006
I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).
The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.
The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.
My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.
Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.
Of course if demand exceeds the allotted time we put on overtime.
Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?
And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.
I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.
View 9 Replies
View Related
Oct 21, 2013
I work in a computer lab and we have to keep track of how many people sign in (using excel 2010), and how long they were here.
I'm looking for a way to count how many entries were made per hour for every hour someone was signed in.
For example someone signs in at 9:22 am and leaves at 3:34 pm.They were here from 9-10, 10-11, 11-12 etc.. and its getting very tiresome to go through and manually enter a "1" for each individual hour in each cell under the hour.
Is there a way I can feed the in and out times into a spreadsheet and have it automatically count how many people were in the lab each hour?
A
B
C
D
E
1
2
4
2
2
In
Out
8 am
9 am
10 am
[Code] ...........
View 3 Replies
View Related
Jan 19, 2014
I have this huge formula and I want to run it in one row, across 350 columns:
=IF(IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1))="","/////",IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(3:3))-10,1)))
The only thing I need to change from one column to the next is the four places where it says ROW(3:3). I need that to ratchet up as it goes across to the next. The cell to the right should say:
=IF(IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1))="","/////",IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(4:4))-10,1)))
Then the next cell to the right should say:
=IF(IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1))="","/////",IF(ISERROR(INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1)),"",INDEX('Allocated & Spent'!$G$11:$G$307,SMALL(IF('Allocated & Spent'!$F$11:$F$307="x",ROW('Allocated & Spent'!$G$11:$G$307)),ROW(5:5))-10,1)))
etc., etc.
How can I fill across so those go up and I don't have to fill in those four numbers 350 times?!
View 6 Replies
View Related
Nov 13, 2009
How to use VBA codes to automatically change parts of the formula? Example1:
Change formula "=SUM(A$10:A$100)" into "=SUM(A$10:A$1000)"; Example2: Chage formula.........
View 2 Replies
View Related
Dec 2, 2013
I have a time table which some hours are 3 hours time period. I want to distribute to new table hour by hour this kind of hours, or hour to same hour period.
Attached file, defined example and detail.
View 1 Replies
View Related
Mar 1, 2012
I have a program that outputs a total amount of time that a service was provided. This time is reported in hours minutes [Example: 01.08 (hh.mm)]. I need a formula that will "round" to the closest .25 based on a 7 minute window on either side. Here how it would need to work....
0 - 7 Minutes = .00 or 1.00 - 1.07 = 1.00
8 - 22 Minutes = .25 or 1.08 - 1.22 = 1.25
23 - 37 Minutes = .50 or 1.23 - 1.37 = 1.50
28 - 52 Minutes = .75 or 1.28 - 1.52 = 1.75
53 - 7 Minutes = .00 or 1.53 - 2.07 = 2.00
View 4 Replies
View Related
Apr 19, 2014
I have a spreadsheet that I use to track my stocks. I have installed a third party add-in that goes out to the internet and grabs various stock information. But, when you are pulling info on about 30 stocks and getting info like name, price, dividend, div pay date and such, it slows the spread sheet down tremendously. Is there a way to keep the value in the cell and change it only during a certain time period such as 5 minutes before the top of the hour to five minutes after the top of the hour.
View 2 Replies
View Related
Dec 28, 2006
I am building a spreadsheet to manage 15 folks wages to be able to know how much money has been spent.
We routinely work 40 hour weeks and have a sheet that automatically multiplies hour work by person (Say A1) times their hourly rate (say A2)
My question is how could I right it a formula that allows me to type in their hour work (A1) if it's greater than 40 hours?
Say A1 is 40, A2 =$20.00, A3 =(A1*A2) $800.00
If the A1 is 43 what would I put into A3 that would automatically multiply the additional hours over 40 times 1.5 A2
View 9 Replies
View Related
Mar 1, 2014
I have sheets with names of people in columns....some married...some not. When they are married, here's a sample format...
Jones, Donald T | Baker, Sarah Jane | Jones, Sarah Jane | Smith, Sarah J | Jones, Sarah Jane Smith
In this example, I would like to be able to determine which of the Sarah's belongs to Donald w/o having to visually look at each record ( 100,000's of records). (FYI: the names for Sarah would/could be her Maiden Name and possibly a name or two from a former marriage). What I need to be able to do is match and extract the names of Jones, Donald T and Jones, Sarah Jane and Jones, Sarah Jane Smith and eliminate Smith, Sarah J and Baker, Sarah Jane.
In my example, Donald is in the first column, but can be in any column on a row so the name positions are random across the columns. However, the format for each column is then same...Last Name, First Name Middle Name(or Initial) with a comma always after the last name in each column. The length of the last name also varies.
VBA or Formula that will search the cells in the columns of each row and return the names (complete contents of the cells with matching last names) that have a matching last name for that row.
View 3 Replies
View Related
Apr 27, 2014
Formula to calculate time allotted minus time used and show the difference in hour and minute.
View 1 Replies
View Related
Jan 11, 2009
I need to work out how long the batten has to be so the roof sheets fit evenly, the measurement has to start from 1460mm and go up in increments of 80mm eg 1540mm, 1620mm, 1700mm and so on.
But the number has be closest increment of 80mm over the shed width if this makes sense, the size of the battens for 2400 width shed would be 2420mm but i need this to work out for any width shed not just 2400.
View 3 Replies
View Related
Dec 13, 2007
What I am trying to do is take entered data in a spread sheet, and convert the units within that cells.
I have several entries for data in this sheet. Here is what I want to do:
-I have 104 entered in cell B2
-I want to convert that reading (which is ºF) to ºC in that same cell
-I would like a button or selection from a drop down box to select Metric or Imperial
I want to do this so if the whole spread sheet is entered in metric, I can select a name or click a button to have all the data switch to imperial. I know I can do this with two sheets in the same file, but I am not sure if this is possible to do in a cell I entered the data in. Any help would be great. I am using Office 2003.
View 13 Replies
View Related
Jan 25, 2009
I'm wondering how to display the following cost model on a graph (this is a piecewise-linear cost model for some production company):
Cost per unit for 0-10 units: $1
Cost per unit for 11-20 units: $3
Cost per unit for 21-30 units: $5
Cost per unit for 31-40 units: $8
Fixed cost: $100 (this is regardless of the number of units produced)
Basically I'm looking to construct a cost vs units graph.
View 11 Replies
View Related
Feb 3, 2009
I have been tasked with tracking the expected income for our Advertising Department. (I work for a newspaper.) The rate I charge an ad depends upon the total number of advertising units a customer purchases during an entire billing period. Essentially: I need to have a total of column G for when column B matches the current row. I need this in column K. The value of K should be the same when the value of B is the same.
My programing flow chart I sketched out checked to see if any row in column B that is above the current row matched the current row's data. If it did then it would use that row's value for the total number of units. If it did not it would add up the units from G when B matched the current column. I attached a document
View 2 Replies
View Related
Jul 24, 2006
I have a spreadsheet that keeps attendance. Now I need to calculate the
units (1 unit= 15 minutes) the children are actual here. On a normal
schedule they are here for 16 units but if the are late the units get
subtracted. When entering the data, they would like to use letter instead of
numbers.
View 9 Replies
View Related
Sep 15, 2007
Refer to the attached sample workbook,
(Keep in mind that there is an error in the sample workbook; Im not sure how to remove and re-attach the sample workbook as this is my first day on this forum.)
Cell F19 should contain =D19*B19
Column 1- These values will not change and represent price/cost of each UNIT
Column 2- "UNITS" the quantity or number of items at column1 value
Column 3- "$ AMT" the total cost of UNITS at that price/cost, i.e. Column1*Column2
Im looking for a way populate the values in Column2 when, "NUMBER OF UNITS" AND "AVERAGE COST PER UNIT" are changed
Ideally speaking, As few units in one row as possible to allow for a good mix.
For example: What i don't want is $9000*86 for 774k.
If it helps: "AVERAGE COST PER UNIT" will always be a whole number rounded up to the nearest 1000.
I believe a Most Even distribution of units will work fine.
After the distribution/population of fields in column2;
D29 must equal F3
F29 must equal F7
I'm looking for something that will populate the values in D12:D27 while adhering to the two conditions above. Even when the Input at F3 and F4 are changed.
The values in "UNITS" represent the number of a specific cost item in inventory when added together do not exceed the "TOTAL COST OF INVENTORY". Depending on store location, the "AVERAGE COST PER UNIT" and/or the "NUMBER OF UNITS" will change. It will be used to help forecast how many of a specific cost item should be purchased based on the overall average unit cost without exceeding the budget or inventory space.
View 9 Replies
View Related
Mar 4, 2009
I have three columns. Lets assume i have One header row and 3 rows of actual data and the 4th row is for totaling column B values.
Column A is a col of "Ingredients", column B is the amount of the ingredient (from col A) used. The value is either in Oz or Lbs. Can be either. Column C is a yes or no column. If the value is Yes then i want to have the value in col-B included in my overall sum which is in the 4th row. If the value is No then i do not want the value in col-b (in that row) included in the overall sum.
Any ideas on how to do this?
Thanks.
david
View 10 Replies
View Related
Apr 3, 2009
i have been trying to write an Excel sheet in 2007
that can be given a random amount of (money) and show
me the best way to spend it.
Weapons:
Name - Power - Cost
1 - 10 - 20
2 - 20 - 40
3 - 40 - 80
4 - 80 - 160
5 - 160 - 320
6 - 320 - 640
So i have 40K to spend, i want to eneter 40000 into a box and it
will auto populate the number of units i can buy, always setting the weapon
#6 as the priority.
View 13 Replies
View Related
Oct 23, 2009
I have lists of room areas in metres squared, I extract the web based lists to excel: for example.
10.56 m2
14.23 m2
21.34 m2
How do i calculate the total of these amounts?
Is there a formular to remove the "m2"?
I have tried using =SUBSTITUTE(A1,"m2","") which deletes the "m2" from the cell, but then I can't use =SUM(A1:A3) as this totals the 3 cells as 0.
If i use =SUM(A1+A2+A3) the total is correct, but I have over 3000 cells in one column to add together.
Is there a formula or something to remove all instances of "m2" or "m3" from the whole sheet or another way i can calculate the totals.
View 12 Replies
View Related
Nov 23, 2009
I have a simple spreadsheet that allows the user to enter a dimension in metric or inches. I want to display the other units in the adjacent cell. In cell A1, the units are "Metric" or "Inch" in a pull down list. In cell A2, the value is entered.
In cell A3 i want to show the value in the other units. So if A1 is Metric, then take A2 and divide by 25.4. And if A1 is Inch, then take A2 and multiply by 25.4.
Also, if A1 is Inch, then display 2 decimal places in A3, and if A1 is Metric, then display 3 decimal places in A3. Is this possible?
View 3 Replies
View Related
Dec 4, 2012
I have a column named length and values are in different units ie few values in mm, few in cm, few in m. i have to filter out columns having range from 9mm to 6cm. How to do this task.(10mm = 1cm and 100cm = 1m)
Consider sample data as:
4 m
8 cm
9 m
3 mm
9 mm
6 m
6 cm
3 cm
2 m
View 6 Replies
View Related
Oct 9, 2011
Can split a single cell within excel to two or three units of the single cell?
View 2 Replies
View Related
May 8, 2008
I need a formula that will tell me what % of the first 5 units sold are new, based on the dates provided. This is an example of the data I am working with. What i am looking for is a cell that will respond with .4 (meaning 40% of the first 5 units are new) ...
View 9 Replies
View Related
Mar 2, 2009
I have a formula to add the volume (units) for a customer. The formula is:
=E5+E10+E21+E31+E38+E52+E65+E69
Is there a formula that I can use instead of the one above that will find the customer number and automatically sum the units?
View 9 Replies
View Related
