CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulascumulative-sum
LawnBob
LawnBob

Reputation: 1

Running Max in ArrayFormula of a Query

I am generating a set of data in Google Sheets with a query and then want to get a running max of that data:

My query is as follows:

=query(GOOGLEFINANCE("SPX","price",TODAY()-365,TODAY()),"Select Col2 offset 1",0)

It generates a single column of data, and I want to return a single column of data which is just the running max.

I could easily do this outside of a query with MAX(A$1:A1) and drag that down, but don't know how to reference the individual rows of a query. Please help!

Upvotes: 0

Views: 72

Answers (3)

LawnBob
LawnBob

Reputation: 1

Thanks, all. I was able to get the following to work well:

=SCAN(0,
      query(GOOGLEFINANCE("SPX","price",TODAY()-365,TODAY()),
        "Select Col2 offset 1",0),
      LAMBDA(currmax,value,MAX(currmax,value)))

Credit to https://support.google.com/profile/24340643...

Upvotes: 0

z..
z..

Reputation: 13056

You can also do this using SCAN:

=SCAN(-9^99,
   QUERY(
     GOOGLEFINANCE("SPX","price",TODAY()-365,TODAY()),
     "Select Col2 offset 1",0),
   LAMBDA(a,c,MAX(a,c)))

Upvotes: 1

rockinfreakshow
rockinfreakshow

Reputation: 30281

Here's one approach you may test out:

=let(Σ,query(googlefinance("SPX","price",today()-365,today()),"Select Col2 offset 1",0),
 reduce(tocol(,1),sequence(rows(Σ)),lambda(a,c,vstack(a,max(a,chooserows(Σ,c))))))

enter image description here

Upvotes: -1

Related Questions