Forum Discussion
VERY NEW to EXCEL
How do i create a formula to Average blocks of time?
I input the units for each block of time and i can get the Average after 8 hours,
but i need a running average of each block of time though out the night.
So my AVG. UPH (units per hour)column, when asked, for example the first two column of time equal
50, so my AVG. UPH would be 50/4.5 hours = 11.11.
Hope i explained this right.
Thanks for any help
Dale3158
| 3:30-6:00pm 2.5 Hrs. | 6:00-8:00pm 2 Hrs. | 8:00-10:00pm 2 Hrs. | 10:00-12:00am 2 Hrs | TOTAL | AVG - UPH |
| =SUM(C2:F2) | =SUM(G2)/8 | ||||
| 25 | 25 | 25 | 25 | =SUM(C3:F3) | =SUM(G3)/8 |
- Thank you very much, your help is greatly appreciated.
I will let you know how it turns out.
Dale
12 Replies
I attached an example workbook of what I think you're trying to do. I split the start time, end time, and hours into separate cells, added a formula to compute the hours, and added a formula to compute the UPH.
You could hardcode the hours instead of using a formula, but I would at least put the hours in a separate cell. Also, note that I used a custom number format to add the "Hrs" label - it is not actually part of the cell contents as that would cause an error in the UPH formula.
- One thing, after looking at the formula it's actually 8 hours work day not 8.5, its the time when i input the numbers ,that throw me off, there 2 hour blocks.
Thank you very much.
Is there a way to copy and paste the formula's into my spreadsheet,
Here is a copy of my spreadsheet, I use everyday, columns A,B and I will change from time to time, as my group changes.
Maybe I should have showed the spreadsheet in the first post.
My boss really has a thing for UPH.
3:30 PM 6:00 PM 8:00 PM 10:00 PM 6:00 PM 8:00 PM 10:00 PM 12:00 AM 2.50 Hrs 2.00 Hrs 2.00 Hrs 2.00 Hrs Total UPH ID NUMBER NAME 10 43 25 27 105 12.35 COMMENTS 8836 TORY YOUNG 0 0 0 0 0 8740 ANTHONY SAMEC 41 32 24 26 123 8138 SANDRA ORR 57 23 51 98 229 8978 CLAUDREZ THOMPSON 43 44 25 34 146 8945 KIRK WILSON 46 40 36 44 166 8947 SCOTT BRALLIER 43 46 49 51 189 8980 TAYLOR WALTON 28 21 32 25 106 8930 STEVEN GUEVARA 0 0 0 0 0 8931 JEVELEISKA AVELLANET 19 29 24 27 99 start 4:30 8987 BROOKE CROTHERS 40 52 31 40 163 8988 CANDACE DELLAROVA 9 25 23 27 84 TEO UNTIL 5:00 THEN FO RECV. N/A CLIFFORD WILLIAMS 0 0 0 0 0 TRASH AND CARDBOARD. You just have to change the cell references according to where the data is located in your spreadsheet. Your screenshot does not include row/column labels, but assuming the upper left corner is cell A1, then the formula for UPH in cell H5 would be:
=G5/SUMIF(C5:F5,">0",C$3:F$3)
For the hours in Row 3, you may just want to key in the number of hours instead of computing them in order to exclude the half hour that I assume is lunch.
