# Max Moving Average Formula REVISON

1. ## Max Moving Average Formula REVISON

I need to revise the Max Moving average Formulas I have created to allow for an undefined length of the data set and the ability to change the offset (moving average window) of the data.
Current data set is only 323 rows.

2. ## Re: Max Moving Average Formula REVISON

Set up 3 Named ranges (ColF, ColG, ColH... CTRL-F3 to view edit) along this pattern:

=\$H\$3:INDEX(\$H:\$H,MATCH(10^100,\$H:\$H))

The formulae then change to look like this:

=INDEX(ColF,MATCH(MAX(SUBTOTAL(1,OFFSET(G2,ROW(ColG)-ROW(G2),,C2))),SUBTOTAL(1,OFFSET(G2:G3,ROW(ColG)-ROW(G3),,C2)),0))

3. ## Re: Max Moving Average Formula REVISON

Glenn... you are a Rockstar thank you!

Last question... for: Lowest Moving Average of C (EXCLUDING 0's) how do I exclude ZERO's from this? Meaning... Can the formula look only for a consistent data set where there are NO ZEROS in the data that match the offset ?

4. ## Re: Max Moving Average Formula REVISON

Maybe try

=LET(z,H2:H999,n,C4,c,INDEX(z,SEQUENCE(n)+SEQUENCE(,COUNT(z)-n,0)),s,IF(MMULT(SEQUENCE(,n,,0),--(c>0))=n,MMULT(SEQUENCE(,n,,0),c)),MIN(s)/n)

5. ## Re: Max Moving Average Formula REVISON

Bo_Ry Super impressed!! Thank you. you nailed it!

6. ## Re: Max Moving Average Formula REVISON

now Im confused... glen fixed the unlimited row problem with =MIN(SUBTOTAL(1,OFFSET(H2:H3,ROW(ColH)-ROW(H3),,C4))) how do I combine that with your >0 solution?

7. ## Re: Max Moving Average Formula REVISON

Glen fixed the unlimited row problem with =MIN(SUBTOTAL(1,OFFSET(H2:H3,ROW(ColH)-ROW(H3),,C4))) how do I combine that with your >0 solution?
Bo_Ry fixed the EXCLUDE 0 issue with =LET(z,H2:H999,n,C8,c,INDEX(z,SEQUENCE(n)+SEQUENCE(,COUNT(z)-n,0)),s,IF(MMULT(SEQUENCE(,n,,0),--(c>0))=n,MMULT(SEQUENCE(,n,,0),c)),MIN(s)/n)

Thank you both for your contributions!

Now I am stuck with two independent solutions that have to be merged together? suggestions?

There are currently 1 users browsing this thread. (0 members and 1 guests)