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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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:

mingandmong

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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,046
Messages
5,466,243
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top