Fix value of calculated cell

I have a math cell that computes the value x/y on a regular basis. x is a fixed number, but y changes every few hours. I want to lock in the value of y so there is a starting point for x/y, let y update, then compare each updated value of x/y to the locked in starting point of x/y. Appreciate any direction on how to lock in the initial value of y. Thank you.

I think the direction you need is to have 2 columns of Y. “Original Y” and “Updated Y”, updates would only write to the “Updated Y” column. Then you have 2 math columns to compare.

Agree, but need to determine how to fix the value of y at some point in time, as it is updated throughout the day. In Sheets, I would copy y and paste special using the value. There should be a more elegant way to do this in a Sheet with thousands of rows. What do you think?

So you don’t have a fixed logic to lock in the Y?


How are you updating the Y now? I assume it’s a manual update from the app?

Y is updated automatically from Google Finance in the Sheet.

As you use Google Finance, which is not available in Glide, I think your best bet is to have an additional Script and a boolean column, let’s call it “Locked?”.

If Locked is not True then set the Google Finance formula to the column “Y original”.

If Locked is True then copy and paste the “Y original” value to that same cell to lock it in, and then set the Google Finance formula to the column “Y updated”.

You would have to set the boolean column yourself, from the app.

1 Like

Thank you. Will give it a look.

1 Like

What kind of value are you trying to lock in? Is it possible to set up google finance to pull a value from a certain day or time? I’m not really sure, just wondering if it’s possible to get what you need by setting up a second formula.

1 Like

I wound up simplifying the use case and was able to arrive at a solution. Requires user to enter a little bit of data, but is a good trade-off to avoid some of the complexities that are popping up around this issue. But, yes, down the road a script that will pull data at a point in time is likely the answer. As always, thanks to you both for your prompt and insightful anwers.