What's wrong the formlua

  • Thread starter Thread starter David S
  • Start date Start date
D

David S

What the spreadsheet figures out is how many pounds of
product a person averages per hour of labor. Below are
the formatting and formula I'm using..

I13 is the total tonnage for the week, which is entered
daily on other sheets within the book and totaled on the
recap sheet, K13 is the hours worked, again it is totaled
on the recap sheet from other sheets and is formatted as
[h]: mm, and L13 gives me the average. The formula I'm
using for L13 is =IF(K13=0,0,I13/(HOUR(K13)+(MINUTE
(K13)/60))). The function works great until the hours
total more than 23:59, and then they are way off. The
result for 225799 pounds moved in 24:24 hours is showing
564498 pound per man-hour when the correct answer should
be 9253.
 
Instead of using hour and minute if K13 is the hours worked in a time format
you can use


=I13/(K13*24)

format as general
 
What the spreadsheet figures out is how many pounds of
product a person averages per hour of labor. Below are
the formatting and formula I'm using..

I13 is the total tonnage for the week, which is entered
daily on other sheets within the book and totaled on the
recap sheet, K13 is the hours worked, again it is totaled
on the recap sheet from other sheets and is formatted as
[h]: mm, and L13 gives me the average. The formula I'm
using for L13 is =IF(K13=0,0,I13/(HOUR(K13)+(MINUTE
(K13)/60))). The function works great until the hours
total more than 23:59, and then they are way off. The
result for 225799 pounds moved in 24:24 hours is showing
564498 pound per man-hour when the correct answer should
be 9253.
Click to expand...

The HOUR function is the HOUR of the time value, and can only be between 0
(12AM) and 23 (11 PM).

I think you want to express the total hours worked per week as a decimal
number, rather than as a fraction of a day which is the way Excel stores that
information.

To convert the total hours to a decimal number, multiply by 24.

So try this formula in place of yours:

=IF(K13=0,0,I13/(K13*24))



--ron
 
Thanks Ron.....

-----Original Message-----
What the spreadsheet figures out is how many pounds of
product a person averages per hour of labor. Below are
the formatting and formula I'm using..

I13 is the total tonnage for the week, which is entered
daily on other sheets within the book and totaled on the
recap sheet, K13 is the hours worked, again it is totaled
on the recap sheet from other sheets and is formatted as
[h]: mm, and L13 gives me the average. The formula I'm
using for L13 is =IF(K13=0,0,I13/(HOUR(K13)+(MINUTE
(K13)/60))). The function works great until the hours
total more than 23:59, and then they are way off. The
result for 225799 pounds moved in 24:24 hours is showing
564498 pound per man-hour when the correct answer should
be 9253.
Click to expand...

The HOUR function is the HOUR of the time value, and can only be between 0
(12AM) and 23 (11 PM).

I think you want to express the total hours worked per week as a decimal
number, rather than as a fraction of a day which is the way Excel stores that
information.

To convert the total hours to a decimal number, multiply by 24.

So try this formula in place of yours:

=IF(K13=0,0,I13/(K13*24))



--ron
.
Click to expand...
 
Back
Top