Google Sheets – Track Highest Number in a Cell Over Recalculations

google sheetsworksheet-function

How do I keep track of the highest number that was in a cell in Google Sheets?

I'm running random numbers through a cell and I would like to track the highest number reached. Specifically, I am trying to count the number of matches between two random columns. I'd like to keep track of the highest number of matches between sheet refreshes. I have a button that I click to force recalculation on the page.

Best Answer

To keep track of running maximum of the content of some cell, one needs a script that runs every time the cell changes value. Such a script could be triggered by the same button you are using to force recalculation, although a delay (Utilities.sleep(1000), time in milliseconds) may be necessary to make sure the recalculation runs before this script. Or, if recalculation was caused by an edit to the sheet, the function could be simply named onEdit to run on every edit.

In this example, B1 is used to record the maximum of values that were placed in A1.

function recordMax() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A1:B1");
  var values = range.getValues()[0];
  range.setValues([[values[0], Math.max(values[0], values[1])]]);
}
Related Topic