Forum Discussion

Dale3158's avatar
Dale3158
Copper Contributor
Apr 28, 2021
Solved

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 ...
Formulas and Functions
  • Dale3158's avatar
    Dale3158
    Apr 29, 2021
    Thank you very much, your help is greatly appreciated.
    I will let you know how it turns out.
    Dale
JMB17's avatar
JMB17
Bronze Contributor
Apr 28, 2021

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.

 

  • Dale3158's avatar
    Dale3158
    Copper Contributor
    Apr 29, 2021
    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's avatar
    Dale3158
    Copper Contributor
    Apr 28, 2021

    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.
    • JMB17's avatar
      JMB17
      Bronze Contributor
      Apr 29, 2021

      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.

       

      • Dale3158's avatar
        Dale3158
        Copper Contributor
        Apr 29, 2021
        Thank you very much, your help is greatly appreciated.
        I will let you know how it turns out.
        Dale

Resources