• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

what is indirect function [SOLVED]

Hi ,


There is a lot of material already available on the Internet :

1. http://www.contextures.com/xlFunctions05.html

2. http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-the-indirect-function/

3. http://www.articlejobber.com/tutorial/indirect_function_excel_20060215_1.html

4. http://www.myonlinetraininghub.com/excel-indirect-function#sthash.eR9oIxE6.dpbs

After going through the above , if you have any specific questions on this function , you can post the same in this forum.

Narayan
 
Last edited by a moderator:
One thing missing from all those links is a big warning about the fact that INDIRECT is volatile (along with OFFSET, TODAY, NOW, RAND, RANDBETWEEN and a few other functions).


What that means is that if you have volatile functions in your workbook, any time you make a change anywhere at all on the spreadsheet, Excel recalculates the value of any the volatile functions too. Excel then recalculates every applicable formula downstream of these functions too – regardless of whether anything upstream actually changed or not.


That last part – “regardless of whether anything upstream actually changed or not” – is worth a demonstration.

• Say you have the function =TODAY() in cell $A$1. Obviously that value is only ever going to change once per day, at midnight.

• Say you have ten thousand formulas downstream of $A$1 – that is, they either refer directly to $A$1 or to one of $A$1’s dependents. Those ten thousand formulas will get recalculated each and every time any new data gets entered anywhere on the spreadsheet, even though the value of $A$1 itself only changes one per day!


For this reason, too much reliance on volatile functions can make recalculation times very slow. As Charles Williams (Excel MVP who is an expert on worksheet bottlenecks) "Use them sparingly. Try to get out of the habit of using them at all". In fact, there are usually alternatives to every volatile funciton (sometimes requiring VBA code), including INDIRECT.


While I appreciate INDIRECT's power, I steer clear of it if I can. Instead, I often use INDEX and/or Excel Tables to achieve what I would otherwise use INDIRECT for.
 
One more point to keep in mind:

It works with Open workbooks only and doesn't create 'link'. So do not use it where you have to use cross-workbook references.
 
You should also remember the following points.

Limitations of INDIRECT Function:

*/ It could slow down your workbook, if used in many formulas.

*/ If the INDIRECT function creates a reference to another workbook, that workbook must be open, or the formula will result in a #REF! error. So if you want to call data from other workbook, firstly open the workbook from which workbook you want to calculate. According to the above example open the workbook Module-1.xlsx

*/ If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error in the version Excel-2007.


Regards,

Nazmul Muneer
 
Nazmul - regarding It could slow down your workbook, if used in many formulas.


...in actual fact, it could slow down the workbook if used in one formula that many other formulas are downstream of.
 
@Luke M

Hi!

All precautions are worth if the cost-benefit equation scale leans toward avoiding certain functions usage like INDIRECT. Which not always -and I'd say not in most cases as per my experience in these forums, but in a just few ones instead- happens, so besides agreeing with your favorite use, I still love using it to make things easier and simpler when performance is not affected in practice. In practice, not in theory.

Regards!
 
Yep, that's a fair enought use of Indirect, Luke. Although again it would be unneccessary if the macro at that other thread clears the cells rather than flat out deletes them.


And you could actually use the non-volatile =INDEX(1:1048576,3,1) rather than the volatile INDIRECT("'Sheet1'!$A$3")


Me = broken record ;-)
 
I like this "volatile" discussion on the INDIRECT fx. And thank u Jeffey 4 so much clarity.

I am saving it in my "Chandoo_Likes" folder.

Thanks,

James
 
Good evening jeffery


You are taking the time and trouble to explain and show examples of your work/code/formulas, this I am sure will be appriciated by many.
 
All: here's a good example of when I WOULD use INDIRECT. Although you'll see that even in this case I offer a non-Volatile alternative.

http://www.excelguru.ca/forums/showthread.php?1812-How-to-prepare-summary-sheet-in-the-same-workbook-have-multiple-sheets&p=8115#post8115


And thanks for the feedback, bobch.
 
I think the one thing all of us can agree on is that there is always "another way" to solve a problem in XL. =)


@jeffrey

If you do write up a blog post, I would enjoy one covering all (or at least the major) volatile functions, or if there is some way to test whether a function is volatile or semi-volatile.
 
Some good, differing opinions on the thread which makes for an interesting read.


I think advanced Excel users too frequently make the mistake of dismissing volatile functions without proper consideration (just because they're 'volatile' oh no!) and, as a result, may end up using inferior, non-volatile formulae instead. Quite often the non-volatile formulae alternatives aren't any better or are even worse.


Assess your options on a case by case basis: try them out, test, test, test, test again, and then make a balanced decision.
 
Hi Jeffreyweir,


"Say you have ten thousand formulas downstream of $A$1"


No offence, but in the case mentioned above, I think "Manual Calculation" method will be more suitableto save the time regardless of volatile or non-volatile. Please correct if I am wrong.
 
@Colin Legg

Hi!

I couldn't agree more with your emphasized.

Regards!


@Sachinbizboy

Hi!

If immediate results are not needed that'd be an excellent choice.

Regard!
 
@Colin. ...may end up using inferior, non-volatile formulae instead. Which non-volatile formula do you class as 'inferior'?


Occasionally using things like CHOOSE over INDIRECT can be a little more kludgy. But I find those cases are pretty rare. I'll take INDEX over OFFSET anytime. I think Charles Williams' advice is wise - get out of the habit of using them, and you won't inadvertently have issues when it really matters.


@Sachinbizboy - I'd never set calculation to manual if I could help it. There's just too much chance that someone someday will use output of the model without remembering to set calculation to Auto.


What's worse, when you open two workbooks, one saved in manual mode and one saved in automatic mode, they will both have the calculation mode of the first workbook opened. I have seen many cases in my career where analysts have done just that...opened a workbook with calc set to manual, opened a whole bunch of others where calc was set to auto, and then done an entire day's work without realizing that calc was subsequently turned off for all of them.


I'd restructure the spreadsheet instead, so that any bottlenecks were fixed and it didn't need to be in manual.


Many people think that having to run a spreadsheet in manual calculation mode is simply a byproduct of having a big spreadsheet with lots of data and formulas in it. But in actual fact in most cases I've seen, performance issues are more often than not because of inefficient spreadsheet design, coupled with use of volatile functions.


For instance, at work I just restructured someone's model so that it now recalculates in well under half a second in most cases. Previously it was taking one to two minutes. The culprit - bad structure and volatile functions. One was multiplying the effect of the other.
 
@Colin. ...may end up using inferior, non-volatile formulae instead. Which non-volatile formula do you class as 'inferior'?
Click to expand...



Hi Jeff,


But that's my point: it entirely depends on the workbook setup and how it is used, so I can't make a generalisation. A non-volatile formula might be better in one case, but the same one may be worse in another case.


Since Charles' name keeps popping up on this thread, I'll give an example of one of his workbooks which I remember him telling me about. I'll add a disclaimer that this is the conversation as I remember it, it was way back in ~ 2009, it had been preceded by a few beers and I was jet-lagged! :)


He'd developed a workbook which made extensive use of OFFSET() formulae. The formulae and the whole workbook were lightning fast so the fact that they were volatile didn't matter a jot for that workbook's purpose.


This workbook had started life as a slow calculating workbook with extensive use of INDEX(). He 'fixed' it by, amongst other things, changing INDEX() formulae to OFFSET().


If I recall correctly one of the issues was the INDEX() formulae in that particular case, in conjunction with the other inner workings of that workbook, were creating a very bloated dependency tree. Perhaps they were similar to the =INDEX(Sheet1!1:1048576,3,1)[/code] mentioned earlier in this thread in that they had large precedent ranges?


So am I saying OFFSET() is better than INDEX()? Not at all. I'm saying it was better in that particular case. I think it's fine to lean towards INDEX() as a preference because it is not volatile (which I do), but I also think it is a mistake to completely dismiss OFFSET() as an alternative.


I read through the threads written by Charles which you referenced and I found this interesting quote from him:



•Avoid volatile functions like INDIRECT and OFFSET where you can, unless they are significantly more efficient than the alternatives. (Well-designed use of OFFSET is often fast.)



It sounds like the example I remembered is an example of this. Workbooks and calculations are complex things.
 
Last edited by a moderator:
Very interesting. Where he says 'Well-designed use of OFFSET is often fast' he must mean the case where the calculation that OFFSET is doing is faster than an alternative and there are not large numbers of formulas downstream of it. So yes, I absolutely agree that volatile formulas can be faster than non volatile provided they don't have large dependency trees hanging off of them.


But therein lies the rub for me...I keep coming across very large models built by the likes of the big 4 accounting/consulting firms that have recalc times so long that they are effectively ususable. And the sad thing is, said firms (and the users of the models) think that that's just the way it is.


So i'd hypothesise that given 100 slow cumbersome models, a much larger proportion of them would be faster if they didn't use OFFSET than if they did. So if I had to give model builders without your/my/Charles' vast experience of functions and performance issue one 'golden rule', it would be to err on the side of caution and steer clear of volatile functions. If they did this, and still found that they had slow recalc times, only then would I recommend they look at whether OFFSET would improve things. And only after they'd discounted other issues, such as making sure they didn't have exact match VLOOKUPS for no good reason, or that they'd implemented efficient range slicing on large datasets (such as the example I posted at http://chandoo.org/forums/topic/multicheck-across-multiple-tabs which is a pretty unwieldy formula, but damn fast).


Sounds like this is exactly what Charles did in this particular case.
 
You must log in or register to reply here.
Back
Top