LinkBack URL
About LinkBacks
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.
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.
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))
and the ranges adjust dynamically to suit your data. see file.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
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 ?
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)
Bo_Ry Super impressed!! Thank you. you nailed it!
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?
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?
Last edited by Flyboy1969; 05-10-2022 at 11:53 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks