Pivo Table Calculated Field Mis-Calculating. Why?

[Mattdaddy1]

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

 

[joeu2004]

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.

 

[RoryA]

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]

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