I am working on a pivot table and trying to recreate something I did last
month. I am having two basic problems. Any help is appreciated!

I want to group by month to list the average and the maximum. The data is
llike the following:
Date Val
12/29/2006 1
12/30/2006 2
12/31/2006 3
1/1/2006 5
1/2/2006 7
1/3/2006 9

What I want the pivot table to calculate is the following:
Data
Month-Year Max of Val Average of Val
Dec 2005 3 2
Jan 2006 9 7

Does anyone have any ideas? I was able to do the following last month, but I
do not know how I got (1) the year as a field and (2) grouping by month.
Data
Year Month Max of Val Average of Val
2005 Dec 3 2
2006 2006 9 7

Thanks!

1) Add a column for month and year using this formula
=DATE(YEAR(A2),MONTH(A2),1)
2) Format this column as Month and Year
3) Select all of the cells of interest for your pivot table (including
headers)
4) Data|Pivot Table and Pivot Chart Report|
5) Select NEXT and FINISH
6) Put your month and year in DROP ROW FIELDS HERE
7) Drop VAL in the DATA ITEMS field twice.
8) Right click on Sum of Val to FIELD Settings
9) Change this to MAX
10) Right click on Sum of Val2 to FIELD Settings
11) Change this to Average
12) CLICK on the "DATA" pulldown and move it to the TOTAL field.

"Mike" wrote in message
...
I am working on a pivot table and trying to recreate something I did last
month. I am having two basic problems. Any help is appreciated!

I want to group by month to list the average and the maximum. The data is
llike the following:
Date Val
12/29/2006 1
12/30/2006 2
12/31/2006 3
1/1/2006 5
1/2/2006 7
1/3/2006 9

What I want the pivot table to calculate is the following:
Data
Month-Year Max of Val Average of Val
Dec 2005 3 2
Jan 2006 9 7

Does anyone have any ideas? I was able to do the following last month, but
I
do not know how I got (1) the year as a field and (2) grouping by month.
Data
Year Month Max of Val Average of Val
2005 Dec 3 2
2006 2006 9 7

Thanks!

In the pivot table, put the Date field in the Row area
Right-click on the Date field button
Choose Group and Show Detail Group
From the Group by list, select Months and Years
Click OK

Mike wrote:
I am working on a pivot table and trying to recreate something I did last
month. I am having two basic problems. Any help is appreciated!

I want to group by month to list the average and the maximum. The data is
llike the following:
Date Val
12/29/2006 1
12/30/2006 2
12/31/2006 3
1/1/2006 5
1/2/2006 7
1/3/2006 9

What I want the pivot table to calculate is the following:
Data
Month-Year Max of Val Average of Val
Dec 2005 3 2
Jan 2006 9 7

Does anyone have any ideas? I was able to do the following last month, but I
do not know how I got (1) the year as a field and (2) grouping by month.
Data
Year Month Max of Val Average of Val
2005 Dec 3 2
2006 2006 9 7

Thanks!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html