Pulling averaged data based on start and end dates in Excel

[amuench16]

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.

[#2 · Accepted solution]

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

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

[#4]

Thank you! That worked perfectly!