How do I set up a formula for parts (or units) per hour?

[Guest]

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!
Please help!

 

[Guest]

I think column C ought to be showing 48.75

You need to use the formula

=b1/(a1*24)

 

[Guest]

If only 13 total parts were produced, how is it supposed to read 48.75 parts
per hour?

It took 6 hours, 24 minutes to produce 13 parts. I need to show that that
breaks down to 2.166 parts per hour.

 

[Peo Sjoblom]

With 13 in B1 and 6:24 in C1

=B1/(C1*24)

format result as general or else it will display as time

I get 2.03125 not 2.166


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

I believe Duke's formula is correct. However, it produces an answer of
2.03125 and not 48.75. Try the formula out and see what you get. You may
have to play with the formats of the cells. Ensure that the cell where the
answer goes is formatted as General or some sort of number format. Look
under Excel help for Time and you may learn more about how Excel calculates
with time and the issues you are having.

Hope this helps.

Bill Horton

 

[Guest]

Ok, it appears that you and Duke are correct (Peo as well, but for a
different result). I'm still not sure where Duke got 48.75.

I guess my next question is what is the significance of multiplying the
hours by 24?

 

[Peo Sjoblom]

It's to convert the time value into a decimal value, 1 hour in excel = 1/24
that means if that you have 6:00 the underlying value is in fact 0.25 that
also means that if you divide 13/0.25 you will get 52 thus by converting
6:00 into 6.00 it will give you the correct value 13/6


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

I could have been clearer - using your numbers col C should NOT have shown
120 (as your example stated), it should have shown 48.75. After multiplying
the time value by 24 to get actual hours, the answer should have been a
little over 2

 

[Guest]

48.75 is what your initial example should have shown in column C, not the 120
you actually stated. I pointed it out only because IF your division resulted
in 120 there was some other error in addition to what has been pointed out.

 

[Guest]

Oh, ok. I misunderstood what you had typed. My mistake. I have no idea
why it was giving me 120.00.

I still don't understand why you have to multiply the time by 24hours. I
entered the value in hours. 6hours. Why doesn't 13/6:24 (B2/B1) give me
the 2.03?

 

[Guest]

Because Excel stores time as a fraction of a day. Reformat your cell to
general and you'll see that 6 hours & 24 minutes is something just a little
higher than 1 quarter of a day: .2666667 So, if you divide 13 by that
number you get a result of 48.75