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?
Dude! what's your question?
My question is how to explain the observed behavior. Am I misinterpreting the results or is this a possible bug in Excel?