+ Reply to Thread
Results 1 to 4 of 4

Excel 2002 Pivot Table calculated field percentage.

  1. #1
    ~Dave
    Guest

    Excel 2002 Pivot Table calculated field percentage.

    I have the following pivot table format:
    Please note that the numbers are calculated fields for net debit and credits.

    (Column)
    Year 1998 % 1999 % 2000 % Total
    (Row)
    Accounts
    xxxx $456 ?? $552 ?? $600 ?? $1608
    xxxx
    xxxx

    I would like to calculate the percentage variance for each account (xxxx) per year based upon the previous year.

    Having problems inserting a percentage field using the calculated fields.

    Example, the $456 dollar field is a calculated field derived from the net of the debit and credit fields of the account. The debit and credit fields are hidden in this table.

  2. #2
    Debra Dalgleish
    Guest

    Re: Excel 2002 Pivot Table calculated field percentage.

    Add another copy of the Net field to the data area
    Right-click on its column heading
    Choose Field Settings
    Click the Options button
    From the Show data as button, choose % Difference from
    For Base Field, choose Year
    For Base Item, choose (previous)
    Click OK

    ~Dave wrote:
    > I have the following pivot table format:
    > Please note that the numbers are calculated fields for net debit and
    > credits.
    >
    > (Column)
    > Year 1998 % 1999 % 2000 % Total
    > (Row)
    > Accounts
    > xxxx $456 ?? $552 ?? $600 ?? $1608
    > xxxx
    > xxxx
    >
    > I would like to calculate the percentage variance for each account
    > (xxxx) per year based upon the previous year.
    >
    > Having problems inserting a percentage field using the calculated fields.
    >
    > Example, the $456 dollar field is a calculated field derived from the
    > net of the debit and credit fields of the account. The debit and credit
    > fields are hidden in this table.



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  3. #3
    ~Dave
    Guest

    Re: Excel 2002 Pivot Table calculated field percentage.

    Thank you and I got the data items to appear horizontal,
    but the percentages all grouped between the Total and the years. I need
    the year total then the percentage, then the next year and that years
    percentage. Is there no way to stagger the year with the appropiate %
    variance? I copied the net field and renamed it but it does not appear on
    the field list. The net field is a calculated field.

    "Debra Dalgleish" <[email protected]> wrote in message
    news:[email protected]...
    > Add another copy of the Net field to the data area
    > Right-click on its column heading
    > Choose Field Settings
    > Click the Options button
    > From the Show data as button, choose % Difference from
    > For Base Field, choose Year
    > For Base Item, choose (previous)
    > Click OK
    >
    > ~Dave wrote:
    >> I have the following pivot table format:
    >> Please note that the numbers are calculated fields for net debit and
    >> credits.
    >> (Column)
    >> Year 1998 % 1999 % 2000 % Total
    >> (Row)
    >> Accounts
    >> xxxx $456 ?? $552 ?? $600 ?? $1608
    >> xxxx
    >> xxxx
    >> I would like to calculate the percentage variance for each account
    >> (xxxx) per year based upon the previous year.
    >> Having problems inserting a percentage field using the calculated
    >> fields.
    >> Example, the $456 dollar field is a calculated field derived from the
    >> net of the debit and credit fields of the account. The debit and credit
    >> fields are hidden in this table.

    >
    >
    > --
    > Debra Dalgleish
    > Excel FAQ, Tips & Book List
    > http://www.contextures.com/tiptech.html
    >




  4. #4
    Debra Dalgleish
    Guest

    Re: Excel 2002 Pivot Table calculated field percentage.

    Right-click on the Year button, and choose Order>Move Left.
    The Net field will only appear in the field list once.

    ~Dave wrote:
    > Thank you and I got the data items to appear horizontal,
    > but the percentages all grouped between the Total and the years. I need
    > the year total then the percentage, then the next year and that years
    > percentage. Is there no way to stagger the year with the appropiate %
    > variance? I copied the net field and renamed it but it does not appear on
    > the field list. The net field is a calculated field.
    >
    > "Debra Dalgleish" <[email protected]> wrote in message
    > news:[email protected]...
    >
    >>Add another copy of the Net field to the data area
    >>Right-click on its column heading
    >>Choose Field Settings
    >>Click the Options button
    >>From the Show data as button, choose % Difference from
    >>For Base Field, choose Year
    >>For Base Item, choose (previous)
    >>Click OK
    >>
    >>~Dave wrote:
    >>
    >>>I have the following pivot table format:
    >>>Please note that the numbers are calculated fields for net debit and
    >>>credits.
    >>> (Column)
    >>>Year 1998 % 1999 % 2000 % Total
    >>>(Row)
    >>>Accounts
    >>>xxxx $456 ?? $552 ?? $600 ?? $1608
    >>>xxxx
    >>>xxxx
    >>> I would like to calculate the percentage variance for each account
    >>>(xxxx) per year based upon the previous year.
    >>> Having problems inserting a percentage field using the calculated
    >>>fields.
    >>> Example, the $456 dollar field is a calculated field derived from the
    >>>net of the debit and credit fields of the account. The debit and credit
    >>>fields are hidden in this table.

    >>
    >>
    >>--
    >>Debra Dalgleish
    >>Excel FAQ, Tips & Book List
    >>http://www.contextures.com/tiptech.html
    >>

    >
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1