0

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.

koen van tiel
  • 7
  • 5
  • 1
    yes, it will be more calculation efficient. a worksheet IF does not calculate the false portion of the formula. another benefit would be swapping out offset for an equivalent index. –  Jan 22 '18 at 00:22
  • You beet me to it. I forgot it in the PS. Indexing is planned for next month. – koen van tiel Jan 22 '18 at 00:25
  • [The Gold Standard sites for Excel performance](https://fastexcel.wordpress.com/) - [and this](http://www.decisionmodels.com/) – chris neilsen Jan 22 '18 at 00:45
  • put spaces after `;` to make it readable – phuclv Jan 22 '18 at 04:21
  • Jeeped: Indeed, swapping out OFFSET for INDEX would help. But it turns out that short circuiting a Volatile function with IF only works if the volatile function is in another cell, and not in the same formula. See my answer below. – jeffreyweir Jan 22 '18 at 10:02

1 Answers1

0

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.
jeffreyweir
  • 4,668
  • 1
  • 16
  • 27
  • I've used the volatile function counter on http://www.decisionmodels.com/calcsecretsi.htm on a group of 18 of the Offset() functions. It went up to 460000 after altering 10 cells. Thanks for the tip. There is a lot of useful info there! – koen van tiel Jan 22 '18 at 22:01
  • *I also have a sheet with 100798 OFFSET() functions. So that's why it didn't work on an Ipad 0:-) – koen van tiel Jan 22 '18 at 22:19