Can somebody clarify Pivot Table Calculated Fields for me

TomCon

Active Member
Joined
Mar 31, 2011
Messages
303
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I thought this seemed logical/simple but it does not work as i would have expected

There is one Pivot Field that contains numeric values, both positive and negative.

Lets call this field F1. I made a Calcuated Field in the Pivot Table, lets call it F1Calc.

The formula is:
If (F1>0, 1, 0)

Now, i drag that Field to the column area, the field is "Sum of F1Calc".

In the rows are several row labels, lets say Lab1,Lab2, etc.

I expected to see, for the Lab1 Pivot Table row, the count of F1>0 for lab 1. And for the Lab2 row, the count of F1>0 for Lab2. Etc.

BUT, i do not see this.

The Pivot Table tells me that the Sum of F1Calc is exactly 1 for every row, Lab1, Lab2, etc.

Why is this? If i have a column within the Pivot Table range with the same formula, the Pivot Table will give the correct sum for each label, Lab1,Lab2, etc.

Is there a way to accomplish the above with a Pivot Table Calculated Field, or MUST i do back and alter the data range of the Pivot Table?

And if that is the case, why is a Calculated Field not acting the same as if it were a formula within the Pivot Table range? What is it that Calculated Fields are, if not that?

Thanks if you can explain this all!

Yes, i know the "workaround" is to alter the Pivot Table input range, but i am looking for how to solve this with a Calculated Field within the Pivot Table instead.

Thanks!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Forum statistics

Threads
1,148,255
Messages
5,745,691
Members
423,968
Latest member
LTCASA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top