I’m working with a Google Sheets document where one of my cells keeps updating with new values throughout the day. The problem is that I want to keep track of what the highest and lowest values were, but since the cell data changes automatically, I lose the previous numbers. For example, let’s say cell B5 shows live stock prices that update every few minutes. I need to capture and store the peak price and the lowest price somewhere else in my spreadsheet, even after the original cell value changes again. Is there a way to use built-in functions or formulas to automatically record these extreme values? I’ve tried basic MAX and MIN functions but they don’t seem to work with this type of shifting data. Any suggestions would be really helpful!
The tricky part is that regular formulas only look at current values, not what happened before. I solved this with a Google Apps Script that runs whenever the cell changes. Just write a simple script that checks if the new value beats your stored max/min in other cells, then updates them only when it finds a new record. The script runs automatically and keeps tracking your highs and lows even when your source data keeps changing. Takes a bit of coding upfront, but once it’s running it’s perfect for catching those peaks and valleys.
Try an onChange trigger in Google Apps Script - it’ll watch your cell and fire whenever the value updates. The script checks if the new value beats your current max/min (stored in separate cells) and only updates when you hit a new extreme. I’ve done this with temperature sensors that update constantly and it works great once you get it running. Just make sure the script writes to dedicated max/min cells so they don’t get wiped when your source data changes. You’ll need basic JavaScript but tons of templates exist to copy from.
totally get that, harry! google sheets isn’t great with live updates. you might wanna look into using a script or maybe even a third-party add-on to keep track of those high and low values since the built-in stuff won’t do it for ya.