Finding date in which the average for trailing 6 months is >= x

I'm trying to use a single formula that allows me to return the first date where the average of the trailing 6 values is greater than x. The two ranges being used are a horizontal range of dates (month by month) and a horizontal range of values. The difficulty with this formula is that the "trailing 6" criteria requires a different sized range to be used. I've tried using minifs(, averageifs, and offset together but can't get anything to work. Any ideas?

EDIT: Please see attached excel sheet for reference. Sorry for the ambiguity... from a glance it's actually a very simple problem I'm trying to solve for. However, I just can't get the "trailing 6" criteria to work with any functions. FYI I'm trying to this WITHOUT a helper row. Happy to answer questions if this is still too vague.

Too many potentially ambiguous phrases there!



Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

Are you interested in a situation where the first average >70 occurs after 4 months... e.g.

60,60,66,150

This formula assumes thta you are NOT interested in getting that value, as it does not meet the 6 month criterion.

=LET(N,6,A,COLUMNS(B2:R2)-(N-1),B,TRANSPOSE(DROP(B1:R1,,N-1)),C,MAP(SEQUENCE(A),LAMBDA(X,AVERAGE(INDEX(2:2,1+X):INDEX(2:2,N+X)))),D,HSTACK(B,C),TRANSPOSE(TAKE(FILTER(D,INDEX(D,,2)>70),1)))

If you DO want the first average >70, even if it occurs in < 6 months, then this will do that.

Alternative solution for Ex2016 or earlier

Please Login or Register  to view this content.

Originally Posted by Robertcm99

Thank you sir! How would you change this formula to reflect "forward 6" while not including "current" month in the "forward 6"?

If you were talking to me:

=LET(N,6,A,COLUMNS(B2:R2)-(N-1),B,TRANSPOSE(DROP(B1:R1,,N-1)),C,MAP(SEQUENCE(A),LAMBDA(X,AVERAGE(INDE

in V1, and in V2:

=LET(A,B2:R2,B,TRANSPOSE(B1:R1),C,6,D,COLU

change to whatever you want. It returns the average of that number of values (in V2, or fewer if <n are available.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.