Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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).

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

The only way to accomplish that, is through a script, Google Apps Script that is.

Script

function 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);
  }
}

Trigger

enter image description here

Explained

The 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-to

Add 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.

Example

I've created an example file for you: get max value from formula

Second Best

=MAX(INDEX(GOOGLEFINANCE("GOOG", "HIGH", "01-01-2011", TEXT(NOW(), "dd-MM-yyyy"), "1"),"", 2))

Explained

This 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.

share|improve this answer
1  
I got it working with the Google Apps Script method that you describe above. I hooked it to the specific spreadsheet that I needed, and after waiting for a bit, I saw it updating the value. –  127961409810234 Nov 28 '13 at 11:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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