Items picked per partial hrs not full hrs ( items / time)

[mingandmong]

Hi
I'm using excel 2016, with data running down the columns Q,S,T
it calculates items divided by hrs, but I'm stuck as i have partial hrs and this over inflates the value in T1, is there anyway i can make it more accurate say to the nearest 15 minutes
also how do i remove #DIV/O! as my [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IFERROR does not work on the formula below[/FONT]



Cell Q1 = 272 (number value)
Cell S1 = 0:45:20 ( time value)
Cell T1 resides my formula

=Q1/(S1*24)​

The result is 360 which is greater than the cell Q1

Many thanks

 

[Gerald Higgins]

So what should the answer be ?

If you are picking 272 items in 45m 20s, that's the same as an hourly rate of 360.


EDIT TO ADD - ALSO, what do you want to do instead of getting a DIV/0 message ?
This should work (it works for me) ....
=IFERROR( [YOUR FORMULA], 0)
It will show zero instead of a DIV/0 warning.

 

[mingandmong]

Hi Gerald Higgins
The formula works correctley for any hrs over 1 hr inc part incuments ie
Q2= 1,209, S2=7:07:18 T2 = 170

So i would presume under 1 hrs it cannot calculate more than the original cell value of Q1 (272)

ie if the target is paid on a bonus of say 300 ph the operator would receive a bonus as the result is 360 but only actually picked 272 if that made sense

or you can just say that's coorect pro rata

PS your IFERROR now works thankyou

 

[Gerald Higgins]

So i would presume under 1 hrs it cannot calculate more than the original cell value of Q1 (272)

~~~

or you can just say that's coorect pro rata

You just need to decide for yourself what you want to do.

If you're happy with the hourly rate (even if less than an hour was worked) then use what you've got.
If you want to change it so that IF less than an hour was worked, use the actual number picked as the rate, you can use something like . . .
=IF(S1<1/24,Q1,Q1/(S1*24))

It's up to you.

 

[mingandmong]

Thankyou Gerald Higgins