I have a cell that references the total value of a stock portfolio. I want to figure out how I can let Google Sheets (over a period of time, say a month) automatically tell me the highest number and also separately the lowest number reached in that specific cell. In other words, I want to find the monthly high of my stock portfolio as a whole, and the monthly low as well.
I assume that this won't work when I'm not online on google sheets but as long as I can find the high/low and have it update for whenever I log onto sheets (if a new high/low is reached) that's good enough. If it updates without me online that's a plus, but I'm not sure if that's even possible.
Edit: As requested, the total value of the main cell is calculated from the market value of all stocks in my portfolio combined. The number of stocks changes whenever I buy or sell one but usually there are a few. I am using
googlefinance() for the stock data, together with an
index() formula for some other assets (notably crypto where
googlefinance() doesn't work). The closest I got to resolve the problem was using the
ifs() function by referencing cells and making them equal if they're greater than the current value, but this info might be irrelevant because I came to the conclusion that it's not really possible.
Please let me know if there's any other info you would like to know about.