# Thread: Pivot Table Insert Calculated Field

1. Since the Pivot Table won't accept the formula in the calculated field, how can I get the formula in the adjacent column to update the RANK's reference range using a macro or some other function? Can OFFSET help? I'm not really clear on how it might (if at all) be used.

Actually, when I first wrote this I had the question at the end. But I don't want to waste anyone's time, so feel free to close this thread at any time. I can't see you so my feelings won't get hurt. And now for the explanation:

I have a report (exported to Excel) of monthly results of over 10k customers to which I have added a field classifying the customers by level or type of activity into types 0, B, S, T and Z. In addition to regular business activity, customers earn or redeem points, all captured in this one monthly spreadsheet. Type Z customers have earned or redeemed points but did not transact typical business activity. Customers may sometimes be awarded points earned as an adjustment or even on a discretionary basis. I would like to identify the customers who have earned or redeemed points but did not transact typical business activity, resulting in an Exception Report.

I have created a pivot table from the data to filter one group, Type Z, for Points Only customers. For the Pivot Table Report Filter I select Type Z and bring Customer and Customer ID into the pivot table Rows. Sum of Points Earned and Sum of Points Redeemed are Values in the next two columns (columns C & D). In column E (not part of the Pivot Table), labeled "Rank", I have the formula: =IF(OR(RANK(C16,C\$16:C\$403,0)<=20,RANK(D16,D\$16\$403,0)<=20),1,0).

If a customer's Earned or Redeemed points Rank in the Top 20 the formula will post a 1 in the cell, otherwise zero. From there I can do a Data Filter on the column to show only the customers with a 1.

I tried to Insert Calculated Field into the Pivot Table, but get the error "References, names and arrays are not supported in Pivot Table formulas".

The numbers of customers will change from one month to the next. That leaves me with manually revising the RANK formula range each month. I'm trying to automate some/most of the process by developing small chunks of VBA code.

So, by now I'm sure you're asking "what's the question"?

(This is where I originally had the question, which I have since moved to the beginning tf this thread.)

2. Can you provide a data sample with non-proprietary data along with a sample of what the report should look like?

3. The sample file is attached. It's saved as a macro-enabled file but there are none in this version. This file is about one-tenth the original size.

Thanks for checking this out.

Just noticed that I uploaded the file with the RANKing Data Filter (col E) set to ALL. You should change the value to 1 before printing or viewing.

4. Originally Posted by Arcturus16a
........
I would like to identify the customers who have earned or redeemed points but did not transact typical business activity, resulting in an Exception Report.
........

If a customer's Earned or Redeemed points Rank in the Top 20
.........
I made a guess at what might be needed. Attached are two Pivot Tables. Each table shows only top 20 for CustType of "Z", tables are sorted in order of highest to lowest. The tables are using Net Points (Points Earned minus Points Redeemed). One uses straight calculated values, the other uses the "absolute" numbers.

Is this headed in the right direction?

I had to Zip the file to stay within the Lounge's file size limitations.

5. In the raw data there are some things that may create problems when doing any analyses.
There are many CustName with same name but different CustID. In addition, there are many CustID with same ID but different CustName.

see pic below ....
[attachment=90574:Strange data.jpg]

Note that CustName of MANSO has 3 different CustID(s).
Also, CustID 1032 has 13 different Cust Name(s).