1

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.

  • 1
    Are they really random numbers in the sense that you put =rand() into that cell? Or are they imported from some data source, or entered manually? – user79865 Jun 8 '16 at 23:59
  • Are you tracking the value or the specific cell that the highest number is in? – Dale Jun 9 '16 at 6:17
  • 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. – Nathan S. Jun 9 '16 at 17:27
  • @NathanS.Please edit that information into the question when you have a chance. Thanks! – jonsca Jun 9 '16 at 22:45
1

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])]]);
}
0

With the current relevant value in A1, A2 will retain the all-time highest value of A1 with this formula:

=max(A1,A2)

provided File > Spreadsheet settings..., Calculation has Iterative calculation set to On with Max. number of iterations set to 1.

-1

In a cell of your choosing enter the formula:

=max(A1:A7)

Where A1:A7 are the cells of interest.

A1:A7 interprets as cells A1 through A7, in a column.
A1, A2, C1, C2 interprets each cell is looked at indiviually.
A1:A7, B1, C1:C3 or any combination.

=max(A1:A7, B1, C2, D1:D20)

  • 1
    Correct but irrelevant. – user79865 Jun 9 '16 at 12:29
  • @soup It's the best possible solution. You can't perform a VB to track past data. What solution do you propose? – ejbytes Jun 9 '16 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.