Data Table

[Guest]

Hi Group
I purchased a new book, "Microsoft Excel Data Analysis and Business Modeling," which is pretty good. On page 122, the author takes you through a Data Table set up, two-way table. It is pretty complicated, so I will not attempt to explain it here. I guess this is oriented to anyone that might have the book. I actuall copy the worksheet out to another book and close the original, erase the the data in the table, and then run the Data/Table/Row input=E8 and Column input = E7. This is right out of the book and can be verified from the original file, but I am not coming up with the same table

Any ideas

Thanks
David

 

[Max]

Hi, don't have the book ..
but maybe you could experiment
and try re-constructing the data table
along the lines of a simple example set-up below
[for a 2 variable data table] ?

Let's say you have in D7:E9

Var1....1
Var2....2
Result..5

where Result = Var1 + 2 x Var2
i.e. the formula for "Result" in E9: =E7+2*E8

("Result" is an output depending on 2 inputs
: Var1 and Var2)

Put in D12: =E9 (this is the link to the output cell: E9)

Put some values for Var1 vertically down in D1316,
say: 1,2,3,4

Put some values for Var2 horizontally across in E12:H12,
say: 5,4,3,2

Select the table D12:H16

Click Data > Table

Input the settings:
----------------------
For row input: E8
For column input: E7

Click OK

Mask D12 by formatting the font color white,
ie = backgrd color

The 2 variable data table in D12:H16 will calculate the
various outputs for "Result" [in E13:H16] for the combo
values of Var1 (in D1316) and Var2 (in E12:H12)

You can also try inputting / changing the values of Var1
and Var2

The data table will recalculate to give the corresponding
values for "Result"

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----

Hi Group,
I purchased a new book, "Microsoft Excel Data Analysis

and Business Modeling," which is pretty good. On page 122,
the author takes you through a Data Table set up, two-way
table. It is pretty complicated, so I will not attempt to
explain it here. I guess this is oriented to anyone that
might have the book. I actuall copy the worksheet out to
another book and close the original, erase the the data in
the table, and then run the Data/Table/Row input=E8 and
Column input = E7. This is right out of the book and can
be verified from the original file, but I am not coming up
with the same table.

 

[Jerry W. Lewis]

You will likely get more replies if you describe the sheet contents and
procedure, instead of assuming that others have access to the same book.

Hi Group,
I purchased a new book, "Microsoft Excel Data Analysis and Business

Modeling," which is pretty good. On page 122, the author takes you

through a Data Table set up, two-way table. It is pretty complicated,

so I will not attempt to explain it here. I guess this is oriented to

anyone that might have the book. I actuall copy the worksheet out to

another book and close the original, erase the the data in the table,

and then run the Data/Table/Row input=E8 and Column input = E7. This

 

[Max]

Just a bit of clarification on the line:

You can also try inputting / changing the values of Var1
and Var2

means changing the sample values of:

Var1 in D1316

and/or

Var2 in E12:H12

 

[Guest]

Hi Again

I have worked through several simple examples, including a couple that are two variables and they have all worked. It si a very complicated table, but I will try -
Description
(growth rate, yearly- these are the variables that will change
revenue - $100000 .2
expense - $150000 .0
We are looking for a break even point, given the growth rates above, in terms of years

year-> 0 1 2 3........1
revenue +revenue above(100000
expense +expense(150000
breakeven (blank yr 0) =IF(AND(E11<E12,F11>F12),F10,0)Copied across to yr 1

total =IF(SUM(F13:R13)>0,SUM(F13:R13),"No BE")(this formula find the year of break even AND is the single input for the table) See the # 3 upper left corner of tabl

Table
3 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 TO-> .
0.1 6 7 8 9 11 No BE No BE No B
0.11 5 6 7 8 9 12 No BE No B
0.12 5 5 6 7 8 9 12 No B
0.13 4 5 5 6 7 8 9 1
0.14 4 4 5 5 6 7 8 1
(some hidden rows
0.44 2 2 2 2 2 2 2
TO .

This is the table above, I have copied the whole sheet out to another workbook and then closed the original workbook. I erase the "Body" of the worksheet, leaving the variable Row and Column intact, then go through Data/Table/ Row Input /Column Input. The output I get is all 3s in the table. I have tried to swith the Row Input and Column inputs, but I have the original file and know that I have picked the right inputs, or at least I believe I am picking the right cells. Mabe I am expecting too much that someone else might have the book and associated CD, but this is hard to explain. Again the book is "Microsoft Excel Data Analysis and Business Modeling," ISBN 0-7356-1901-8, the file is 47Kb in size, if someone wants to look at it. Analysis pack is installed. I am running Excel 2003

Thanks
David

 

[Max]

Hi David,

Just one thought cued specially from your line:

.. The output I get is all 3s in the table ..

The calculation mode for the book may have been set to:
"Automatic - except tables" (or "Manual")

The "Automatic - except tables" setting is quite
a "normal" setting for sheets containing data tables,
which would otherwise drag Excel's performance down with
their intense calcs / recalcs

Try pressing F9. Does the data table now compute?

To change / check calc mode settings:
-------------------------------------
Click Tools > Options > Calculation tab

Check what's showing under "Calculation"?

Click to set as desired > OK

But note that the calculation mode of each Excel session
is dictated by the calc mode of the first book opened
within the session.

For example, if the first book's calc mode is Automatic,
then the mode for all subsequent books opened in the same
Excel session (i.e. until / unless Excel is exited) will
be Automatic, irregardless of their earlier saved modes.

In your case, probably the file in the CD was saved with
the mode: Automatic except tables, and this was the first
file opened within the same Excel session that you then
proceeded to copy, etc ..
--

Hope the above helps.

Anyway, please send a copy of the file to me at:
xdemechanik <at>yahoo<dot>com

--
Rgds
Max
xl 97
---
Please respond, in newsgroupb
xdemechanik <at>yahoo<dot>com
----

Hi Again,

I have worked through several simple examples, including

a couple that are two variables and they have all worked.
It si a very complicated table, but I will try -

Description:
(growth rate,

yearly- these are the variables that will change)

revenue - $100000 .25
expense - $150000 .05
We are looking for a break even point, given the growth

rates above, in terms of years.

year->

0 1
2 3........13

revenue +revenue above(100000)
expense +expense(150000)
breakeven (blank yr

total =IF(SUM(F13:R13)>0,SUM

(F13:R13),"No BE")(this formula find the year of break
even AND is the single input for the table) See the # 3
upper left corner of table

Table:
3 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 TO-> .2
0.1 6 7 8 9 11 No BE No BE No BE
0.11 5 6 7 8 9 12 No BE No BE
0.12 5 5 6 7 8 9 12 No BE
0.13 4 5 5 6 7 8 9 12
8 10
(some hidden rows)
0.44 2 2 2 2 2 2 2 2
TO .5

This is the table above, I have copied the whole sheet

out to another workbook and then closed the original
workbook. I erase the "Body" of the worksheet, leaving the
variable Row and Column intact, then go through
Data/Table/ Row Input /Column Input. The output I get is
all 3s in the table. I have tried to swith the Row Input
and Column inputs, but I have the original file and know
that I have picked the right inputs, or at least I believe
I am picking the right cells. Mabe I am expecting too much
that someone else might have the book and associated CD,
but this is hard to explain. Again the book is "Microsoft
Excel Data Analysis and Business Modeling," ISBN 0-7356-
1901-8, the file is 47Kb in size, if someone wants to look
at it. Analysis pack is installed. I am running Excel 2003.

 

[Guest]

Hi Max
You got it, thanks. The autocalc was off! I am sure I had pressed F9, during the testing, but maybe not.
Thanks again
David

 

[Max]

You're welcome!

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----

Hi Max,
You got it, thanks. The autocalc was off! I am sure I had pressed F9,

during the testing, but maybe not.