Results 1 to 4 of 4

Thread: Pivo Table Calculated Field Mis-Calculating. Why?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Pivo Table Calculated Field Mis-Calculating. Why?

    Hi all, I have a pivot table with a simple subtraction formula in it but the results being returned are way off of what they should be. The calculated field is in column F and is
    [=IFERROR(E6-B6,"")]. I have include Column G to show what the expected result should be. Why is this happening?

    I know Pivot Tables are beginners stuff, but I honesty have not had that much use for them until now. Please help. Thanks


    Column: B C D E F G (What it should be)
    15 537 51.87 10.4 -64.6 -4.6
    15 271 46.65 5.8 -54.2 -9.2
    15 422 53.08 7.9 -67.1 -7.1

  2. #2
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,335
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pivo Table Calculated Field Mis-Calculating. Why?

    I don't know much about Pivot Tables. But IIRC, PTs are not automatically recalculated, which is one reason why I do not use or recommend PTs.

    Confirmed by the Office support page at https://support.office.com/en-ie/art...6-8b6568ca9be2 , to wit:

    ``By default, PivotTables are not refreshed automatically, but you can specify that the PivotTable is automatically refreshed when you open the workbook that contains the PivotTable.``

    So I suspect that what you see in column F is based on previous values.

    The help page contains instructions for manually refreshing PTs in an open Excel file and for automatically refreshing PTs when an Excel file is opened.

    I found the following instructions with a google search (``how to recalculate pivot table`` without quotes).

    Manually refresh

    1. Click anywhere in the PivotTable.
    2. On the Options tab, in the Data group, do one of the following:
    3. To update the information to match the data source, click the Refresh button, or press ALT+f5.
    4. To refresh all PivotTables in the workbook, click the Refresh button arrow, and then click Refresh All.

    Refresh When File Opens

    1. Right-click any cell in the pivot table.
    2. Click PivotTable Options.
    3. In the PivotTable Options window, click the Data tab.
    4. In the PivotTable Data section, add a check mark to Refresh Data When Opening the File.
    5. Click OK to close the dialog box.
    Last edited by joeu2004; May 20th, 2019 at 11:47 AM.

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,499
    Post Thanks / Like
    Mentioned
    46 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Pivo Table Calculated Field Mis-Calculating. Why?

    I assume that's not the real formula, unless you actually have fields called E6 and B6.

    You should be aware that calculated fields always operate on the SUM of the relevant field, so your calculation is actually SUM(E6)-SUM(B6). Are your other fields set to SUM? If not, that's why the calculation is different.

  4. #4
    Board Regular
    Join Date
    Dec 2013
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivo Table Calculated Field Mis-Calculating. Why?

    You can always just do the calculation in a new table field and bring this field into your PT.

Some videos you may like

User Tag List

Tags for this Thread

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
  •