Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Table Function (Excel 2002)

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Table Function (Excel 2002)

    Hi Lana

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

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Table Function (Excel 2002)

    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
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Function (Excel 2002)

    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.
    Attached Images Attached Images
    • File Type: png x.png (11.2 KB, 1 views)

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Table Function (Excel 2002)

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

Posting Permissions

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