I have a model that takes a whole range of inputs to give you a final
amount you can borrow. I would like to use something like the
{=TABLE(,C3)} formula that would enter a number of data elements into
this model.


ie:

PAYE Income Rental Income Business Income Amount can
Borrow
$50k $10k -
(Plugs these three into model)
$100k - $50k
same again
etc....


I guess I can write a VB macro that does this line by line, but i'd
rather work smarter...

Thanks,

Tim

The "Data Table" menu can be used to set up to output what-if scenarios,
but only up to 2 input variables

To illustrate, here's a simple example for a 2 variable data table
structured along the lines of your post

Sample construct at:
http://www.savefile.com/files/1931667
2 variable data table example.xls

Assume the table below is set-up in A1:C2

Main Inc Other Inc BorrowAmt
$30 $15 $28.50

where in C2 is the base formula to compute BorrowAmt:
=(A2*70%)+(B2*50%)
[ i.e. BorrowAmt = (Main Inc x 70%) + (Other Inc x 50%) ]

Setting up a 2 variable data table to compute BorrowAmt
----------------------------------------
Put in E3: =C2
(just a simple link to the output cell.
mask this cell by formatting the font white)

List some rep values for Other Inc in F3:I3 : 5, 10, 15, 20
Similarly, list some rep values for Main Inc in E4:E8 : 10, 20, 30 ... 50

Select E3:I8, and click Data Table
In the Table dialog, enter:
for Row input: B2
for Column input: C2
Click OK

The data table will compute the BorrowAmt values (F4:I8)
depending on the various Main Inc and Other Inc values
(intersection points)

We could change the listed values in either F3:I3 or E4:E8
and the table will recalc accordingly. Extend to suit ..

Notes:
-------
a. The same formula: {=TABLE(B2,A2)} will appear within F4:I8
Albeit it looks like an array formula, it cannot just
be entered as such. The construct must be done / invoked
via the Data Table steps outlined

b. As data tables (especially large ones) are calc intensive, it's usually
better to switch to semi-auto calc mode (or manual mode), via:
Tools Options Calc tab Check "Automatic, except tables"* OK
(*or check "Manual")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
oups.com...
I have a model that takes a whole range of inputs to give you a final
amount you can borrow. I would like to use something like the
{=TABLE(,C3)} formula that would enter a number of data elements into
this model.


ie:

PAYE Income Rental Income Business Income Amount can
Borrow
$50k $10k -
(Plugs these three into model)
$100k - $50k
same again
etc....


I guess I can write a VB macro that does this line by line, but i'd
rather work smarter...

Thanks,

Tim