So I have this table:

I added 1.5 in B11 and 5 in B12 manually but what formula should I type to get the average for before or equal and after a given date?

CodePudding user response：

The way you currently have it set up, you can use INDEX/MATCH to return the row you're looking for, supply that to AVERAGEIFS, and match the date against the first row:

=averageifs(index(A1:E4, match(B9, A1:A4, 0)), A1:E1, "<=" &B10)
=averageifs(index(A1:E4, match(B9, A1:A4, 0)), A1:E1, ">" &B10)

See this demo on Google Sheets, but the formulas should be the same for Excel: