Pivot Table Calculated Field Formula Limitations

R

RKN

New Member
Joined
May 5, 2010
Messages
20
I created a calculated field in a pivot table with the intent of creating a custom formula for that field which would calculate an average of 40+ other pivot table fields. When i go to the "Pivot Table ->Options->Formulas->Calculated Field" window and enter the formula as follows Excel limits me to 255 characters:
Average ('field 1', 'field 2', etc.)

When I tried using a colon in the formulas to indicate a range of fields to be averaged Excel would not accept the formula:
Average ('field 1':field 40')

Any ideas on how I can accomplish this?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
An average is just the sum divided by the count.

If you are running into too many variables, can you divide your sums into, say, a subtotal of the first 20 and then the last 20? Then you sum these two to get the sum of all 40.

FIRSTGROUP=field1+field2+...+field20 then subtotal by summing them
SECONDGROUP=field21+...+field40
TOTALSUM = FIRSTGROUP+SECONDGROUP

Take the same approach for the counts, and then divide at the end.
 
Upvote 0
It may be possible to resolve this at some earlier stage in your source data (?). Can you do some preliminary staging of data before the final pivot?

I don't know if PT calculated fields can use other calculated fields (probably not) - though the above seems a great try anyway. I could be wrong.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Calculated Field Coming Back as Zero
Replies
4
Views
276
biotbravo
B
B
  • Question
Calculated Field in pivot table difficulty
Replies
2
Views
264
bluefish44
B
A
  • Solved
Calculated Field
Replies
5
Views
117
Alex Blakenburg
A
D
  • Question
Calculated Field in Pivot
Replies
2
Views
289
daveyc18
D
A
  • Question
Calculated Pivot Table Field
Replies
1
Views
417
RoryA
RoryA

Forum statistics

Threads
1,214,520
Messages
6,120,013
Members
448,935
Latest member
ijat

Share this page

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
Back
Top