Grouping and Average in a Pivot Table

Question

Grouping and Average in a Pivot Table

Anonymous

I have a table with dates in one column and values in the next several columns. I'd like to have a pivot table group the dates by year and have the following columns list the average of the data. So in the sample table the result would be "2011 Red=12.67 and White=68" and "2010 Red=23.34 and White=80.34"

Date Red White

1/12011 13 100

2/1/2011 10 30

3/1/2011 15 74

1/1/2010 25 75

2/1/2010 30 101

3/1/2010 15 65

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

0 additional answers

Answer 1

Hi,

Try this

Drag dates to the row arew, Red to the Data area and White to the Data area
Right click on the first date under the date column and select Group. Uncheck the default month and select year
Right click on the first number under "Red" in the pivot and under Summarise by Average
Right click on the first number under "White" in the pivot and under Summarise by Average

Hope this helps.

Answer 2

Click anywhere in the data.

Activate the Insert tab of the ribbon.

Click PivotTable.

Specify the location for the pivottable, then click OK.

In the Field List, drag Date to the Row Labels.

Drag Red to the Values area.

Click the dropdown in the field, and select Value Field Settings...

Right-click any date in the pivot table.

Select Group... from the popup menu.

Select Years and deselect everything else, then click OK.

Select Average as summarize function, then click OK.

Do the same for White.

Share via

Grouping and Average in a Pivot Table

0 additional answers