Calculated Field on Pivot Tables

To the best of my knowledge that is not possible. You can combine data items in the sense of aggregating sums or counts, and customize the calculations of their values -- I.e., you can get calculation for apples and oranges, but you can't get "orangpples".

What would you do with an item that doesn't exist in your dataset anyway? What would you count, sum, multiply or divide?

Alex.

Thanks, Alexander. I was hoping to be able to group my values based on the first three characters of a field. I guess I'll just have to create another field for this in the data.

Usually that's all anyone can come up with here and its the recommended course of action ... Pivot tables are in some ways powerful and in some ways limited (they aren't as flexible as you'd hope but they still do a lot that's very useful).

Alex

Hi,

Try adding the new field in SQL as a step in between the source data and the pivot table.

Best to set this up from a new file to avoid memory leak problems. So, close the data file and from a new file create a pivot table using 'external data' functionality. It can be from either the 'external data' option at the first step of the pivot table wizard or, the way I prefer when doing this manually, from menu data, import external data, new database query. If the source data doesn't have named ranges (not dynamic) and you haven't already selected the 'system tables' option, you'll get a message about no visible tables. OK to this and take the 'options' button and then 'system tables' - now worksheet names show. Continue and go into MS Query when you get the chance, now edit the SQL. It is just text. So, it will be something like,
where you will have your field names not field1, etc. And you need to edit it to be, untested,

Continue and finish the pivot table and it now has the desired new field without changing the source data.

The resultant worksheet containing the pivot table can be moved back into the source file and all should be OK.

HTH, Fazza