Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    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, 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


    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.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't know the mechanics of DataTables. I have used them but in the past have found them to be less intuitive and less editable than other methods, and also calc time intensive so tend to not even use them anymore.

    For example, If I understand your mechanics (and pershaps I don't) why not get rid of the data table and in your original dataset add an intermediate row of Total CashFlow-to-Year in Row 9. In G9:
    =SUM($G8:G8)
    Copy G9 to H9:L9
    Then the max cashflow in the range is:
    =Max(G9:L9)
    and the year with the Max cashflow is:
    =INDEX(G6:L6,MATCH(Max(G9:L9),$G$9:$L$9,0))

    In my (very limited) testing it seemed to give the same results as your table method only without the data tables and the extra spreadsheet calcs...

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Steve,

    Thanks for taking the time to answer. But as I said in my post, the example I posted is trivially simple -- in fact you could solve it "by eye", without any calculations. There are more complex uses for this technique, where it would work for my purposes better than Solver, VBA etc, and where in fact intermediate caclautions such as what you mention would not be possible -- but to go into them would have made an already very long post even longer, with frankly irrelevant stuff.

    What I really wish to know is simply how Data Tables work, and why what I've done appears to be legal, although on the face of it, it is circular.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    I think you may have a misunderstanding as to what a circular reference is, at least how Excel defines it.

    Excel defines a circular reference as a formula which includes the cell containing the formula within the formula, e.g.

    Cell A5 contains the formula =sum(A1:A10) see attached. I hope this helps.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Thanks for your response. With respect, actually I think I do know what a circularity is. It is when Precedent A determines Dependent B which determines Precedent A. It is what occurs when you when you try to solve the problem in the model I posted without using a Data Table, and instead -- as in the variant of it which I'm showing in a screenshot here -- you type "=F17" in cell E4 (see the formulae descriptions in yellow).
    circ ref.jpg


    As for the original question -- if anyone can shed light on how Data Tables do what they do, and on why the original example I've posted is not circular, I would be very grateful.

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    I believe the use of Data Tables has many benefits. They work the way you think they work. Data Tables perform a "what-if" summary analysis. "What - if" I change the year in cell E4 what will be the impact on Truncated Cashflow calculated in cell F13. The table shows (in compact form) what the impact on CashFlows would be for all years in the example without having to present six separate worksheets.

    There are One-Way or Two-Way Data Tables available. In a One-Way table, such as in the example, you can show the impact on multiple parts of your work by changing one data assumption. One-Way tables can have multiple columns of results for the change in one variable.

    Did you get any indication of Circular issues during the build of the sample? I don't think number of iterations would have an impact in the example given.

    May want to explore Two-Way "what-if" with your example. For example, try various outcomes on cumulative cash flow by varying Year project ends and Year 1 cash flow.

    Data Tables have many uses with forecasts that use tax rates, interest rates, inflation rates, etc.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It is not circular since the table does not use the formula reference in its calculations. The table seems to be calcuated at runtime feeding in each of the appropriate values. The "seed value" the formula references seems immaterial to the calculation as it is only a placeholder/example. When the table calculates it replaces the "formula" you have with a value from the column.

    Steve

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    To anyone following the thread. This has been cross-posted at http://eileenslounge.com/viewtopic.php?f=27&t=6756

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Yes, Steve, that is the case, because after a few days, while I received some answers here, they are all speculative, i.e. about what how Data Tables SEEM to work, as opposed to a firm answer about how they DO work. I have therefore posted this at Eileen's Lounge on the advice of Woody Leonhard, who suggsted I would be able to find Hans V there.

    Personally I do not think that if a question is not definitively answered on one forum, there is anything wrong with casting one's net beyond a single forum -- provided that once the question is answered on one of them, the poster shares that answer on all of them, which is what I do. Thanks for your vigilance though.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The only answers you will receive in boards like this when asking questions of this sort (how they work) will be speculative unless you actually get a reply from one of the microsoft programmers. How they seem to work is about the best you can get with most of the features in Excel, even when discussing MATCH, averaging, counting, filtering, pivot tables, etc. The reality is that we have not written the code, even though we may have written code to do similar actions and from our experience we can have a good idea of how it seems to work in practice.

    There is a policy on this forum about cross-posting:
    14. Avoid cross-posting into two or more forums
    Cross-posting is the posting of the same or nearly the same message on more than one forum (for example, posting the same message in both the Database forum and the Spreadsheets forum). To avoid this, choose the one forum that is the most appropriate and post your message there. Most members watch for new threads in several forums.
    The problem is that you did not acknowledge the cross-post and link your new question to this discussion or link the discussion to your new discussion (which is what I did). You should acknowledge the cross-posting so that people don't duplicate efforts and can build on the previous replies.

    If you want a "definitive answer" about the specifics of the code in a program, the best place for an answer is to go to Microsoft since only someone who has seen the actual code can do anything but speculate on the mechanics of a program. I would speculate that much (if not all) of the code would be proprietary, however, and they may not want to share the mechanics with anyone.

    Steve

  11. #11
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Steve, a) Have tried MS. Their lack of response led me here. b) Please read the forum rules you cite -- they clearly refer to choosing the single most appropriate Windows Secrets forum; they do not refer to external sites; to do so would be to arrogantly assume that Windows Secrets has a monopoly on Excel wisdom. c) If Woody Leonhard, aware that I posted this here, personally suggests that I try HansV at his forum as well, that's good enough for me. d) Perhaps some MS programmers do read this forum, or perhaps other readers have, or have had, access to such programmers on this point; I would not rule it out. Can't hurt to ask. If it bothers you, feel free to ignore. e) You were asked by the moderator of the site where I have cross-posted this not to disrupt my thread there any more, suggesting that instead you start a new thread, if you wish, on your thoughts on netiquette. That would be a good idea here too. f) I have no wish to get in a flame war with you. You have already expressed your view clearly. You seem more interested in procedure than substance. I'm more interested in the substance. So perhaps you could call it a day on this one, please.

  12. The Following User Says Thank You to lingyai For This Useful Post:

    nononcents (2011-06-09)

  13. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    a) Have tried MS. Their lack of response led me here.
    And as I mentioned, that is expected in some manner as the coding and mechanics could be considered proprietary.

    b) Please read the forum rules you cite -- they clearly refer to choosing the single most appropriate Windows Secrets forum; they do not refer to external sites; to do so would be to arrogantly assume that Windows Secrets has a monopoly on Excel wisdom. c) If Woody Leonhard, aware that I posted this here, personally suggests that I try HansV at his forum as well, that's good enough for me.
    I read the rules, I see forums as both internal and external. And the complaint was not about cross-posting but about the (n)etiquette about being upfront about cross-posting and cross-referencing the sites. But Hans did not program Excel, so his speculations are no more valid than mine or anyone else's, he just has more experience to base his speculations on. But it seems unlikely he can provide a "definitive answer"

    d) Perhaps some MS programmers do read this forum, or perhaps other readers have, or have had, access to such programmers on this point; I would not rule it out. Can't hurt to ask. If it bothers you, feel free to ignore.
    It doesn't bother me. My goal is to use my experience to answer questions. If the fact that I did not program excel to makes my experience less of an answer, you can ignore my answers.

    e) You were asked by the moderator of the site where I have cross-posted this not to disrupt my thread there any more, suggesting that instead you start a new thread, if you wish, on your thoughts on netiquette. That would be a good idea here too.
    I was NOT asked any such thing of a moderator. StuartR had posted a message about starting a thread on netiquette, but is was not directed at me since my only response at that time into the thread was referencing the cross-post (which he thanked me for doing). My later reply was in response to a question you specifically addressed at me, so I courteously provided a response.

    f) I have no wish to get in a flame war with you.
    That surprises me a little based on your responses to me since I mentioned the cross-posting.

    You seem more interested in procedure than substance. I'm more interested in the substance. So perhaps you could call it a day on this one
    Not procedure, just courtesy and the respect for people's time. That is all I have to say on the subject in either forum on this topic. Good luck with finding a definite answer.

  14. #13
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

  15. #14
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by lingyai View Post
    ........ I have therefore posted this at Eileen's Lounge on the advice of Woody Leonhard, who suggsted I would be able to find Hans V there.
    ......
    Quote Originally Posted by lingyai View Post
    .......
    c) If Woody Leonhard, aware that I posted this here, personally suggests that I try HansV at his forum as well, that's good enough for me........
    Is there a link to the comments posted to/by Woody?

  16. #15
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    As former neighbours, we correspond by email. I'm not linking into my email account, thanks. If you've doubts, ask him. Or just assume I'm lying, whatever you please.

    (This is becoming like a parody of irrelevant threads. Where's the emoticon for rolled eyes? )
    Last edited by lingyai; 2011-06-10 at 10:21.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •