software

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,

TimThe quot;Data gt; Tablequot; 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:
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 gt; 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 gt; 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 gt; Options gt; Calc tab gt; Check quot;Automatic, except tablesquot;* gt; OK
(*or check quot;Manualquot;)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
gt; wrote in message oups.com...
gt; I have a model that takes a whole range of inputs to give you a final
gt; amount you can borrow. I would like to use something like the
gt; {=TABLE(,C3)} formula that would enter a number of data elements into
gt; this model.
gt;
gt;
gt; ie:
gt;
gt; PAYE Income Rental Income Business Income Amount can
gt; Borrow
gt; $50k $10k -
gt; (Plugs these three into model)
gt; $100k - $50k
gt; same again
gt; etc....
gt;
gt;
gt; I guess I can write a VB macro that does this line by line, but i'd
gt; rather work smarter...
gt;
gt; Thanks,
gt;
gt; Tim
gt;