I would like cell A to have the max value that another cell B has ever had. How can I do that? Cell B changes value dynamically according to a =GoogleFinance() formula (every few seconds or so).
How to update cell value to max if value is smaller than other cell in Google Spreadsheets
google spreadsheets
The only way to accomplish that, is through a script, Google Apps Script that is.Scriptfunction getMax() {  var sh = SpreadsheetApp.getActiveSheet();  var cValue = sh.getRange(C2).getValue();  var hRange = sh.getRange(B3), hValue = hRange.getValue();  if(cValue > hValue) {    hRange.setValue(cValue);  }}TriggerExplainedThe script is retrieving the two values, at 1 minute intervals, and compares them. If the real value if higher than the highest value, then the highest will be replaced by the real' value.How-toAdd the script under Tools>Script editor. Press the bug button to authenticate the script (as it need access to the Google Spreadsheet). Select Resources\Current project's triggers in the script editor and set it to the screenshot given. ExampleI've created an example file for you: get max value from formulaSecond Best=MAX(INDEX(GOOGLEFINANCE(GOOG, HIGH, 01-01-2011, TEXT(NOW(), dd-MM-yyyy), 1),, 2))ExplainedThis formula will show all the highest day values of the Google share, from the beginning of 2011. The INDEX formula will show only the second column and the MAX formula will show only the highest value. 