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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
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
35,034
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,099,804
Messages
5,470,884
Members
406,733
Latest member
darzu

This Week's Hot Topics

Top