Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Two Variable Data Table Oddity (XP - SR2)

    I just came across this rather weird situation involving a two-input data table one of my users had constructed. I have uploaded a non-specific copy of the worksheet that illustrates this problem. (If you look at it, you need to scroll down to row 133 to see my text box explaining the problem.)

    Basically, the issue is that if she refers one of her row input variable cells from the table to a specific cell in the worksheet, the table returns incorrect values. If she refers to any other cell (that contains the same value), the table returns correct values. If she simply enters a number, the table returns correct values.

    My question is, why does the table return incorrect values when this row input variable refers to cell D87?

    Thanks,
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Two Variable Data Table Oddity (XP - SR2)

    I have run into this before, but I'm not sure I can give you a complete answer. It's because you are setting up a kind of circular reference that Excel doesn't recognize, in that your table inputs are {=TABLE(D87,D44)} and your row input which is the source of the variables used in the table also refers to D87. One way around it is to make D87 dependent on another cell, and the use the other cell as the argument in cell G133.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Variable Data Table Oddity (XP - SR2)

    Actually, that doesn't work either. I selected cell G132, which contains a hard-coded "2", and the results in the table calculated correctly. Then, when I changed the contents of G132 to "=D87", the table again calculated incorrectly.

    I just can't think of a reason why this occurs. It's pretty weird. Perhaps I should query Microsoft directly...

    Thank you for your response, though. If I come up with an answer, I will be sure to let you know what I find.

    Regards,

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Two Variable Data Table Oddity (XP - SR2)

    I'm not truly expert in this George, but my point about the D87 reference means that no column input or row input to the table can have a direct or indirect reference to D87, if it does, the result is a kind circular calculation which Excel doesn't recognize. And in your fix, you still have an indirect reference back to D87 where an input row cell is also part of the table calcs. I admit it's a bit mysterious to me, but try it this way:

    leave the hardcoded '2' in cell G132
    set cell D87 as '=G132"
    set cell G133 as '=G132"

    Does that fix it?
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Variable Data Table Oddity (XP - SR2)

    Hi John,

    Forgive me for not responding sooner, but the weekend got in the way <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Anyway, your suggestion did work. And the user is happy, despite the fact that neither of us knows why the original construct doesn't work.

    Thanks again for your efforts and your suggestions. I really appreciate it.

    Regards,

Posting Permissions

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