SOLVED

VERY NEW to EXCEL

%3CLINGO-SUB%20id%3D%22lingo-sub-2300252%22%20slang%3D%22en-US%22%3EVERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2300252%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20i%20create%20a%20formula%20to%20Average%20blocks%20of%20time%3F%3C%2FP%3E%3CP%3EI%20input%20the%20units%20for%20each%20block%20of%20time%20and%20i%20can%20get%20the%20Average%20after%208%20hours%2C%3C%2FP%3E%3CP%3Ebut%20i%20need%20a%20running%20average%20of%20each%20block%20of%20time%20though%20out%20the%20night.%3C%2FP%3E%3CP%3ESo%20my%20AVG.%20UPH%20(units%20per%20hour)column%2C%20when%20asked%2C%20for%20example%20the%20first%20two%20column%20of%20time%20equal%26nbsp%3B%3C%2FP%3E%3CP%3E50%2C%20so%20my%20AVG.%20UPH%20would%20be%2050%2F4.5%20hours%20%3D%2011.11.%3C%2FP%3E%3CP%3EHope%20i%20explained%20this%20right.%3C%2FP%3E%3CP%3EThanks%20for%20any%20help%3C%2FP%3E%3CP%3EDale3158%3C%2FP%3E%3CTABLE%20width%3D%221320%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22220%22%3E3%3A30-6%3A00pm%26nbsp%3B%202.5%20Hrs.%3C%2FTD%3E%3CTD%20width%3D%22220%22%3E6%3A00-8%3A00pm%202%20Hrs.%3C%2FTD%3E%3CTD%20width%3D%22220%22%3E8%3A00-10%3A00pm%202%20Hrs.%3C%2FTD%3E%3CTD%20width%3D%22220%22%3E10%3A00-12%3A00am%202%20Hrs%3C%2FTD%3E%3CTD%20width%3D%22220%22%3ETOTAL%3C%2FTD%3E%3CTD%20width%3D%22220%22%3EAVG%20-%20UPH%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3DSUM(C2%3AF2)%3C%2FTD%3E%3CTD%3E%3DSUM(G2)%2F8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E%3DSUM(C3%3AF3)%3C%2FTD%3E%3CTD%3E%3DSUM(G3)%2F8%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2300252%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2300590%22%20slang%3D%22en-US%22%3ERe%3A%20VERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2300590%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1038993%22%20target%3D%22_blank%22%3E%40Dale3158%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20an%20example%20workbook%20of%20what%20I%20think%20you're%20trying%20to%20do.%20I%20split%20the%20start%20time%2C%20end%20time%2C%20and%20hours%20into%20separate%20cells%2C%20added%20a%20formula%20to%20compute%20the%20hours%2C%20and%20added%20a%20formula%20to%20compute%20the%20UPH.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20hardcode%26nbsp%3B%20the%20hours%20instead%20of%20using%20a%20formula%2C%20but%20I%20would%20at%20least%20put%20the%20hours%20in%20a%20separate%20cell.%20Also%2C%20note%20that%20I%20used%20a%20custom%20number%20format%20to%20add%20the%20%22Hrs%22%20label%20-%20it%20is%20not%20actually%20part%20of%20the%20cell%20contents%20as%20that%20would%20cause%26nbsp%3B%20an%20error%20in%20the%20UPH%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2304807%22%20slang%3D%22en-US%22%3ERe%3A%20VERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2304807%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much.%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20copy%20and%20paste%20the%20formula's%20into%20my%20spreadsheet%2C%3C%2FP%3E%3CP%3EHere%20is%20a%20copy%20of%20my%20spreadsheet%2C%20I%20use%20everyday%2C%20columns%20A%2CB%20and%20I%20will%20change%20from%20time%20to%20time%2C%20as%20my%20group%20changes.%3C%2FP%3E%3CP%3EMaybe%20I%20should%20have%20showed%20the%20spreadsheet%20in%20the%20first%20post.%3C%2FP%3E%3CP%3EMy%20boss%20really%20has%20a%20thing%20for%20UPH.%3C%2FP%3E%3CTABLE%20width%3D%221504%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22145%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22217%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22145%22%3E3%3A30%20PM%3C%2FTD%3E%3CTD%20width%3D%22145%22%3E6%3A00%20PM%3C%2FTD%3E%3CTD%20width%3D%22145%22%3E8%3A00%20PM%3C%2FTD%3E%3CTD%20width%3D%22145%22%3E10%3A00%20PM%3C%2FTD%3E%3CTD%20width%3D%22145%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22145%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22272%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E6%3A00%20PM%3C%2FTD%3E%3CTD%3E8%3A00%20PM%3C%2FTD%3E%3CTD%3E10%3A00%20PM%3C%2FTD%3E%3CTD%3E12%3A00%20AM%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2.50%20Hrs%3C%2FTD%3E%3CTD%3E2.00%20Hrs%3C%2FTD%3E%3CTD%3E2.00%20Hrs%3C%2FTD%3E%3CTD%3E2.00%20Hrs%3C%2FTD%3E%3CTD%3ETotal%3C%2FTD%3E%3CTD%3EUPH%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EID%20NUMBER%3C%2FTD%3E%3CTD%3ENAME%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E43%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E27%3C%2FTD%3E%3CTD%3E105%3C%2FTD%3E%3CTD%3E12.35%3C%2FTD%3E%3CTD%3ECOMMENTS%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8836%3C%2FTD%3E%3CTD%3ETORY%20YOUNG%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8740%3C%2FTD%3E%3CTD%3EANTHONY%20SAMEC%3C%2FTD%3E%3CTD%3E41%3C%2FTD%3E%3CTD%3E32%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3CTD%3E26%3C%2FTD%3E%3CTD%3E123%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8138%3C%2FTD%3E%3CTD%3ESANDRA%20ORR%3C%2FTD%3E%3CTD%3E57%3C%2FTD%3E%3CTD%3E23%3C%2FTD%3E%3CTD%3E51%3C%2FTD%3E%3CTD%3E98%3C%2FTD%3E%3CTD%3E229%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8978%3C%2FTD%3E%3CTD%3ECLAUDREZ%20THOMPSON%3C%2FTD%3E%3CTD%3E43%3C%2FTD%3E%3CTD%3E44%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E34%3C%2FTD%3E%3CTD%3E146%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8945%3C%2FTD%3E%3CTD%3EKIRK%20WILSON%3C%2FTD%3E%3CTD%3E46%3C%2FTD%3E%3CTD%3E40%3C%2FTD%3E%3CTD%3E36%3C%2FTD%3E%3CTD%3E44%3C%2FTD%3E%3CTD%3E166%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8947%3C%2FTD%3E%3CTD%3ESCOTT%20BRALLIER%3C%2FTD%3E%3CTD%3E43%3C%2FTD%3E%3CTD%3E46%3C%2FTD%3E%3CTD%3E49%3C%2FTD%3E%3CTD%3E51%3C%2FTD%3E%3CTD%3E189%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8980%3C%2FTD%3E%3CTD%3ETAYLOR%20WALTON%3C%2FTD%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E21%3C%2FTD%3E%3CTD%3E32%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E106%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8930%3C%2FTD%3E%3CTD%3ESTEVEN%20GUEVARA%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8931%3C%2FTD%3E%3CTD%3EJEVELEISKA%20AVELLANET%3C%2FTD%3E%3CTD%3E19%3C%2FTD%3E%3CTD%3E29%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3CTD%3E27%3C%2FTD%3E%3CTD%3E99%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Estart%204%3A30%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8987%3C%2FTD%3E%3CTD%3EBROOKE%20CROTHERS%3C%2FTD%3E%3CTD%3E40%3C%2FTD%3E%3CTD%3E52%3C%2FTD%3E%3CTD%3E31%3C%2FTD%3E%3CTD%3E40%3C%2FTD%3E%3CTD%3E163%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8988%3C%2FTD%3E%3CTD%3ECANDACE%20DELLAROVA%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E23%3C%2FTD%3E%3CTD%3E27%3C%2FTD%3E%3CTD%3E84%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3BTEO%20UNTIL%205%3A00%20THEN%20FO%20RECV.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EN%2FA%3C%2FTD%3E%3CTD%3ECLIFFORD%20WILLIAMS%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ETRASH%20AND%20CARDBOARD.%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2305119%22%20slang%3D%22en-US%22%3ERe%3A%20VERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2305119%22%20slang%3D%22en-US%22%3EOne%20thing%2C%20after%20looking%20at%20the%20formula%20it's%20actually%208%20hours%20work%20day%20not%208.5%2C%20its%20the%20time%20when%20i%20input%20the%20numbers%20%2Cthat%20throw%20me%20off%2C%20there%202%20hour%20blocks.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2307863%22%20slang%3D%22en-US%22%3ERe%3A%20VERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2307863%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1038993%22%20target%3D%22_blank%22%3E%40Dale3158%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20just%20have%20to%20change%20the%20cell%20references%20according%20to%20where%20the%20data%20is%20located%20in%20your%20spreadsheet.%20Your%20screenshot%20does%20not%20include%20row%2Fcolumn%20labels%2C%20but%20assuming%20the%20upper%20left%20corner%20is%20cell%20A1%2C%20then%20the%20formula%20for%20UPH%26nbsp%3B%20in%20cell%20H5%20would%20be%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DG5%2FSUMIF(C5%3AF5%2C%22%26gt%3B0%22%2CC%243%3AF%243)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20hours%20in%20Row%203%2C%20you%20may%20just%20want%20to%20key%20in%20the%20number%20of%20hours%20instead%20of%20computing%20them%20in%20order%20to%20exclude%20the%20half%20hour%20that%20I%20assume%20is%20lunch.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2308818%22%20slang%3D%22en-US%22%3ERe%3A%20VERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2308818%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much%2C%20your%20help%20is%20greatly%20appreciated.%3CBR%20%2F%3EI%20will%20let%20you%20know%20how%20it%20turns%20out.%3CBR%20%2F%3EDale%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2319950%22%20slang%3D%22en-US%22%3ERe%3A%20VERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2319950%22%20slang%3D%22en-US%22%3EJMB17%3CBR%20%2F%3EHave%20a%20question%2C%20can%20I%20use%20a%20IF%20statement%20to%20change%20the%20color%20of%20a%20cell%3F%3CBR%20%2F%3ESo%2C%20say%20my%20Expectation's%20for%20my%20UPH%20was%2025%20so%20that%20cell%20would%20turn%20green%20and%20if%20lower%20than%2025%20turn%20red.%3CBR%20%2F%3EI%20have%20been%20reading%20about%20the%20IF%20statement%20but%20not%20sure%20how%20to%20use%20it%20to%20change%20the%20color%20of%20a%20cell.%3CBR%20%2F%3EThanks%3CBR%20%2F%3EDale%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2320396%22%20slang%3D%22en-US%22%3ERe%3A%20VERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2320396%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1038993%22%20target%3D%22_blank%22%3E%40Dale3158%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELook%20at%20using%20conditional%20formatting.%20Click%20on%20the%20home%20tab%2C%20conditional%20formatting%20split%20button%20in%20the%20styles%20group%2C%20and%20select%20new%20rule.%20This%20will%20bring%20up%20a%20dialog%20box.%20Then%2C%20select%20%22format%20only%20cells%20that%20contain%20and%20fill%20in%20your%20criteria%20and%20select%20your%20formatting%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JMB17_0-1620106152442.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277755iF1D1F99DEC18BABF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JMB17_0-1620106152442.png%22%20alt%3D%22JMB17_0-1620106152442.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20also%20an%20option%20to%20%22use%20a%20formula%20to%20determine%20which%20cells%20to%20format%22%20for%20more%20complex%2Fcustom%20criteria%20-%20that%20may%20be%20to%20what%20your%20reference%20material%20is%20referring.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2333131%22%20slang%3D%22en-US%22%3ERe%3A%20VERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2333131%22%20slang%3D%22en-US%22%3EThank%20You%2C%20JMB17%3CBR%20%2F%3EI%20will%20try%20it.%3CBR%20%2F%3EDale3158%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2353483%22%20slang%3D%22en-US%22%3ERe%3A%20VERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2353483%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20conditional%20formatting%2C%20it%20worked%20ok%2C%20my%20problem%20is%2C%20when%20I%20have%20to%20move%20someone%20to%20a%20different%20job%2C%20where%20the%20UPH%20change%2C%20it%20didn't%20seem%20to%20work.%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20I%20is%20where%20I'm%20having%20problems.%3C%2FP%3E%3CP%3EI%20included%20my%20spreadsheet.%3C%2FP%3E%3CP%3EAny%20suggestions%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EDale%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2356630%22%20slang%3D%22en-US%22%3ERe%3A%20VERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2356630%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1038993%22%20target%3D%22_blank%22%3E%40Dale3158%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20assume%20the%20conditional%20formatting%20should%20be%20applied%20to%20Column%20H%3F%20I%20misunderstood%20earlier%20that%20the%20expectation%20was%20not%20a%20fixed%20amount%20across%20the%20board%2C%20but%20was%20in%20a%20separate%20column.%20So%2C%20a%20formula%20would%20be%20needed%20for%20the%20conditional%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20the%20values%20in%20expectations%20must%20be%20numbers.%20If%20you%20key%20%2223%2FHr%22%20into%20a%20cell%2C%20then%20excel%20treats%20it%20as%20text%20and%20any%20text%20value%20is%20considered%20greater%20than%20every%20numeric%20value%20in%20a%20conditional%20statement.%20So%2C%20I%20input%20only%20the%20numeric%20values%20in%20Column%20I%2C%20but%20applied%20a%20custom%20number%20format%20to%20display%20it%20as%20%2223%2FHr%22%20-%20the%20%22%2FHr%22%20part%20does%20not%20exist%20in%20the%20cell%20data%20(formula%20bar)%2C%20but%20only%20on%20screen%20as%20fancy%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20I%20selected%20H5%3AH16%2C%20clicked%20conditional%20formatting%2C%20and%20entered%20the%20formula%3C%2FP%3E%3CP%3E%3DH5%26gt%3B%3DI5%3C%2FP%3E%3CP%3Eand%20selected%20a%20green%20fill%20color%20(you%20enter%20the%20formula%20as%20it%20applies%20to%20the%20cell%20in%20the%20top%20left%20of%20your%20selected%20range%2C%20and%20excel%20will%20apply%20it%20to%20the%20rest%20of%20the%20range).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20I%20added%20another%20conditional%20format%20using%20the%20formula%3C%2FP%3E%3CP%3E%3DH5%3CI5%3E%3C%2FI5%3E%3C%2FP%3E%3CP%3Eand%20selected%20a%20red%20fill%20color.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBear%20in%20mind%20that%20if%20you%20add%20to%20your%20data%2C%20you%20may%20need%20to%20expand%20your%20conditional%20formatting%20by%20going%20into%20the%20conditional%20format%20dialog%20and%20changing%20the%20%22applies%20to%22%20range%20as%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2370577%22%20slang%3D%22en-US%22%3ERe%3A%20VERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2370577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%2C%20really%20appreciate%20the%20help.%3C%2FP%3E%3CP%3EThere%20is%20so%20much%20to%20learn%20in%20Excel.%3C%2FP%3E%3CP%3EDale%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2370578%22%20slang%3D%22en-US%22%3ERe%3A%20VERY%20NEW%20to%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2370578%22%20slang%3D%22en-US%22%3EIndeed.%20You're%20quite%20welcome.%3C%2FLINGO-BODY%3E
Occasional Contributor

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 HrsTOTALAVG - UPH
    =SUM(C2:F2)=SUM(G2)/8
25252525=SUM(C3:F3)=SUM(G3)/8
12 Replies

@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.

 

@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 PM6:00 PM8:00 PM10:00 PM   
  6:00 PM8:00 PM10:00 PM12:00 AM   
  2.50 Hrs2.00 Hrs2.00 Hrs2.00 HrsTotalUPH 
ID NUMBERNAME1043252710512.35COMMENTS
8836TORY YOUNG00000  
8740ANTHONY SAMEC41322426123  
8138SANDRA ORR57235198229  
8978CLAUDREZ THOMPSON43442534146  
8945KIRK WILSON46403644166  
8947SCOTT BRALLIER43464951189  
8980TAYLOR WALTON28213225106  
8930STEVEN GUEVARA00000  
8931JEVELEISKA AVELLANET1929242799 start 4:30                                                    
8987BROOKE CROTHERS40523140163  
8988CANDACE DELLAROVA925232784  TEO UNTIL 5:00 THEN FO RECV.         
N/ACLIFFORD WILLIAMS00000 TRASH AND CARDBOARD.
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.

@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.

 

best response confirmed by Dale3158 (Occasional Contributor)
Solution
Thank you very much, your help is greatly appreciated.
I will let you know how it turns out.
Dale
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

@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:

 

JMB17_0-1620106152442.png

 

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

@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

 

@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.

 

 

@JMB17 

Thank you very much, really appreciate the help.

There is so much to learn in Excel.

Dale

Indeed. You're quite welcome.