Calculate the average per customer per year

[MrGibbage]

I think this is more of a math question that an excel question, but maybe someone can help me see the light. I am trying to calculate the average for each widget per customer per year. So, think of it like this. I have a business where I sell widgets. I would like to calculate for a mythical customer in a given year how much that customer would spend on each widget. So I need to calculate for each widget the average cost per customer per year. Then I could add up the costs and figure out what an average customer would spend in an average year for each widget. Sound simple?

Here I have a screenshot of how I tried to do this (unsuccessfully, I should add)

I have recorded the Item, Customer Name, Year and Value. I created the Cust-Year column to help with the first pivot table. I calculate the average for each row in the pivot table. And when I total the average for each widget, I get 26.5, and you can see the values for each individual widget (8.5 for w1, etc).

So I go to check my work and see what the average for each customer. Not a single customer comes close to the 26.5. Again, I thought if I added up for all the widgets the average per customer per year, I would be able to model a hypothetical customer for any given year. But the numbers don't check out.

I think this is one of those "The sum of the averages is not equal to the average of the sums" situations. Am I correct?

Is there a better way to calculate the average of something per this thing and per that?

Here's a link to my spreadsheet if you want to see the real numbers:
http://skip@pelorus.org/pictures/CustomerSalesSample.xlsx

 

[Misca]

A single pivot table should do it: Place the widgets in the row fields, the years in the column fields and the values to the data fields. Then change the data field settings from Sum to Average and you're done. If you want to see average of each of your customers, drop them to the row fields as well and you'll see not only the total average per product but the averages of each of your customers as well.

 

[MrGibbage]

Wow, you are fast. Thanks for the help. Your answer does help (I forgot about the capability to show averages in the data field).
The problem though is I need a single table with two columns: the widget name and the average per customer per year for that widget (of course there can be columns in between). But here can't be extra rows. I am dealing with thousands of widgets, and this table will be used to model future sales. So adding customers to the rows won't help me.

But I do see something interesting. If I take the widgets vs years pivot that you suggested, and divide the Grand Total average for each widget by the number of customers that purchased that widget, and sum those averages up, then I get numbers in the right range (10.8889 vs 8.958 for the customer vs. year pivot above.)

The more I think about this I am pretty sure it is one of those "the sum of the averages is not equal to the average of the sums" sort of things. I just need to think some more about what it is that I really need.