Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    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?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 "what-if" 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.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  4. #4
    2 Star Lounger
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •