SOLVED

VERY NEW to EXCEL

Occasional Contributor

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
12 Replies

Re: VERY NEW to EXCEL

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.

Re: VERY NEW to EXCEL

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.

Re: VERY NEW to EXCEL

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.

Re: VERY NEW to EXCEL

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.

best response confirmed by Dale3158 (Occasional Contributor)
Solution

Re: VERY NEW to EXCEL

Thank you very much, your help is greatly appreciated.
I will let you know how it turns out.
Dale

Re: VERY NEW to EXCEL

JMB17
Have a question, can I use a IF statement to change the color of a cell?
So, say my Expectation's for my UPH was 25 so that cell would turn green and if lower than 25 turn red.
I have been reading about the IF statement but not sure how to use it to change the color of a cell.
Thanks
Dale

Re: VERY NEW to EXCEL

Look at using conditional formatting. Click on the home tab, conditional formatting split button in the styles group, and select new rule. This will bring up a dialog box. Then, select "format only cells that contain and fill in your criteria and select your formatting:

There is also an option to "use a formula to determine which cells to format" for more complex/custom criteria - that may be to what your reference material is referring.

Thank You, JMB17
I will try it.
Dale3158

Re: VERY NEW to EXCEL

I tried conditional formatting, it worked ok, my problem is, when I have to move someone to a different job, where the UPH change, it didn't seem to work.

Column I is where I'm having problems.

I included my spreadsheet.

Any suggestions

Thanks

Dale

Re: VERY NEW to EXCEL

I assume the conditional formatting should be applied to Column H? I misunderstood earlier that the expectation was not a fixed amount across the board, but was in a separate column. So, a formula would be needed for the conditional format.

First, the values in expectations must be numbers. If you key "23/Hr" into a cell, then excel treats it as text and any text value is considered greater than every numeric value in a conditional statement. So, I input only the numeric values in Column I, but applied a custom number format to display it as "23/Hr" - the "/Hr" part does not exist in the cell data (formula bar), but only on screen as fancy formatting.

Then, I selected H5:H16, clicked conditional formatting, and entered the formula

=H5>=I5

and selected a green fill color (you enter the formula as it applies to the cell in the top left of your selected range, and excel will apply it to the rest of the range).

Then, I added another conditional format using the formula

=H5<I5

and selected a red fill color.

Bear in mind that if you add to your data, you may need to expand your conditional formatting by going into the conditional format dialog and changing the "applies to" range as needed.

Re: VERY NEW to EXCEL

Thank you very much, really appreciate the help.

There is so much to learn in Excel.

Dale

Re: VERY NEW to EXCEL

Indeed. You're quite welcome.