# 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.  Register To Reply

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))  Register To Reply

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 ?  Register To Reply

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)  Register To Reply

5. ## Re: Max Moving Average Formula REVISON

Bo_Ry Super impressed!! Thank you. you nailed it!  Register To Reply

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?  Register To Reply

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?  Register To Reply

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