CODEWITHSUNDEEP
google-sheetsformulas
Krystian
Krystian

Reputation: 987

average of last x cells for dates < today()

We are tracking our orders in a google sheet. This sheet shows also the forecast of the products. Now we would like to get the average number of orders of the last 15 days. So the function must be sth like this: =average(last 15 cells for dates before today)

enter image description here

I got this but its only working, if the forecast wouldnt be there: 

=AVERAGE(INDEX(L3:ACV3;COUNTA(L3:ACV3)-14):ACV3)

Upvotes: 1

Views: 63

Answers (1)

user4039065
user4039065

Reputation:

Try,

=average(index(3:3, 1, match(today(), 2:2, 0)-14):index(3:3, 1, match(today(), 2:2, 0)))
=average(index(3:3; 1; match(today(); 2:2; 0)-14):index(3:3; 1; match(today(); 2:2; 0)))

Upvotes: 1

Related Questions