Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
charting how many widgets over time [email protected] Charts and Charting in Excel 3 May 1st 06 08:05 PM
Are there any free XL widgets out there ... Speedmaster Excel Discussion (Misc queries) 2 February 22nd 06 04:49 PM
Calculating number of instances occuring in an hour for 24 hours rp Excel Worksheet Functions 2 January 12th 06 04:27 PM
Calculating a colmun to total a 40 hour work week Harley mom Excel Worksheet Functions 3 December 20th 05 08:41 PM
Calculating time worked using 100ths of an hour, from 1 day into n maintchief New Users to Excel 4 October 27th 05 11:48 PM


All times are GMT +1. The time now is 06:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"