SOLVED

# Pulling averaged data based on start and end dates in Excel

Copper Contributor

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

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Pulling averaged data based on start and end dates in Excel

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

# Re: Pulling averaged data based on start and end dates in Excel

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

# Re: Pulling averaged data based on start and end dates in Excel

Thank you! That worked perfectly!
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Pulling averaged data based on start and end dates in Excel

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