PivtolTable_x2 Row label filters? -- XL2010, Win7

[whitesabbathica9]

Excel Pros,

I am trying to consolidate some data that I have divided into two PivotTables into just one, but I'm unsure how to go about doing it. Below is some random data for reference.

What I'm trying to do is get the average of Val2 using two different criteria:
1) Avg of Val2 in groups when Val1 <= 100
2) Avg of Val2 in groups when Val1 > 100

Please see data and example solutions below:

Table Data:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]GROUP
[/TD]
[TD]ID
[/TD]
[TD]Val1
[/TD]
[TD]Val2
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]110[/TD]
[TD]0.15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]120[/TD]
[TD]0.10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]125[/TD]
[TD]0.20[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]105[/TD]
[TD]0.05[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]90[/TD]
[TD]0.10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[TD]95[/TD]
[TD]0.10[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD]105[/TD]
[TD]0.20[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2[/TD]
[TD]90[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]90[/TD]
[TD]0.20[/TD]
[/TR]
</tbody>[/TABLE]

Desired PivotTable Solution:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]GROUP
[/TD]
[TD]Avg<=100
[/TD]
[TD]Avg>100
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]- -[/TD]
[TD]0.15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]0.10[/TD]
[TD]0.05[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]0.225[/TD]
[TD]0.20[/TD]
[/TR]
</tbody>[/TABLE]


I've only been able to do one average or the other, but not both in the same PT. Is there a way to accomplish this? I appreciate any assistance.

 

[Jerry Sullivan]

You could use Grouping to do that.

Organize your report like this:
Row Labels: GROUP (your field name- not to be confused with the Grouping Function).
Column Labels: Val1
Data Values: Val2 (summarized by Average)

To group the column labels, Right-click on any one of the Column Labels > Group... >
In the Grouping dialog:
Starting at: 100
Ending at: 100.01
By: 0.01

The data will be summarized and grouped as you wanted.
You can edit the labels to match yours.
(Avg<=100; Avg>100)

 

[whitesabbathica9]

Very nice, Jerry! It consolidated them perfectly. Thank you for your help.