for nerds and wannabes: why =OFFSET(A1,0,0) is bad

Harlan Grove and Charles Williams are having a discussion on volatility. OFFSET vs INDEX
Over at Smurf on Spreadsheets.

Nerd alert.

enjoy

http://smurfonspreadsheets.wordpress...05/offset-wtf/

Hi teylyn,
What your take on the indirect() function?

lol

Thanks Teylyn - this wannabe is in!
I haven't seen a "live" debate of Harlan's but I've read some of his... (we can definitely say knowledgeable & also, hmmm... "forthright views") in comments/threads.

Rob

What your take on the indirect() function?

I'm still learning. But what I've learned so far:

- avoid volatile functions if you can.
- Sometimes a longer formula can be more efficient than a shorter one with volatile functions. It's not about how short your formula is. It's about how fast it is.
- sometimes you can't avoid volatile functions
- sometimes volatile functions are just the easy way out. Work harder.
- try to develop the non-volatile alternative and become familiar with it
- if you tend to use volatile functions in small workbooks, where speed is not an issue, you're more likely to also use them in big workbooks where they may bring the house down.
- if you see one of my solutions commented and improved on by DonkeyOte, study DO's version and learn from it. Like I try to do.
- It may take a while, but it can be done. I'm sure.

if you tend to use volatile functions in small workbooks, where speed is not an issue, you're more likely to also use them in big workbooks where they may bring the house down

now the thing is when?
i use loads of workbooks for stuff but they never get that big(when they do i archive and start again!),i avoid volatility when i can but sometimes its just easier. a diference of a couple of seconds in a workbook calculating doesnt matter to me.

mdw, when you read through the blog post where Harlan Grove and Charles Williams post their insights, you can see that they discuss 20MB files with dozens of columns and thousands of rows.

That's where volatile functions will make a difference to the worse.

DonkeyOte tried to make it clear to me that if you get into the habit of favouring volatile functions in small files, just because they're easier to construct and use, you may develop a habit and continue to uses them, even when the files grow so big that a non-volatile alternative would make a lot of difference.

Even when posting answers here, we often only see a small data sample and don't know how big the OP's original file may actually be. So it would be better to develop the most efficient solution from a speed perspective rather than the convenience side.

I know that every situation is different, and that solutions also often need to be tailored to suit the skills and understanding of the OP, but, hey, if we can teach them to fish ....?

Yes, I agree, if we can teach them to fish, let's do it

I learned how to create non-volatile dynamic named ranges using Index late last year from Roger Govier's tutorial (http://www.contextures.com/xlNames03.html). BUT I still occasionally have difficulty wrapping my head around this approach & even knowing the Index approach, my previous habit of...
[alt + i + n + d], "dynPTSrcRng", [tab], [tab], "=offset(..."
still exists & I usually find myself 1/2 way through typing the offset formulae before I stop myself!
(Edit: at work in 2003)


Rob

I've often wondered re: use of INDEX versus OFFSET for DNR's in the past and would confess I'm still not entirely clear on the subject having never really got a definitive answer on it in the past.
My "impression" was/is that Names are Evaluated when the formulae using them are themselves Evaluated - hence why an overuse of Names can lead to detrimental impact on performance.
On that basis I have always presumed that it is in fact the Volatile nature of the formulae referring to the Names that is of primary concern rather than the Name definition itself... needless to say I don't know how accurate that assumption is !
Thinking again now - I guess things become Volatile by association... that would make most sense.

Quoting Charles Williams' site:

Originally Posted by DecisionModels

The benefit of this [DNR] (apart from the fact that you don’t have to change all your formulae) is that Excel then only has to calculate using exactly the right number of rows and columns. However there is a performance hit if you use a lot of Dynamic Ranges. The main performance hit is because OFFSET is a volatile function and therefore is always recalculated,...

Originally Posted by DecisionModels

Circumstances causing a name to be evaluated.
The circumstances causing a name to be evaluated are not the same as a formula in a cell.

A name seems to only be evaluated when a formula that refers to it is evaluated.
A name is evaluated every time a formula that refers to it is evaluated, so that using a name in multiple formulae can cause the name to be evaluated multiple times.
Names that are NOT referred to by any formula are not calculated even by a full calculation (Ctrl/Alt/F9).

Regardless, it would seem based on general DNR approaches adopted/illustrated by the formula gurus that OFFSET is the norm.
I think most agree that INDEX is generally speaking a pretty slow formula (more so with large ranges of course) which if you consider it's (immense) flexibility is not surprising ... I think you could argue INDEX to be the "best value for money" function of all, I would.

... an overuse of Names can lead to detrimental impact on performance

Assuming that's true (and they surely carry some overhead), IMO it's a pretty good tradeoff for understandability and maintainability.

It would seem based on general DNR approaches adopted/illustrated by the formula gurus that OFFSET is the norm.

Agree, and that surprises me a little.

Most interesting bit for me was this:

using INDEX as the second part of a range argument (A$2:INDEX(A$2:A$8,7,)) makes it semi-volatile: it gets recalculated at workbook open time

which I never knew before.

R, what's your take on use of OFFSET in Names ?

It seems that the evaluation of the Name must be Volatile yet why then recommend use of OFFSET in DNRs for use with SUMPRODUCT ?

Let's assume I have 3000 rows of data - ie cells populated are A1:B3000 - remaining cells are blank - my range will expand over time and I need to ensure I encompass all data.

Option 1:

=SUMPRODUCT(--(A1:A10000="a"),--(B1:B10000="b"))

Option 2:

I have 2 OFFSET based DNR's setup that restrict my range to just the 3000 rows thereby reducing the number of cells "iterated" in the SUMPRODUCT, ie:

=SUMPRODUCT(--(DNR1="a"),--(DNR2="b"))

If the Name is Volatile in it's own right then by association the SUMPRODUCT would become Volatile too, no ? I would have thus assumed that calculating 20000 cells "as and when" required would be better than calculating 6000 cells at every volatile action.

I would dearly love to get to the bottom of this.

(And I agree with Rob that use of INDEX over OFFSET would obviously remove the Volatility in the Name but that's not my question here... rather the Q is concerned with the (prevalent) use of OFFSET full stop)

I try to avoid using OFFSET ever in names, for exactly the reason you specify. Ignoring the fact that you should rethink a worksheet with thousands of SUMPRODUCT formulas, the performance using OFFSET in a DNR will be horrible when working with any open workbook, even if you are referring to a single cell for the COUNT/COUNTA part of the offset.
For smaller workbooks, it's probably not too bad, but I still wouldn't recommend it.

My position is with shg, he brings in a very important statement:

understandability and maintainability

goes first.

I have never run into performance issues (yet), but I'll follow this thread as I like this discussion. Previous post (and even Teylyn's link) are mainly based on assumptions.

"INDEX() became non-volatile in Excel 97", I do not see why OFFSET is treated differently?

And what about Conditional Formatting. This seem to be even worst as these re-calculate all CF-cells at any screenupdate (regardless of calculation in AUTO/MAN mode).

Romperstomper,

What is then your alternative to

Please Login or Register  to view this content.

Would that be

Please Login or Register  to view this content.

and if you know the # of rows will never exceed 20000, would it make a big difference to use
Sheet1!A1:A20000 then Sheet1!A:A ?

Originally Posted by rwgrietveld

Would that be

Please Login or Register  to view this content.

Yes, but I would do the COUNTA part in a cell and refer to that.

and if you know the # of rows will never exceed 20000, would it make a big difference to use
Sheet1!A1:A20000 then Sheet1!A:A ?

I doubt it, and I am always extremely reluctant to ever specify a number of rows/columns if I don't have to. Especially if someone tells me it will never be exceeded...

Oh, and people who use a lot of CF should be slapped silly (or sillier, anyway), IMO.

Here's a related link
http://www.dailydoseofexcel.com/arch...o-more-offset/
In it they talk about the fact it's the recalculation of "COUNTA" that takes up all the time and not the OFFSET but it would seem to me that, in most cases, you will be using the OFFSET in collusion with other functions so stay away from it. I guess if you're just using OFFSET or just using INDEX, there's little difference.

I am still struggling with keeping OFFSET out of my defined names. I just learned how to use the INDEX to define two dimensional ranges as one would get with OFFSET.