VERY NEW to EXCEL

[Dale3158]

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

[#2]

@Dale3158 

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.

[#3]

@JMB17 

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.

[#4]

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.

[#5]

@Dale3158 

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.

[#6 · Accepted solution]

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

[#7]

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

[#8]

@Dale3158 

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.

[#9]

Thank You, JMB17
I will try it.
Dale3158

[#10]

@JMB17 

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

[#11]

@Dale3158 

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.

[#12]

@JMB17 

Thank you very much, really appreciate the help.

There is so much to learn in Excel.

Dale

[#13]

Indeed. You're quite welcome.