Excel Function :TABLE

Its been a while since I have done some posting. This option in excel is something which I thought would be very useful for many.

Its the TABLE Function in MS Excel. This is especially useful in the following scenarios.

Assume you have a very lengthy calculation to arrive at a value. And you want to see how one (or two) input variables affect the output. One way to do this is to have each calculation in adjacent columns and populate the rows with varying input value. Also, if you want two inputs to be changed, its even more difficult. Using TABLE, you can do this very easily.

I even remember that I had written a whole set of VBA codes to manage what TABLE could do!

Enough of introduction....



(A) How to do a 1D TABLE (one variable is changed).




Step 1: Basic Calculation

Setup your basic calculation with the variable value in separate cell(s).
In the Eg, Input is in [C2] and output is in [C3]


Step 2: Prepare the Table

Prepare the frame for the Table. Different possible values of the variable is arranged in a column ( assume [E3:E10] ). Immediate to the right of these values ( [F3:F10] ) will be the place for the results. Link the result cell of Step1 to the cell just above it [F2] (for eg, =C3).


Step 3: Populate the Table

Select the full table. ( [E2:F3] ).
Go to Data -> Table.
Select the input variable cell as Column Input Cell ([C2])
Select OK.

You are DONE.

(B) How to do a 2D TABLE (two variables are varied).





Step 1: Stays the same.

Step 2: Populate the possibilities of variable 1 in the 1st column and variable 2 as 1st row. The output cell should be linked to the Top-Left Corner.

Step 3: Select the two variable reference cells in the dialog. Click OK and you should be DONE.

If there is some mismatch, please repeat the step 3 with the reference cells interchanged.



Hope its useful.. Any questions?, let me know...