Volatile or non Volatile, that is the question

[GeorgeF211]

Hi All,


Not a specific question really, but I've been using OFFSET for my lookups for a while, and I'm aware that for resource efficiency I should be using INDEX.


My issue with INDEX is that it requires me to know exactly how large my data set is (unless I'm not understanding it properly - if so please enlighten me). This is way more of a pain than just telling MATCH to look all the way through a column or row (or both) and OFFSETing.


At what point does the speed saved by using a non-volatile solution outweigh inconvenience of working out how big my data series is?

 

[Faseeh]

Hi George,


Offet is a bit slower, see here:


http://www.dailydoseofexcel.com/archives/2010/01/07/new-years-resolution-no-more-offset/


I think nesting many offsets for large data sets can slow down the calculation sheet but for small data size it does't effect too much. (My personal opinion)


Faseeh

 

[Hui]

George


Working out how big a range is is quite trivial and won't add to your problems, especially if you do it once in a named formula and then just reference those parameters in other locations where the Index is used.


The issues with use of Volatile functions is they are only a real problem when you have a Volatile function and then copy if to lots of (10,000+ or more) cells

If you are using a single volatile function in a dashboard to retrieve 1 number you won't even notice it is there


I redirect people to http://www.excelhero.com/blog/2011/03/the-imposing-index.html

to learn about the use of Index instead of Offset

 

[GeorgeF211]

Cheers, knew I could count on you guys for a good answer .

 

[GeorgeF211]

Hi again (figured it was better to revive this thread than start a new one).


I've been looking over the link you sent Hui (thanks for that by the way, I'm pretty sure I get Index now), and something about it is bothering me a little. The way the author uses to set up a dynamic range is interesting, but I ran in to a problem with it today.


Suppose you have information in Columns A, B, F and N. If you use COUNTA($1:$1) to define the width of your array with a fixed start point of $A$1 you'll end up with an array that runs from A to D with however many columns you need.


Any idea if there's a way of overcoming this?


Thanks.

 

[Luke M]

I'm assuming that you want a single array of A:N?


You could do:

=MAX(MATCH("zzz",1:1),MATCH(9E99,1:1))

to get the column number of last column in row 1 that contains info. Note that the 2 MATCH functions are to look for text and numbers, respectively. Does that help?

 

[GeorgeF211]

Thanks Luke.


One small issue with your solution though: won't that throw up an error if you only have text/only have numbers in your array? That means this formula has to become

=MAX(IF(ISNUMBER(MATCH("zzz",1:1)),MATCH("zzz",1:1),0),IF(ISNUMBER(MATCH(9E+99,1:1)),MATCH(9E+99,1:1),0))

Which is really bulky, there must be a more elegant solution surely?