Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: pivot tables

  1. #1
    Guest

    Default

    Dear sirs,

    multiplying figures with percentages in a pivot table leads to the right result in the line, but the total total provides a wrong value; example:
    10 * 20% = 2 => o.k.
    20 * 30% = 6 => o.k.

    30 * 50% = 15 => this would be ttl total,
    because the percentages would be added.
    How is it able to hide the ttl of the percentages (50%) and to get the right result of 8 in the end ??
    Many thanks in advance..

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you using a Calculated Field? Where are your percentage values?

  3. #3
    Guest

    Default

    On 2002-02-25 08:57, Mark W. wrote:
    Are you using a Calculated Field? Where are your percentage values?
    yes, it's a calculated field;
    raw data would be
    -a- -b-
    1st line: 10 ! 20%
    2nd line: 20 ! 30%

    does this help ?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-26 00:17, Anonymous wrote:
    On 2002-02-25 08:57, Mark W. wrote:
    Are you using a Calculated Field? Where are your percentage values?
    yes, it's a calculated field;
    raw data would be
    -a- -b-
    1st line: 10 ! 20%
    2nd line: 20 ! 30%

    does this help ?
    Yes, it helps! As it states in the Help topic for "Syntax for calculated field and item formulas in PivotTables", "Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula." You're getting incorrect results because your order of operations isn't correct! To preserve the proper order of operations you need a new data field (in column C) containing the formula, =A2*B2. It's this new field that should be used in your PivotTable.

    [ This Message was edited by: Mark W. on 2002-02-26 06:43 ]

  5. #5
    Guest

    Default

    Thanks Mark,
    it works now,

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Mike T.
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tool have a calculate field that returns percents, but the wind up beng summed.

    I fixed that by adding new fields, but now, I get #DIV. How can I set the Cal formulat to get rid of the #div?

    Mike

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-20 08:12, Henry wrote:
    I tool have a calculate field that returns percents, but the wind up beng summed.

    I fixed that by adding new fields, but now, I get #DIV. How can I set the Cal formulat to get rid of the #div?

    Mike
    If you're calculating percentages using...

    =A1/B1

    ...use this instead...

    =IF(B1,A1/B1,0)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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