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

mingandmong

Active Member
Joined
Oct 15, 2014
Messages
339
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top