Results 1 to 4 of 4

20070524, 22:52 #1
 Join Date
 Jan 2007
 Posts
 123
 Thanks
 13
 Thanked 1 Time in 1 Post
TABLE function in array formula  help decipher p (Excel 2002)
I have encountered a finance spreadsheet which uses the following formula (note the curly brackets)
{=TABLE(,B25)}
copied down 10 rows, each returning a different value.
The reference is always to B25, despite the lack of a dollar sign.
(The column heading is standard deviation; the 10 rows are cumulative percent in increments of 10%; and cell b25 is the percentage weighting of one of two subportfolios which make up a combined portfolio. The sheet is attached).
Does anyone know what, in plain English, is this formula doing?

20070524, 23:21 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: TABLE function in array formula  help decipher p (Excel 2002)
There is only one formula for the range I27:J37.
The formula was created by the Data  Table menu option. This option lets you create a "whatif" table that shows the result of varying one or two arguments in a formula. In this example, only one argument varies.
The formulas that are used are in cells I26 and J26, and the varying values are in H27:H37.
The range H26:J37 was selected, then Data  Table.
In the dialog that appeared, the row input cell was left blank, and B25 was entered in the column input cell.
Finally, OK was pressed.
In the cells in I27:J37, the formulas from I26 and J26 are evaluated, substituting the value of the cell in column H for the value of B25. So for example in I30, the formula from I26 is taken, but where this depends on cell B25 (0.5), the value of H30 (0.3) is substituted.
The formula could also have been created as follows:
Select the range I27:J37.
Enter the formula =TABLE(,B25) (without brackets around it).
Confirm with Ctrl+Shift+Enter instead of just Enter.
This makes the formula into an array formula, indicated by the brackets around it.
You can only edit or delete the formula by selecting the entire range I27:J37. If you try to edit or delete the formula in a single cell or a subrange, you'll get an error message.
More info in the help subjects about Data Tables and about Array Formulas.

20070524, 23:37 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: TABLE function in array formula  help decipher p (Excel 2002)
Also see BET: Microsoft Excel Data Analysis  Data Table  One Variable for an explanation and example.

20070524, 23:48 #4
 Join Date
 Jan 2007
 Posts
 123
 Thanks
 13
 Thanked 1 Time in 1 Post
Re: TABLE function in array formula  help decipher p (Excel 2002)
Thanks Hans. I feel pretty stupid  I have actually used data tables, I just totally forgot what the cell formulas looked like  doh!