Basic Question - Pivot Table Div/0 issue - Excel 2010

Hi Forum!

There's some great resources on this site. I'm looking forward to poking around and increasing my Excel knowledge.

I'm running into a very basic problem with Pivot Tables (I'm definitely new to Excel for this type of data formatting). I have the information I need but every time I think I've got the right things in the right places to get an average I'm dividing by zero and can't get the answer.

Here is what I have:

I have a table with 2 simple columns:

• User IDs
• Event

The Event is when a user purchased either item 1, 2 or 3.

User	Event
A	purchase item 1
A	purchase item 2
B	purchase item 1
C	purchase item 1
C	purchase item 3

My pivot table has the User IDs running down the rows and the event in the columns. Then I dragged the Purchases field to the Values.

This gives me a simple chart that tells me what User ID has purchased what item, and how many total purchases they made (of the 3 items possible). Perfect.

User	Purchase 1	Purchase 2	Purchase 3	Total
A	1	1		2
B	1			1
C	1		1	2

Now my problem here is that I'm trying to find out what the average number of purchases is for all User IDs. I can do this manually by looking at the table as is. It gives me a grand total of the number of purchases made. And then I simply divide that by the number of users with a calculator. In my case (for my original document) I get an average of about 2.5 purchases per user.

However I want the pivot table to generate this total for me without me having to do it manually.

Every time I try to change the values to Average I get a divide by zero.
Every time I try to add another Purchases and try to divide the two together I get weird results or divide by zero.
I'm not sure how to sort the data properly in the pivot table to get what I'm looking for!


I hope I've made sense here. I would love some help from the experts. I have a feeling this is a very basic question, but I can't seem to wrap my simple head around it! (I'm comfortable in Excel, but I've just started using pivot tables and this kind of data organization)

I think you're gonna have a problem with that since pivot tables don't do distinct counts. can you use PowerPivot?

Example_pivot.xls

Thanks for the replies!

Attached above is a sample of some of the data.


You can see I have a number of Users who have purchased item 1 2 or 3. What I am trying to get is the steps to figure out is what is the average number of purchases per user.

Edit: First uploaded the wrong xls. This one is correct (example_pivot).