Forum Discussion

amuench16's avatar
amuench16
Copper Contributor
Dec 21, 2022
Solved

Pulling averaged data based on start and end dates in Excel

Hello! I have the start and end date of a product. I have separate data that i need to average for those dates. Basically, I need excel to read the start and end date in one table, pull the corresponding data from those dates, and average it. Thanks in advance.

excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Dec 21, 2022

    amuench16  FIRST you have to convert that _time into a Date-Time which I used:

    =LET(_time,$G$2:$G$473,
           DATEVALUE(LEFT(_time,10))+TIMEVALUE(MID(_time,12,12))+(MID(_time,24,1)&"1")*TIMEVALUE(MID(_time,25,2)&":"&RIGHT(_time,2))
           )

    not sure why excel I couldn't get excel to recognize that UTC date-time stamp format

    THEN I used and AVERAGE(FILTER()) combo:

    =AVERAGE(FILTER($H$2:$S$473,($F$2#>=B2)*($F$2#<=C2),0))

     

3 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Dec 21, 2022

    amuench16 

    First, I'd find and replace "T" with " " in the times column.

    MachineAvg LAMBDA:

    =LAMBDA(row,LET(
    start, TAKE(row, , 1),
    end, TAKE(row, , -1),
    filtered, FILTER(Ext, (Times >= start) * (Times <= end)),
    noZero, FILTER(filtered, filtered <> 0),
    IFERROR(AVERAGE(noZero), "")
))

    Sheet level formula:

    =BYROW(StartEnd,MachineAvg)

     

  • mtarler's avatar
    mtarler
    Silver Contributor
    Dec 21, 2022

    amuench16  FIRST you have to convert that _time into a Date-Time which I used:

    =LET(_time,$G$2:$G$473,
           DATEVALUE(LEFT(_time,10))+TIMEVALUE(MID(_time,12,12))+(MID(_time,24,1)&"1")*TIMEVALUE(MID(_time,25,2)&":"&RIGHT(_time,2))
           )

    not sure why excel I couldn't get excel to recognize that UTC date-time stamp format

    THEN I used and AVERAGE(FILTER()) combo:

    =AVERAGE(FILTER($H$2:$S$473,($F$2#>=B2)*($F$2#<=C2),0))

     

Resources