I have a list of numbers entered manually:
1
100
100
10
1
1000
10
1000
1
1000
100
10
I'd like to get the running max - the max value for each sub-array of the initial numbers array. By sub-array I mean numbers from [A1]
to [A2]
, from [A1]
to [A3]
, from [A1]
to [A4]
, etc.
The result would be as follows:
1
100
100
100
100
1000
1000
1000
1000
1000
1000
1000
More entries may be added to the initial list of numbers.
Using the bountied answer, here's a speed test with 100500 rows:
This does not include the latest answer
Thanks to @TheMaster for notes. Now the best solution is:
=SCAN(A3,A3:A,LAMBDA(ini,v,if(ini>v,ini,v)))
The ini
tial value is compared against each current v
alue and the max for each iteration is returned by SCAN
.
I've tested it here. It works fast for 100K rows.
Lambda
solution works better because it needs to operate each cell one time. The complexity = the number of cells operated. While the other solutions use formulas that "look back" to previus values.
The scripting solution may also work fine, but native formulas does not waste time on "loading" data into the Sheet. Original tests were done before the Lambda
function appeared in Sheets.