Table Function (Excel 2002)

Topic

New Reply

Hi,
I’m trying to understand someone else’s worksheet and they have this SAME EXACT formula in whole bunch of cells, however the result is different in each cell… it is as follows:
{=TABLE(P1,U7)}
Nothing is in cell U7, so I’m confused at to how it’s calclating the number it comes up with. Anybody have any info on how this type of formula calculates. I could use an example.
Thanks!
Lana

Reply | Quote

Replies

Hi Lana

There is no Table function in Excel, it sounds like a user defined function.

Reply | Quote

Whoops take it back, have a look at:

How to create and use one-input data tables in Microsoft Excel

Thi swill give an explanation

Reply | Quote

The braces { } around the formula indicate that it is an array formula; array formulas can display different values in different cells even though the formula remains the same.

This kind of formula is created using the Data | Table menu option. You start with a normal formula that takes values from a few cells to calculate something.
The Table feature is used for what-if analysis: how does the result of the formula vary when the values of one or two of the input cells are varied.

In the screenshot below, I took a loan payment calculation as example: someone loans $100,000 at 6% interest, to be paid back in 20 years. Cell A5 calculates the yearly payment, using the formula

=PMT(A2,A3,A1)

The amount is negative because amounts you receive are positive, amounts you pay are negative.

What if the interest rate changes, or the duration of the loan?
In the cells below A5, I entered some percentages.
In the cells to the right of A5, I entered some durations.
I then highlighted A5:F12 and selected Data | Table…
I entered A3 as the row input cell – this is the duration.
And I entered A2 as the column input cell – this is the interest rate.
Clicking OK entered the array formula =TABLE(A3,A2) in the “interior” of the selection.
You can read off the yearly payment for a given interest rate and duration.

BTW, if you enter an array formula yourself, don’t type the { } . Confirm the formula with Ctrl+Shift+Enter instead.

Reply | Quote

Thanks Jerry & Hans… these examples help me understand it! I appreciate the tutorial!!
Lana

Reply | Quote