How to make google sheet formula just calculate once?

Question

I have 3 rows in my Google sheet, that is stock, price, and total. so, I just use "multiple" formula for stock and price then put the value into the total row. but I don't want total row get an update or change the value whenever I change stock value.

Can someone help me?

Assume

Answer 1

Google Sheets is not build to operate in such a manner. The most simple and fastest solution is to calculate what you need and then use CTRL + C and repaste with CTRL + SHIFT + V

Chromyl

umm, so that is imposible for disable auto update for total row? – Assume

@Assume indeed – Chromyl

Answer 2

Assuming you want cell A1 to only calculate its value once, you can put the following in cell A1. This tells the cell to just use its existing value if there is one (and it's not 0), otherwise run the formula.

=IF(A1<>0, A1, formula())

Since the cell is referencing itself, you will need to enable iterative calculation in File > Spreadsheet settings > Calculation.

I use something like the following for historical Google Finance data, since the value is never going to change, and sometimes Google Finance randomly returns an error. This will only run the GOOGLEFINANCE() formula until it returns a non-zero value without erroring.

=IF(IFERROR(A1)<>0, A1, GOOGLEFINANCE(...))

Ratcliff

Just brillian! Love the 2nd one, to avoid re-requesting http resources on page load. – Braun

Answer 3

Initially I was going with Grayson's solution, but this

=IF(IFERROR(A1)<>0, A1, GOOGLEFINANCE(...))

Places a FALSE on the cell until the result is placed.

I needed that to be empty, i.e. "" as whatever different from that (cell <>"") would trigger another cell to do another query/request.

I also needed the formula to be run depending on the trigger (the url in another cell). In this formula, if the trigger is placed after the formula has run, it won't trigger it.

So I have something like

=IF(AND(IFERROR(E53)<>0,E53<>""),E53, if(D53<>"",IMPORTDATA(D53),""))

---

<Update 2022-11> While using this in arrayformula I noticed something that could be wrong.

IFERROR(E53)<>0  # Doesn't make sense.

It should be

NOT(ISERROR(E53))

And the whole thing in arrayformula (careful with AND/OR )

=ARRAYFORMULA(  IF( NOT(ISERROR(E2:E)) * (E2:E<>"") ,E2:E, 'SOMETHING ELSE' )  )

I leave both versions in case someone spots errors in any of them.

<End of update 2022-11>

Explanation (It took me a while to understand it, so I could extend it):

D53 = myself

If I am nothing ("") or I am in Error (importdata not yet completed)
then
I am the result of ->   If(D53<>"",IMPORTDATA(D53),"")

Which is:
If the cell before me is something different from "", run the importdata 
with that cell as url, otherwise I am "" (nothing)

This achieves the goal of running the formula only once if the trigger is valid (the url on the cell before is there). Once the result is placed, it won't change.

If for whatever reason you need it to run again, you have to remove the formula and place it again.

Notes: If cellX has the result of an external fetch (IMPORTDATA for example) and on cellY=cellX, while on cellX you see "Loading ..." on cellY you will see a 0 (zero). I believe that explains why the other solution was comparing with 0.

Braun

Answer 4

use the copy paste value option per https://www.ablebits.com/office-addins-blog/google-sheets-convert-formulas-values/

Attorn