1

In Excel, Vlookup and Indirect are notorious examples of Volatile Functions in which there is an unnecessary amount of recalculations every-time you edit a cell (even if it does not change the output). Is there a list of volatile functions in Google Sheets and is there any documentation warning about its use or is this not the same type of problem?

Update: Vlookup is not officially volatile in the latest version of excel but it did used to cause (haven't checked recently) recalculations when editing cells that could never change the output due to poor optimization.

Rubén
  • 34,714
  • 9
  • 70
  • 166
CodeCamper
  • 6,609
  • 6
  • 44
  • 94

2 Answers2

2

Part of this question asked if there is any documentation listing which sheets formulas are volatile. I couldn't find any list from Google. (There are lots of 3rd party sites such as Ben Collins which provide a list, but no source is given). The only Google Documentation I could locate to confirm functions' volatility was to review the full list of Google Sheet's formulas. Unfortunately to fully check a formula, one has to click within the detail of each individual formula and check in the notes:

enter image description here

I cross-checked the Excel formulas that Microsoft lists as volatile and it appears Player() list is correct (no surprise). What's interesting (to me) is that Offset and Indirect are NOT listed as volatile in google sheets. This can allow for some more flexibility with array functions and other references.

Side Note on OP Comments Regarding Vlookup

OP asserts that vlookup is a volatile function. This is not correct, as was shown in the Microsoft link. VLookup does calculate less efficiently compared to index/match and xLookup but a change in a cell outside of the vlookup range will not trigger a recalculation.

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • Even though it is not officially volatile there is many normal use case circumstances where changing a cell which could never change the output would force a recalculation unnecessarily. – CodeCamper Jan 23 '22 at 02:49
  • 1
    Also I thought I remember reading an article somewhere where Excel updated and optimized the vlookup function but I can't seem to find it. Updated OP to note it is not volatile. – CodeCamper Jan 23 '22 at 02:55
1

yes, Google Sheet does have volatile functions:

and the only issue with them is that they are hard to be frozen

update

with the latest pack of new functions, all volatile functions can be frozen:

=LAMBDA(x,x)(RAND())

enter image description here

=LAMBDA(x,x)(NOW())

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    That makes sense, and you can visually see that, but they are volatile visually in the sense that you know and can see the recalculation. However in excel you can have a vlookup that is never changing but if you change a completely unrelated cell it causes that cell to repeat the vlookup "secretly" thus if you have thousands of vlookups in a totally unrelated sheet it causes a huge performance hit. Is vlookup actually not volatile in Google Sheets so that it is an improvement over Excel or does it have that same issue (if it does then that means there is more volatile functions). – CodeCamper Dec 18 '19 at 22:14
  • vlookup in GS does not have such issues at all. these are the only volatile functions out there for GS – player0 Dec 18 '19 at 22:18
  • So pretty much today() is the only function the average person has to be careful of because they could just put it in one cell. I notice putting Arrays in a formula also acts a little "volatile" since if you reference 10k cells and one get's changed it forces a calculation of all 10k, but that is sort of common sense but a source of slow down especially being spoiled with ArrayFormula. – CodeCamper Dec 18 '19 at 22:28
  • 1
    I am making a comment on your last statement `hard to be freezed` by turning circular references on you can easily freeze them with an if statement `if(freeze,circularreference,rand())` – CodeCamper Dec 18 '19 at 22:32
  • How is ‘whatthefoxsay()’ volatile if it only randomly generates an output in the initial entering, or is there some other situation where it recalculated besides deleting and reentering the actual function? – CodeCamper Oct 18 '22 at 06:16
  • @CodeCamper it is not really volatile I guess but yes, there is one instance when it recalculates and that's when you set the formula with a checkbox for example and then close and reopen the spreadsheet - https://i.imgur.com/GlAGc2C.png – player0 Oct 18 '22 at 09:08
  • For WhatTheFoxSay() Okay I have a question about this checkbox recalculation, it seems to only happen when I insert a new checkbox for the first time and then when I refresh the page a second time it reverts back to the original value? – CodeCamper Oct 21 '22 at 22:05
  • @CodeCamper it just recalculates once. you could get original value or some other value. here: https://i.stack.imgur.com/ndI2V.png – player0 Oct 21 '22 at 23:35