Pulling averaged data based on start and end dates in Excel

Occasional Contributor

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.

3 Replies
best response confirmed by Hans Vogelaar (MVP)

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


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

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




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

MachineAvg LAMBDA:

    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:



Thank you! That worked perfectly!