Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating productivity: widgets per hour
My spreadsheet contains a row of hours worked by our team per day -- 31:06,
18:32, 43:44. I also have a column of number of widgets produced that day. How can I get Excel to calculate the number of widgets produced per hour? For example, row A is 7/1/07 and we had 40:00 labor hours that day (A2) and 60 widgets produced that day (A3). I need a calculation that says =A3/A2 and the result is 1.5 widgets per hour. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating productivity: widgets per hour
Believe it or not, you wrote the formula.
=A3/A2 is the formula to put into any cell other than A3 or A2 to get the result. If this is early in the game you might want to consider laying out your data in a different fashion - to make these calculations a little easier. Consider 4 columns: A B C D 1 Date Widgets Labor Hours Widgets/Hour 2 7/1/07 60 40:00 =B2/C2 3 7/2/07 75 60:30 =B3/C3 What makes that layout easier for the novice is that the formulas in column D can easily be 'filled' on down the sheet as more dates are added. Easier than copying from say A4 down to A7 for a paste, then from A7 into A10, etc. But you may have different needs. By the way - if you put that =A3/A2 formula into A4 and later copy it and paste it into A7, it will then read =A6/A5 - Excel does that type of work for you automatically during a copy and paste (or a fill) operation. "ClarkeTeam" wrote: My spreadsheet contains a row of hours worked by our team per day -- 31:06, 18:32, 43:44. I also have a column of number of widgets produced that day. How can I get Excel to calculate the number of widgets produced per hour? For example, row A is 7/1/07 and we had 40:00 labor hours that day (A2) and 60 widgets produced that day (A3). I need a calculation that says =A3/A2 and the result is 1.5 widgets per hour. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating productivity: widgets per hour
Thanks -- however, I believe my problem is in the formatting of the labor
hours column. When I type in the hours -- 40:00, 33:47, etc. -- Excel automatically formats it in date format. For example, I typed in 41:18 for 41 hours, 18 minutes. Excel automatically formats it to "1/1/1900 5:18:00 PM". I have tried to do a custom format and have not been able to get away from this. When I do my formula, I believe this format is not allowing the correct calculation. I thought this would be an easy thing to fix, but an hour of searching Excel help and online help is getting me nowhere. Thanks for any help you can offer! "JLatham" wrote: Believe it or not, you wrote the formula. =A3/A2 is the formula to put into any cell other than A3 or A2 to get the result. If this is early in the game you might want to consider laying out your data in a different fashion - to make these calculations a little easier. Consider 4 columns: A B C D 1 Date Widgets Labor Hours Widgets/Hour 2 7/1/07 60 40:00 =B2/C2 3 7/2/07 75 60:30 =B3/C3 What makes that layout easier for the novice is that the formulas in column D can easily be 'filled' on down the sheet as more dates are added. Easier than copying from say A4 down to A7 for a paste, then from A7 into A10, etc. But you may have different needs. By the way - if you put that =A3/A2 formula into A4 and later copy it and paste it into A7, it will then read =A6/A5 - Excel does that type of work for you automatically during a copy and paste (or a fill) operation. "ClarkeTeam" wrote: My spreadsheet contains a row of hours worked by our team per day -- 31:06, 18:32, 43:44. I also have a column of number of widgets produced that day. How can I get Excel to calculate the number of widgets produced per hour? For example, row A is 7/1/07 and we had 40:00 labor hours that day (A2) and 60 widgets produced that day (A3). I need a calculation that says =A3/A2 and the result is 1.5 widgets per hour. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating productivity: widgets per hour
First we need to do some formatting of the cells and then kind of remember to
ignore anything that might show up in the formula bar at the top of the window later on. Choose your labor hours cells/column and use Format | Cells and go to Custom formatting and type in this as the format to use: [h]:mm Now when you enter 41:18 it will remain displayed as 41 hours and 18 minutes. Next thing to understand is how Excel stores time values: as fractions of days. And since 1 hour is 1/24 of a day, then we need to multiply the 40:18 value by 24 to get a real number to divide by, so your formula becomes =A3/(A2*24) and make sure that the cell that is in is formatted as General (or numeric other than date or time) to display 1.4... instead of something unexpected like 11:24 (which would happen if Excel automatically tries to convert the results of the formula into time format instead of General). "ClarkeTeam" wrote: Thanks -- however, I believe my problem is in the formatting of the labor hours column. When I type in the hours -- 40:00, 33:47, etc. -- Excel automatically formats it in date format. For example, I typed in 41:18 for 41 hours, 18 minutes. Excel automatically formats it to "1/1/1900 5:18:00 PM". I have tried to do a custom format and have not been able to get away from this. When I do my formula, I believe this format is not allowing the correct calculation. I thought this would be an easy thing to fix, but an hour of searching Excel help and online help is getting me nowhere. Thanks for any help you can offer! "JLatham" wrote: Believe it or not, you wrote the formula. =A3/A2 is the formula to put into any cell other than A3 or A2 to get the result. If this is early in the game you might want to consider laying out your data in a different fashion - to make these calculations a little easier. Consider 4 columns: A B C D 1 Date Widgets Labor Hours Widgets/Hour 2 7/1/07 60 40:00 =B2/C2 3 7/2/07 75 60:30 =B3/C3 What makes that layout easier for the novice is that the formulas in column D can easily be 'filled' on down the sheet as more dates are added. Easier than copying from say A4 down to A7 for a paste, then from A7 into A10, etc. But you may have different needs. By the way - if you put that =A3/A2 formula into A4 and later copy it and paste it into A7, it will then read =A6/A5 - Excel does that type of work for you automatically during a copy and paste (or a fill) operation. "ClarkeTeam" wrote: My spreadsheet contains a row of hours worked by our team per day -- 31:06, 18:32, 43:44. I also have a column of number of widgets produced that day. How can I get Excel to calculate the number of widgets produced per hour? For example, row A is 7/1/07 and we had 40:00 labor hours that day (A2) and 60 widgets produced that day (A3). I need a calculation that says =A3/A2 and the result is 1.5 widgets per hour. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating productivity: widgets per hour
Thanks! This worked like a charm.
"JLatham" wrote: First we need to do some formatting of the cells and then kind of remember to ignore anything that might show up in the formula bar at the top of the window later on. Choose your labor hours cells/column and use Format | Cells and go to Custom formatting and type in this as the format to use: [h]:mm Now when you enter 41:18 it will remain displayed as 41 hours and 18 minutes. Next thing to understand is how Excel stores time values: as fractions of days. And since 1 hour is 1/24 of a day, then we need to multiply the 40:18 value by 24 to get a real number to divide by, so your formula becomes =A3/(A2*24) and make sure that the cell that is in is formatted as General (or numeric other than date or time) to display 1.4... instead of something unexpected like 11:24 (which would happen if Excel automatically tries to convert the results of the formula into time format instead of General). "ClarkeTeam" wrote: Thanks -- however, I believe my problem is in the formatting of the labor hours column. When I type in the hours -- 40:00, 33:47, etc. -- Excel automatically formats it in date format. For example, I typed in 41:18 for 41 hours, 18 minutes. Excel automatically formats it to "1/1/1900 5:18:00 PM". I have tried to do a custom format and have not been able to get away from this. When I do my formula, I believe this format is not allowing the correct calculation. I thought this would be an easy thing to fix, but an hour of searching Excel help and online help is getting me nowhere. Thanks for any help you can offer! "JLatham" wrote: Believe it or not, you wrote the formula. =A3/A2 is the formula to put into any cell other than A3 or A2 to get the result. If this is early in the game you might want to consider laying out your data in a different fashion - to make these calculations a little easier. Consider 4 columns: A B C D 1 Date Widgets Labor Hours Widgets/Hour 2 7/1/07 60 40:00 =B2/C2 3 7/2/07 75 60:30 =B3/C3 What makes that layout easier for the novice is that the formulas in column D can easily be 'filled' on down the sheet as more dates are added. Easier than copying from say A4 down to A7 for a paste, then from A7 into A10, etc. But you may have different needs. By the way - if you put that =A3/A2 formula into A4 and later copy it and paste it into A7, it will then read =A6/A5 - Excel does that type of work for you automatically during a copy and paste (or a fill) operation. "ClarkeTeam" wrote: My spreadsheet contains a row of hours worked by our team per day -- 31:06, 18:32, 43:44. I also have a column of number of widgets produced that day. How can I get Excel to calculate the number of widgets produced per hour? For example, row A is 7/1/07 and we had 40:00 labor hours that day (A2) and 60 widgets produced that day (A3). I need a calculation that says =A3/A2 and the result is 1.5 widgets per hour. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating productivity: widgets per hour
Glad to hear that. Thanks for letting us know how it worked out.
"ClarkeTeam" wrote: Thanks! This worked like a charm. "JLatham" wrote: First we need to do some formatting of the cells and then kind of remember to ignore anything that might show up in the formula bar at the top of the window later on. Choose your labor hours cells/column and use Format | Cells and go to Custom formatting and type in this as the format to use: [h]:mm Now when you enter 41:18 it will remain displayed as 41 hours and 18 minutes. Next thing to understand is how Excel stores time values: as fractions of days. And since 1 hour is 1/24 of a day, then we need to multiply the 40:18 value by 24 to get a real number to divide by, so your formula becomes =A3/(A2*24) and make sure that the cell that is in is formatted as General (or numeric other than date or time) to display 1.4... instead of something unexpected like 11:24 (which would happen if Excel automatically tries to convert the results of the formula into time format instead of General). "ClarkeTeam" wrote: Thanks -- however, I believe my problem is in the formatting of the labor hours column. When I type in the hours -- 40:00, 33:47, etc. -- Excel automatically formats it in date format. For example, I typed in 41:18 for 41 hours, 18 minutes. Excel automatically formats it to "1/1/1900 5:18:00 PM". I have tried to do a custom format and have not been able to get away from this. When I do my formula, I believe this format is not allowing the correct calculation. I thought this would be an easy thing to fix, but an hour of searching Excel help and online help is getting me nowhere. Thanks for any help you can offer! "JLatham" wrote: Believe it or not, you wrote the formula. =A3/A2 is the formula to put into any cell other than A3 or A2 to get the result. If this is early in the game you might want to consider laying out your data in a different fashion - to make these calculations a little easier. Consider 4 columns: A B C D 1 Date Widgets Labor Hours Widgets/Hour 2 7/1/07 60 40:00 =B2/C2 3 7/2/07 75 60:30 =B3/C3 What makes that layout easier for the novice is that the formulas in column D can easily be 'filled' on down the sheet as more dates are added. Easier than copying from say A4 down to A7 for a paste, then from A7 into A10, etc. But you may have different needs. By the way - if you put that =A3/A2 formula into A4 and later copy it and paste it into A7, it will then read =A6/A5 - Excel does that type of work for you automatically during a copy and paste (or a fill) operation. "ClarkeTeam" wrote: My spreadsheet contains a row of hours worked by our team per day -- 31:06, 18:32, 43:44. I also have a column of number of widgets produced that day. How can I get Excel to calculate the number of widgets produced per hour? For example, row A is 7/1/07 and we had 40:00 labor hours that day (A2) and 60 widgets produced that day (A3). I need a calculation that says =A3/A2 and the result is 1.5 widgets per hour. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
charting how many widgets over time | Charts and Charting in Excel | |||
Are there any free XL widgets out there ... | Excel Discussion (Misc queries) | |||
Calculating number of instances occuring in an hour for 24 hours | Excel Worksheet Functions | |||
Calculating a colmun to total a 40 hour work week | Excel Worksheet Functions | |||
Calculating time worked using 100ths of an hour, from 1 day into n | New Users to Excel |