Using Data Table
 

Hi

Am working on practice excerice from MOS Excel 2003 Expert Exam. The problem
am having is understanding the instructions and asks the following...

Example E03E-1-7, page 343 is...

'Open the UseDataTable workbook. In Cell D9, enter the following formula:
=PMT(B5/12,B6,B4). Then enter the following loan terms in E9:H9:' entering
values in those vells. 'In cells D10:D15:' asks to enter more vaules. Then
use a data table to show the matrix that results when the formula is
calulated using each of the two sets of variables.

Q) When it asks.. 'In the column input cell box, enter reference to the
input cell for the varying values listed in the column.' Also asks me the
same for the rows as well. I don't really know what it means by that. Can
someone explain differently, for exanple when it says enter reference what
does it mean.
--
Thanks for your for your help and time

.. 'In the column input cell box, enter reference to the
input cell for the varying values listed in the column.'
Also asks me the same for the rows as well...

Sounds like what you're looking for is pertaining to a 2 variable data
table. The "Column input cell" & "Row input cell" boxes are required inputs
which appear in the "Table" dialog when you select a range and then click
Data Table. The 2 inputs to be entered in the boxes are the actual
precedent cell references, for e.g. A2, B2, assuming A2 and B2 are the 2
precedent cells (i.e. the 2 variables of interest) used to compute the final
what-if results.

For an example set-up of a 2 variable data table which reads both row and
column inputs to output what-if results,
try: http://tinyurl.com/d9gfv

For an example set-up of a one variable data table,
try: http://tinyurl.com/562dj

And you could also type: data tables
in Excel's help for more info
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Is it true that the input cell (the one whose value changes according to
row/collum value in order to calculate different results) cannot be in
another sheet ?

I tried without success but I'd like a confiration from your side

If it is true, is there a workaround ?

Thanks a lot

Paolo

Italy


--
kayard
------------------------------------------------------------------------
kayard's Profile: http://www.excelforum.com/member.php...o&userid=24716
View this thread: http://www.excelforum.com/showthread...hreadid=380392

"kayard" wrote:
Is it true that the input cell (the one whose value changes according to
row/collum value in order to calculate different results) cannot be in
another sheet ?

I tried without success but I'd like a confiration from your side

If it is true, is there a workaround ?

AFAIK*, yes, the data table has to be set-up in the same sheet. One
workaround would be simply to create a mirror in another sheet, say Sheet2
via using link formulas, e.g. In B2: =Sheet1!B2, then filled across & down
to populate the same range size as the source data table created in Sheet1

*Except perhaps only for this kind of instance where creating data tables
for randomization purposes will work in a separate sheet.
See: http://tinyurl.com/9o66f
where the data table is set-up in another Sheet2
with the source data/formulas in Sheet1
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----