Data Tables

[Dean]

Lately, I have been using one-variable (three possible values)data tables for a bunch of different 'output' cells. It seems that (perhaps I'm wrong)I have to create a separate data table for each 'output' and that it goes through the three values for each output, then moves on to the next one.

It seems that it would be a lot more efficient if it would, select an input value and then record all outputs corresponding to that value. Is there a way to use a single (one variable) data table in this manner, preferably without resorting to macros?

Thanks!
Dean

 

[flummi]

Hi Dean,

Can you be a bit more specific as to what you are currently doing and
how you would want it to work? Are you doing this in VBA?

Regards

Hans

 

[Dean]

No, I am not using VBA, and would prefer not to. I have 9 output cells (y1
thru y9) I want to run sensitivity results on, each dependent on three
possible values of an input cell (x1 thru x3) and I like to use 'data table'
to show all the results. So, I have 9 data tables.

I notice that, with 9 separate data tables, understandably, EXCEL, seems to
use x1 to figure y11, then x2 to figure y12, then x3 to figure y13. Then,
it repeats this process (varying x with the same three different values) for
y21, y22, and y23. And so on and so forth until y91, y92, and y93.

It seems that things would run a lot faster if it would choose x1, then
compute y11 thru y91, then move on to x2 for y12 thru y92 and finally y19
thru y99.

As the possible values of X and the number of outputs Y gets larger, it
becomes geometrically more wasteful to have so many separate data tables.
Can I consolidate it all into one?

Is that more clear?

 

[flummi]

No, but maybe it explains more to some of the real experts in this
group.

Hans