Running Max ArrayFormula for Google Sheets

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.

Bounty

100 points of bounty were given to this answer.

Here's a speed test with 100500 rows:

Solution 1:

how about:

=INDEX(VLOOKUP(ROW(A:A), FILTER(
 SORTN({ROW(A:A), A:A}, 9^9, 2, 2, 1), IFNA(SORTN(ROW(A:A), 9^9, 2, A:A, 1)<
 QUERY(SORTN(ROW(A:A), 9^9, 2, A:A, 1), "offset 1", ), 1)), 2, 1))

this will not work... see update bellow

UPDATE:

=INDEX(VLOOKUP(ROW(A:A), FILTER(SORT(SORTN({ROW(A:A), A:A}, 9^9, 2, 2, 1)), 
 COUNTIFS(UNIQUE(A:A), ">"&UNIQUE(A:A), 
 SEQUENCE(COUNTUNIQUE(A:A)+1), "<="&SEQUENCE(COUNTUNIQUE(A:A)+1))=0), 2, 1))

and to account for empty rows:

=INDEX(IF(A:A="",,VLOOKUP(ROW(A:A), 
 FILTER(SORT(SORTN(FILTER({ROW(A:A), A:A}, A:A<>""), 9^9, 2, 2, 1)), 
 COUNTIFS(UNIQUE(FILTER(A:A, A:A<>"")), ">"&UNIQUE(FILTER(A:A, A:A<>"")), 
 SEQUENCE(COUNTUNIQUE(A:A)), "<="&SEQUENCE(COUNTUNIQUE(A:A)))=0), 2, 1)))

Solution 2:

custom formula sample:

=INDEX(IF(A3:A="","",
  runningTotal(A3:A,4)))

sample file

source code

related

code:

/**
 * Get running total for the array of numbers
 * by [email protected]
 * 
 * @param {array} numbers The array of numbers
 * @param {number} total_types (1-dafault) sum, (2) avg, (3) min, (4) max, (5) count;
 *                  1-d array or number
 * @param {number} limit number of last values to count next time. 
 *                 Set to 0 (defualt) to take all values
 * @param {array} keys (optional) array of keys. Function will group result by keys
 * @return The hex-code of cell background & font color
 * @customfunction
 */
function runningTotal(numbers, total_types, limit, keys) { 
  
  // possible types to return
  var oTypes = {
    '1': 'sum',
    '2': 'avg',
    '3': 'min',
    '4': 'max',
    '5': 'count'
  }
  // checks and defaults
  var errPre = '🥴 ';
  if( typeof numbers != "object" ) {
    numbers = [ [numbers] ];
  }
  total_types = total_types || [1];
  if( typeof total_types != "object" ) {
    total_types = [ total_types ];
  }
  if( keys && typeof keys != "object" ) {
    keys = [ [keys] ];
  }
  if (keys) {
    if (numbers.length !== keys.length) {
      throw errPre + 'Numbers(' + 
        numbers.length + 
        ') and keys(' + 
        keys.length + 
        ') are of different length'; }
  }
  // assign types
  var types = [], type, k;
  for (var i = 0; i < total_types.length; i++) {
    k = '' + total_types[i];
    type = oTypes[k];
    if (!type) {
      throw errPre + 'Unknown total_type = ' + k;
    }
    types.push(type);
  }
  limit = limit || 0;
  if (isNaN(limit)) {
    throw errPre + '`limit` is not a Number!';
  }
  limit = parseInt(limit);

  // calculating running totals
  var result = [], 
    subres = [], 
    nodes = {}, 
    key = '-', 
    val;
  var defaultNode_ = {
      values: [],
      count: 0,
      sum: 0,
      max: null,
      min: null,
      avg: null,
      maxA: Number.MIN_VALUE,
      maxB: Number.MIN_VALUE,
      maxC: Number.MIN_VALUE,
      minA: Number.MAX_VALUE,
      minB: Number.MAX_VALUE,
      minC: Number.MAX_VALUE
    };
  for (var i = 0; i < numbers.length; i++) {
    val = numbers[i][0];
    // find correct node
    if (keys) { key = keys[i][0]; }
    node = nodes[key] || 
      JSON.parse(JSON.stringify(defaultNode_));
    /**
     * For findig running Max/Min
     * sourse of algorithm
     * https://www.geeksforgeeks.org
     * /sliding-window-maximum-maximum-of-all-subarrays-of-size-k/
     */
    // max
    //reset first second and third largest elements
    //in response to new incoming elements
    if (node.maxA<val) {
      node.maxC = node.maxB;
      node.maxB = node.maxA;
      node.maxA = val;
    } else if (node.maxB<val) {
      node.maxC = node.maxB;
      node.maxB = val;
    } else if (node.maxC<val) {
      node.maxC = val;
    }
    // min
    if (node.minA>val) {
      node.minC = node.minB;
      node.minB = node.minA;
      node.minA = val;
    } else if (node.minB>val) {
      node.minC = node.minB;
      node.minB = val;
    } else if (node.minC>val) {
      node.minC = val;
    }

    // if limit exceeds
    if (limit !== 0 && node.count === limit) {
      //if the first biggest we earlier found
      //is matching from the element that
      //needs to be removed from the subarray
      if(node.values[0]==node.maxA) {
        //reset first biggest to second and second to third
        node.maxA = node.maxB;
        node.maxB = node.maxC;
        node.maxC = Number.MIN_VALUE;
        if (val <= node.maxB) {
          node.maxC = val;
        }
      } else if (node.values[0]==node.maxB) {
        node.maxB = node.maxC;
        node.maxC = Number.MIN_VALUE;
        if (val <= node.maxB) {
          node.maxC = val;
        }
      } else if (node.values[0]==node.maxC) {
        node.maxC = Number.MIN_VALUE;
        if (val <= node.maxB) {
          node.maxC = val;
        }
      } else if(node.values[0]==node.minA) {
        //reset first smallest to second and second to third
        node.minA = node.minB;
        node.minB = node.minC;
        node.minC = Number.MAX_VALUE;
        if (val > node.minB) {
          node.minC = val;
        }
      }
      if (node.values[0]==node.minB) {
        node.minB = node.minC;
        node.minC = Number.MAX_VALUE;
        if (val > node.minB) {
          node.minC = val;
        }
      } 
      if (node.values[0]==node.minC) {
        node.minC = Number.MAX_VALUE;
        if (val > node.minB) {
          node.minC = val;
        }
      }
      // sum
      node.sum -= node.values[0];
      // delete first value
      node.values.shift();
      // start new counter
      node.count = limit-1; 
    }
    // add new values
    node.count++;
    node.values.push(val);
    node.sum += val;
    node.avg = node.sum/node.count;
    node.max = node.maxA;
    node.min = node.minA;
    // remember entered values for the next loop
    nodes[key] = node;

    // get the result depending on 
    // selected total_types
    subres = [];
    for (var t = 0; t < types.length; t++) {
      subres.push(node[types[t]]);
    }
    result.push(subres);
  }
  // console.log(JSON.stringify(nodes, null, 4));
  return result;
}

Other solutions:

• DMAX arrayformula

Solution 3:

=INDEX(QUERY(SPLIT(FLATTEN(ROW(A1:A12)&"×"&
 IF(ROW(A1:A12)>=TRANSPOSE(ROW(A1:A12)), TRANSPOSE(A1:A12), )), "×"), 
 "select max(Col2) group by Col1 label max(Col2)''"))

but to keep it fast:

=INDEX(QUERY(SPLIT(FLATTEN(
 SEQUENCE(MATCH(9, 1/(A:A<>"")))&"×"&IF(
 SEQUENCE(MATCH(9, 1/(A:A<>"")))>=
 SEQUENCE(1, MATCH(9, 1/(A:A<>""))), TRANSPOSE(
 INDIRECT("A1:A"&MATCH(9, 1/(A:A<>"")))), )), "×"), 
 "select max(Col2) group by Col1 label max(Col2)''"))

and to account for empty rows:

=INDEX(IF(A:A="",, QUERY(SPLIT(FLATTEN(
 SEQUENCE(MATCH(9, 1/(A:A<>"")))&"×"&IF(
 SEQUENCE(MATCH(9, 1/(A:A<>"")))>=
 SEQUENCE(1, MATCH(9, 1/(A:A<>""))), TRANSPOSE(
 INDIRECT("A1:A"&MATCH(9, 1/(A:A<>"")))), )), "×"), 
 "select max(Col2) group by Col1 label max(Col2)''")))

and yes, it works with MIN, SUM, AVG and COUNT

ofc COUNT would be massive overkill tho

Solution 4:

Can I put this one into the running, @Max?

=ArrayFormula(if(A:A="",,vlookup(row(A:A),
{if(countifs(A1:A,">"&A:A,row(A:A),"<"&row(A:A))=0,row(A:A)),A:A}
,2)))

You can use this if there are intervening blank cells:

 =ArrayFormula(if(A:A="",,vlookup(row(A:A),
{if((countifs(A1:A,">"&A:A,row(A:A),"<"&row(A:A))=0)*(A:A<>""),row(A:A)),A:A}
,2)))

The original formula (if there are no blanks between non-blanks) can be simplified to

=ArrayFormula(vlookup(row(A:A),
{if(countifs(A1:A,">"&A:A,row(A:A),"<"&row(A:A))=0,row(A:A)),A:A}
,2))