Weird Calculated Field behavior in a Pivot table in Excel

Intro: I wanted to add an index to a pivot table
I wanted to add an index number (1, 2, 3, ...) to the rows of my pivot table. After some experimenting, I ended up adding a column to my source table (`Day Count` in my example) that counts the unique values of `Day`. I calculated it with the following array formula in A2: `=SUM(1/COUNTIF(\$A\$2:A2;\$A\$2:A2))` (Ctrl+Shift+Enter for the array formula) and pasted it down the table. I added the index (not shown in the picture, but I took the Maximum value of `Day Count`, which resulted in and index).

Great! Everything merry so far. Calculated field strangeness
I wanted to use the `Day Count` in a calculated field, but that gave some strange results. I calculated `=MAX('Day Count')` as calculated field (see screenshot), but instead of 1 (for day A) and 2 (for day B) I got 4 and 6.

I see that 4 is the sum of all day count's for day A and 6 is the sum of all day count's for day B. So I added another test: I created a calculated field that returns 1 (`=1`). Now I would expect that this would then return 4 and 3, but it returns 1 and 1. Now that seems inconsistent to me.

My guess as to what's going on
I think somehow the array formula is expanded during the calculation of the Pivot table. But that's unexpected and possibly an bug in Excel, right?

My question is how to explain the observed behavior. Am I misinterpreting the results or is this a possible bug in Excel?

Your pivot table is showing the Sum of Max DayCount. Max DayCount is calculated for each record and summed, so this behavior is what should be expected.

There are four records for A, and the value of Max DayCount for each A record is 1. The sum of Max DayCount is thus 1 + 1 + 1 + 1 = 4.

There are three records for B, and the value of Max DayCount for each B record is 2. The sum of Max DayCount is thus 2 + 2 + 2 = 6.

• Yeah. That's what I thought. But then: why is "Sum of One" not 4 and 3 but 1 and 1? – agtoever Mar 25 '15 at 17:17
• If you change the value field settings to Count, you'll see that One does not behave as you expect, since the count is 1 for each field. I think that since the value of this constant calculated field does not rely on info from your data, it is not calculated for each record but is instead treated as one constant value. In other words, it is not really a calculated field but rather a constant. – Excellll Mar 25 '15 at 17:42

You don't need a calculated field for this. When you put the `Day Count` field in the Values area of the pivot table you can change it from summing the value to only including the maximum value.

To do this, right-click on cell E2 (in your pivot table) and choose `Summarize Values By` and select `Max`

• I know, but in my case, I need the index (Day Count) for further calculation. – agtoever Mar 25 '15 at 17:18
• hmmm... I would suggest that you change your question to include what you are ultimately trying to do. You may find that the way you are approaching this intermediary step could be improved/changed to better suit your end goal. – guitarthrower Mar 25 '15 at 19:54
• I was trying to solve this SU question. To calculate the "grand average", you need to divide the grand total by the number of items in your pivot table. That's how I started with the index numbers and stumbled on this weird phenomenon. – agtoever Mar 26 '15 at 9:19
• By "Number of items in your pivot table" you mean distinct number of items? Which for purposes of your data above, the Grand Average would equal 3.5? (7 line items/2 distinct items in Day column) – guitarthrower Mar 26 '15 at 15:36
• No. In the Pivot. In my example, the "grand average" of DayCount would be 1,5: (1+2)/2. In the "Grand total" row, I want the average value of the values that are in the column above. Not the average of the underlying values in the source table: (1+1+1+1+2+2+2)/7... – agtoever Mar 26 '15 at 15:43