Calculate the average per customer per year

MrGibbage

New Member
Joined
Jun 29, 2009
Messages
3
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,588
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

New Member
Joined
Jun 29, 2009
Messages
3
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,328
Messages
5,510,619
Members
408,806
Latest member
Hunlight

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top