Pivo Table Calculated Field Mis-Calculating. Why?

Mattdaddy1

New Member
Joined
Mar 8, 2019
Messages
5
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,687
Office Version
2010
Platform
Windows
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/ar...ble-data-6d24cece-a038-468a-8176-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:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,820
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

tcardwell

Board Regular
Joined
Dec 22, 2013
Messages
84
You can always just do the calculation in a new table field and bring this field into your PT.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,321
Messages
5,449,704
Members
405,575
Latest member
Masimo85

This Week's Hot Topics

Top