Forum Discussion

cowboy915's avatar
cowboy915
Copper Contributor
Sep 14, 2022

Smoothing Data

I will try and explain what I'm trying to accomplish in the best way possible. Assuming there are millions of rows of data, I need to use formulas to accomplish the following:   The values in Colu...
excel
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Sep 14, 2022

cowboy915  i created a formula but it isn't "pretty"

 

=LET(in,A1:A33,
     n,ROWS(in),
     zeros,ABS(SCAN(0,in,LAMBDA(p,i,IF(i,IF(p>0,-p-1,0),p+1)))),
     revIN,INDEX(in,SEQUENCE(n,1,n,-1)),
     revZ,INDEX(zeros,SEQUENCE(n,1,n,-1)),
     revZhold,LET(a,SCAN(0,revZ,LAMBDA(p,I,IF(p*I,p,I))),IF(a,a,1)),
     revINhold,SCAN(0,revIN,LAMBDA(p,i,IF(i,i,p))),
     revOUT,revINhold/revZhold,
    INDEX(revOUT,SEQUENCE(n,1,n,-1)))

 

maybe someone else could do it easier.  i had to scan arrays and 'reverse' arrays multiple times.  but it seems to work at least

Resources