Averaging Grouped Data - Excel 2003 PivotTable

WrinkledCheese · 12-09-2006, 12:48 PM

Hey everyone,

I'm fairly new to pivot tables and I'm trying to figure out some things for my boss. But I need to be more fluent in pivot tables to be confident that I will be able to answer her questions when I go to the meeting, which I will let her know when to call.

What I'm trying to do is to average grouped data.

I have an XLS file that has:
Salesperson - name
Order ID - unique ID
Order Date - specific day
Country Origin - US and UK

I set up a pivot table that has a page field of Country Origin. For the rows I have salesperson and order date, grouped into quarter and month, respectively. So for every sales person it shows their sales by month grouped into quarters. There is a total for each salesperson at the end of each salesperson and I have it set to average, by default it gives the average of day by day sales and I would like an average of month by month sales, since I have it grouped that way it doesn't make much sense to give an average day by day.

Is it possible to average based on a grouped field?

Attached is a PNG of the first person showing the average is averaging the day to day sales rather than what I have grouped them by, month by month or even quarter.

Average month by month it seems that the average should be $5732.69 or $5732.68, however you round it.

I would like to get this average without going into the excel spreadsheet or DB and adding a new field that stores by month sales.

WrinkledCheese · 12-09-2006, 01:31 PM

I forgot to mention that there is a Order Amount, which is where I'm getting the sales info. in the first place. I would have edited but I guess there is a time limit...

WrinkledCheese · 12-09-2006, 03:33 PM

I figured it out, it was the way I had the data organized...

Actually I didn't I just got daily averages for the months.

12-09-2006, 03:33 PM	#3 (permalink)
WrinkledCheese Registered User Join Date: Dec 2006 Posts: 4 OS: Win 2k	I figured it out, it was the way I had the data organized... Actually I didn't I just got daily averages for the months. Last edited by WrinkledCheese; 12-09-2006 at 03:54 PM.

12-09-2006, 01:31 PM	#2 (permalink)
WrinkledCheese Registered User Join Date: Dec 2006 Posts: 4 OS: Win 2k	I forgot to mention that there is a Order Amount, which is where I'm getting the sales info. in the first place. I would have edited but I guess there is a time limit...