Search code examples
excelperformanceoffset

Excel Offset performance gain bij adding if()? Answer; NO

update The volatile functions can't be mitigated with the IF() statement. See answers below. 1

I've just heard my excel sheets need to run on potato speed laptops...

Would;

=IF(A1="Test"** ; OFFSET(B5 ; MATCH(C8 ; G10:G15) ; OFFSET(B5 ; MATCH(C8 ; G9:F9)) ; 0)

be quicker than just the offset functions

=OFFSET(B5 ; MATCH(C8 ; G10:G15) ; OFFSET(B5 ; MATCH(C8 ; G9:F9)) ; 0)

My gut says yes... But I can't find a clear answer if the "unused" part of the IF() function isn't calculated or "volatile".

I have around 120 offset functions on the sheet with 200 MATCH functions. Depending on a lot of variables between 50 and 90 offsets are actually used for the result at one time.

I hope to hear from you,

Koen.

PS; it wouldn't make a difference if I would switch the function and the zero right? PS; it would be a temporal fix til I can put stuff into the (quicker?) index function.

update The volatile functions can't be mitigated with the IF() statement.

Solution

  • Last I read, trying to short circuit a Volatile Function with IF still results in the Volatile function being executed, even if it is in the FALSE part of the statement. So what you want to do is remove the volatile OFFSET function rather than try to short circuit it. The INDEX function can return a dynamic range, so that's what I'd use. See my answer at Can Excel's INDEX function return array?

    Here's Charles Williams on the matter:

    Using a volatile function in a formula will flag the cell containing the formula as volatile, even if the volatile function never gets executed:

    • =IF(1<2,99,NOW()) will always return 99 and the volatile NOW() function will never be called, but the cell containing the IF formula will be treated as volatile, (thanks to Stephen Bullen for pointing this out).
    • If cell A1 contains =NOW() then =IF(1<2,99,A1) will always return 99, but the cell containing the IF formula will NOT be treated as volatile.