working out productivity using HOURS and units to establish accurate daily result

Hi,

I have a slight issue with a formula. Seems to work most of the time but have an issue with smaller numbers.

The formula I am using to calculate productivity is:

=B26/HOUR(G26/(7*F26))

One person is coming up with a #DIV/0 error and this appears to be because when evaluating the formula we have a 0.267777 and then B26 tries to divide by zero hence the error.

The numbers I have are as follows:

B26 = 7
G26 = 5:46 (in [h]:mm format)
F26 = 1

basically the formula is = (new cases/(hours worked*FTE)

any help around the error or a better formula?

You seem to be including the (7*F26) in the hour calculation. Is this intended? or do you mean:
=B26/HOUR(G26)/(7*F26)

I'm not sure.. I was under the impression the formula was
" =UNIT/HOUR(hours worked/(hours in day*FTE))"

if i use another's data it appears to work like i saw.

lets work with

B26 being 19
G26 being 33:00
F26 being 1

This works out at 4.75

which would appear to be correct. IF i use UNIT/HOUR(hours worked)/(hours in day*FTE) i get 0.30 which is clearly incorrect.

even eyeballing you could see they are more than 0.30 productive


I do have an alternative around it by converting hours to days in another cell but ideally i didn't want this. Based on other things im assuming this could be the only way.

Still not 100% sure what measure of productivity you are trying to calculate however try the following (I have included minutes to be more accurate):
For Units/Hour: =B26/(HOUR(G26)+MINUTE(G26)/60)
For Unit/Hour for FTE equivalent (assuming hours worked is total hours in day/week etc and not actual hours worked by employee) try:
=B26/(HOUR(G26)+MINUTE(G26)/60)/F26

Other than that if you want to not have a #DIV/0 Error display then use an if statement, either IF(ISERROR([full formula]),"Cannot Calc",[full formula]) or check the divisor part using an IF statement ensuring it is not equal to 0