2

I have a table in Excel that often uses OFFSET to get one column relative to another one. This is necessary to allow me to dynamically resize the table.

So for example in cell D5 of a MyTable, I have a formula like this - where D$2 is the first row of the table

=INDEX(
    OFFSET(
        D$2,
        0,
        (- OffsetDistance ),
        ROWS(
            MyTable
        )
    ),
    MATCH(
        LARGE(
            OFFSET(
                D$2,
                0,
                (- 1 ),
                ROWS(
                    MyTable
                )
            ),
            ROW() -
                ROW(
                    MyTable[#Headers]
                )
        ),
        OFFSET(
            D$2,
            0,
            (- 1 ),
            ROWS(
                MyTable
            )
        ),
        0
    )
)

Now I've heard it said that "OFFSET is slow because it's volatile". That's like saying RAND() is slow; generating a random number is not slow, nothing compared to sorting a list or searching for a substring - the slowness is because the sheet recalculates often.

I'm wondering, how much slower using the OFFSET function is than a Range literal. Basically I have a tradeoff between flexibility and being able to dynamically resize my table vs faster hardcoded offsets.

So generally, is OFFSET itself likely to be the source of slowness in the sheet? I imagine not, because I'm guessing it's an O(1) operation that just has to add a couple of numbers together. But I can't think how to quantify this

Greedo
  • 4,967
  • 2
  • 30
  • 78
  • I don't think big O is relevant to the kind of slowness both `OFFSET` and `RAND` cause - they both have to recalculate after any cell anywhere in the worksheet is modified => they can slow down insertion of new data, changing autofilter conditions and other seemingly unrelated operations – Aprillion May 03 '20 at 11:42
  • The point is that a volatile function is executed whenever anything is calculated in the worksheet. Obviously, there is a difference whether you have one such function being executed or one in every row, and whether you have 300 rows or 1.3 million of them. I suggest you ignore the caution until you feel that your worksheet reacts sluggishly. Just bear in mind that the slow response you may eventually notice will not appear to be related to the functions you suspect. OFFSET() will rarely cause a sheet calculation itself. But it will be recalculated when something else does. – Variatus May 03 '20 at 12:05
  • @Aprillion Yes, I understand the nature of volatile functions triggering everything else to recalculate. But I'm not worried about sluggishness, I have calculations set to manual, so I only care about time for a single calculation, and I'm wondering what proportion of the execution time is spent evaluating `OFFSET` - that's the kind of slowness I'm worried about, not recalculation triggers. – Greedo May 03 '20 at 12:10
  • 3
    OFFSET itself is very fast, its the volatility that is potentially the problem. I once built a workbook with over 10000 OFFSET formulas and it was extremely fast to calculate. – Charles Williams May 03 '20 at 12:15
  • if you have manual calculations, it shouldn't be a problem.. one technicality: it is not that `volatile functions triggering everything else to recalculate`, but the other way around - anything else will trigger volatile functions to recalculate – Aprillion May 03 '20 at 12:17
  • @Aprillion I meant the volatile function will trigger any dependencies to recalculate when it does (just as editing a cell only triggers that cell's dependencies to recalculate rather than the whole sheet). But I see your point, any worksheet level changes will trigger volatile functions and their dependencies to recalculate – Greedo May 03 '20 at 12:20
  • 1
    they shouldn't - if the result does not change, dependencies shouldn't have to recalculate (unless Excel is very badly optimized, which might be the case, but I hope not) – Aprillion May 03 '20 at 12:22
  • 1
    @Aprillion Just tested, what you say is True for a UDF with `Application.Volatile` but which returns the same value each time, but not for OFFSET weirdly. – Greedo May 03 '20 at 12:34
  • oh well, hearing that, I would suspect that big O of `OFFSET` is not well defined either :D – Aprillion May 03 '20 at 12:36

1 Answers1

1

The slowness will be directly proportional to the number of cells recalculating because of OFFSET (or other volatile formulas). It can become very slow because of it's volatility.

I stopped using OFFSET a few years ago because the difference can become huge when working with many formulas. Instead I use INDEX which is not volatile.

Assume Range A1:A100. Let's say we want 50 cells starting with row 2. With OFFSET that would be:

=OFFSET(A1,1,0,50,1)

Or:

=OFFSET(A2,0,0,50,1)

Instead I would probably use this:

=A2:INDEX(A2:A101,50)

Or:

=INDEX(A1:A101,2):INDEX(A2:A101,51)

whatever is needed in that case.

The complexity of the formulas are also important. If you're mixing an INDEX and a MATCH and so on with OFFSET and then you drag that formula for 50000 rows, those 50k rows will simply recaculate all the time. Maybe you are matching inside a table of 100000 rows. I know it's a strech but volatile formulas should be avoided.

On the VBA side the Worksheet_Calculate event is triggered and there might be code that runs based on that event.

Cristian Buse
  • 4,020
  • 1
  • 13
  • 34
  • 1
    Just as an observation, INDEX can sometimes (annoyingly) behave as though it might as well be volatile (even though it isn't). `INDEX(A1:A500,450,1)` is only ever going to point to the 450th cell in the range, but it'll still recalculate if A10 is modified. I _think_ this is why you will occasionally find people mentioning that MS treat INDEX as volatile. Such a shame they didn't make volatility sensitive to whether the cell contents or cell position is being used in a formula, since this is info that should always be available. – tobriand May 12 '22 at 10:34
  • @tobriand Your observation is correct and it reflects the exact way that [recalculation](https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation?redirectedfrom=MSDN#dependence-dirty-cells-and-recalculated-cells) works in Excel. When using a range X (in any formula) then any change to any cell in range X will mark any dependant formulas for recalculation regardless if they refer to only a part of range X that wasn't changed. – Cristian Buse May 12 '22 at 12:14
  • @tobriand But at least INDEX won't recalculate if an unrelated change occurs somewhere else whereas OFFSET does recalculate all the time. So, INDEX is not volatile but I do agree it would have been nice to distinguish between position and content. I would definitely want ```=A2:INDEX(A2:A101,50)``` to only recalculate if a change occurred between A2 and A51 but not between A52 and A101. – Cristian Buse May 12 '22 at 12:15
  • Yep. In general I take the same approach. That said, recently I found myself trying to establish a range for a row in a block of cells users were expecting to edit frequently, and the upshot was that I got a calculation cascade for way too much every time a value was changed (effectively 1s lag, which wasn't acceptable). Changing to an offset for the same formulae switches that to probably a 50-150ms lag. Still there, but MUCH faster. HOWEVER, it is worth noting that my offset usage was VERY simple: `OFFSET(A1, 1, NAME_WITH_CONSTANT, 1, OTHER_NAME_WITH_CONSTANT_WIDTH)` – tobriand Jun 20 '22 at 13:48
  • @tobriand I guess it all depends on how many cells are dependant of the OFFSET formula because that dictates what gets recalculated at every single change and eventually affects the speed of execution. So, OFFSET is not too bad when not much is connected to it. – Cristian Buse Jun 20 '22 at 16:16