(for HansV?)How do Data Tables work? (NOT"How to use them?")

[lingyai]

How do Data Tables do what they do? (NOT asking how to use them)

I apologise in advance for the length of this post. This is not a question about how to use Excel Data Tables. It is about how they work – what is Excel actually doing? (Hans, this might be one for heavyweights such as yourself.)

I ask as I have come up with a potentially very useful application of them, but the problem is that at least superficially it would appear as though I’ve created a circular reference. However, Excel does not notify me that there is a circular reference (Iteration is turned off by the way).

What I’m doing is best seen in the attached example ("Simple data table example for forum.xls") which has two versions of a model which has been kept trivially simple for these purposes.

The first version is a “normal” model (does not use Data Tables), shown for comparison with the next. This normal model consists of a row of six “raw” annual cashflows from a project. The user can choose when the last project year will be by inputting this year in a single cell. This cell then feeds a binary array, such that for years when the project is “alive”, the value is 1; and for years after this, the project is finished, so the value is a 0. In each year, this binary value is then multiplied by the corresponding “raw” cashflow, resulting in a truncated series of annual cashflows which end in the specified year. So far, so non-controversial.

The second version (also shown in the attached screen shot) does the same as the first, but differs in where the single cell (cell E18) containing the last project year comes from. This time, it is not manually input. Rather, it is calculated – specifically it is designed to choose the project lifespan which maximizes total, project-life cashflow. (I know I could use Solver, GoalSeek or VBA, but for various reasons which are not relevant here, I don’t want to.)

Here is the flow of calculations (this will be much easier to follow if you look at the attached file). The first thing in this model is the cell (E18) containing the last project year; it is calculated below, in a way I’ll soon explain. The year in this cell determines the values in the binary array below it, which truncates the annual cashflow series the same way as in the “normal” model. And again, the annual, truncated cashflows are summed to give a project-life total cashflow.

Here’s where the second model differs.

-- This project-life, total cashflow based on the truncated annual cashflows feeds the top cell of a Data Table (cells E35:E40 and F34:F40), in effect telling the Data Table that we would like to see how this total cashflow changes according to changes in a certain variable. In this case, the variable is the last project year. So the Data Table calculates the total, project life cashflow for when the project’s last year is 2011, for when it is 2012… etc up to and including 2016.

-- Next, I have devised simple logic (cells J34:J40) which determines which project lifespan (specifically, which final project year) results in the highest possible project-life, total cashflow. In this example, the cashflow-maximizing lifespan is found to be the one ending in 2016. (In this example, you could of course see this without bothering with a Data Table or even any formulae, but this is just to keep things simple here; I have more complex uses for this technique in mind).

-- Here’s where it gets interesting. I feed this value of 2016, found in cell J34, to the top of the model – i.e. to the aforementioned cell E18 (i.e. the formula in cell E18 is “=J34”.) This in effect makes the model, at the outset, follow the “advice” gained from the Data Table on how to maximize cashflow. But this “advice” comes from the bottom of the model.

It has all the trappings of a circular reference. But it doesn’t seem to be. As mentioned, when I first typed “=J34” into cell E18, Excel did not say anything about a circular reference. I have done this in larger, much more complex models, also with no apparent circular references. The models appear to function quite normally and correctly. In this simple example, you can check the result as follows: the Data Table version of the model says that the best project lifespan for maximizing project-life, total cashflow is the one ending in 2016, resulting in $50; so go to the first, “normal” model, manually choose 2016 (cell E4) as the last project year, and the result (project-life, total cashflow in cell F13) will also be $50. In fact, in the “normal” model you can input any final project year between 2011 and 2016 (which is the model’s timeframe) in cell E4, and cell F13 will always match what the Data Table in the second model says it will be. This happens when you change other underlying assumptions (such as the “raw” cashflows) as well. Nor have I noticed any volatility in the results.

I need to “sell” this Data Table method to some colleagues, and to do so I need to convince them that nothing dodgy is going on. So I need to know how Data Tables actually do what they do. I did find in this Microsoft article

http://msdn.microsoft.com/en-us/library/bb687891.aspx" onclick="window.open(this.href);return false;


that with Data Tables, “Circular references are tolerated. If the calculation that is used to get the result depends on one or more values from the data table, Excel does not return an error for the circular dependency.”

But the article doesn’t say why or how.

I have heard someone once say that with a Data Table such as the one in my example, Excel effectively, “in the background” makes 6 virtual copies of the model, one assuming the last project year is 2011, another, 2012 etc ; and then reports back the 6 resulting project-life, total cashflows to the model. It would be as though you had 6 copies of the model, each on a different PC, and were manually typing into the original model the results you see on each of the 6 PCs. That would – at least to a layman like me – explain why the original Data Table seems to work fine. It would also explain why large Dat Tables (or normal-sized ones in large models), take so long to recalculate.

Is this what Excel actually does? If not, what is it doing?

I would be very grateful for any advice.

[sdckapr]

This is Cross Post from http://windowssecrets.com/forums/showth ... -use-them). That post already has generated some feedback at that site.

Steve

[lingyai]

Yes, it has generated feedback, only in the form of speculation. I am still seeking an answer from someone who knows what Data Tables are doing, rather than opinions about what they might be doing. This is why I waited a few days after posting there before posting here.

[lingyai]

By the way, Steve -- the fact that you look at forums in addtion to Windows Secrets seems to imply you think that other forums also have something to offer. That is my view too, and therefore why I have not limited myself to one forum only.

[rory]

I don't think Steve was suggesting you should not post in more than one forum, but it is considered good (n)etiquette to post links to your other posts so that people don't duplicate effort. It is in fact a rule in most forums, though the level of enforcement varies widely. ;)

[lingyai]

I see your poiht Rory. That said, as I mentioned on the Lounge in response to Steve's post, if I post on more than one forum and get a conclusive answer on one, I then post a link to that answer on all. This is what saves people wasted effort.

[rory]

Not really - if people can't see what has already been suggested and failed or been discounted elsewhere, they may replicate that.

[StuartR]

If anyone wants to continue the discussion about (n)etiquette for cross forum posting then please create a new post for this in the Lounge Matters forum, and then add a link to that discussion here.

[sdckapr]

By the way, Steve -- the fact that you look at forums in addtion to Windows Secrets seems to imply you think that other forums also have something to offer. That is my view too, and therefore why I have not limited myself to one forum only.

I reply to people on many different forums (not just excel). I don't purposely cross-post, and will link answers from one board into another. As mentioned by others, if one is going to cross-post you are up-front about it so that others do not duplicate efforts and can build on the answers of others.

Steve

[lingyai]

Please see Stuart's post, above.

Does anyone care to address the actual question?

[lingyai]

To return to the topic. Having kept digging, I've come across something from Microsoft which now gives me some big-ish reservations about the use of Data Tables described in my original post.

In a notice here

http://msdn.microsoft.com/en-us/library/bb687841.aspx" onclick="window.open(this.href);return false;

MS describes the tolerance of the circularity as a "known issue". That in itself gives me pause. In full, they write:

"Circular References are Tolerated in Data Tables

Excel currently does not raise an error if the calculation that a data table is based on refers to something in the table itself. As unlikely as this scenario might be, you should be careful when you are creating or modifying formulas that are used to calculate data table values."

I'm not entitrely clear what scenario they are deeming "unlikely" -- the use of Data Tables this way, period? the use of Data Tables this way resulting in an error ? -- but the fact that they vaguely urge caution further feeds my doubts.

I also wonder if being a "Known Issue" means a future version of the object model might change with regards to Data Tables' tolerance of this kind of circularity -- note their use of "currently" -- and if so, what that might mean for pre-change spreadsheets using Data Tables this way.

Anyway, even though I've created a number of models using this method, some of them quite complex, which a lot of checking indicates they appear to behave properly, I now think I probably will NOT use or recommend this method. The same result can be accomplished by using VBA to compile, in hard-coded form, the same results a Data Table would give. Making these results hard-coded would eliminate the circularity. The only drawback would be losing the "live" element, as the macro would have to be run anytime a relavant change was made to the model. Which I'll accept if it means preserving the model's integrity.

Any (on-topic) thoughts, gents?

[StuartR]

Given the wording in that MSDN article I think you are wise to avoid this.

[lingyai]

So much ado about nothing, I reckon ;-)

[Don Wells]

The only drawback would be losing the "live" element, as the macro would have to be run anytime a relavant change was made to the model.
Any (on-topic) thoughts, gents?

You might consider using an event driven approach to fire the macro any time the table is changed?

[lingyai]

That's a good point, Don, thank you