Calculating the Sum of Averages in a Pivot Table

[arveearv]

Hi:

This problem has been nagging at me for several days. There must be a way to do this, but I'm just not seeing it. (I'm using Excel 2007.)

A landscaping company has given me their data. At the beginning of the year, each of their client signs up for one of several pre-paid plans. As work needs to be done, the company sends in a contractor to do the work, then the company pays the contractor. The company wants to measure the profit and loss from each of their customers.

Here's a sample of the raw data. Each row represents a service provided to a customer. Note that each customer's Pre-Paid Plan field gets redundant: every time the customer is listed, it's always the SAME plan amount associated with that client.
Excel 2007

	A	B	C	D	E	F
Region	Customer	Pre-Paid Plan Amount	Service Call Date	Service	Contractor Cost
East	SIMPSON	$300.00	Waste Removal	$60.00
East	SIMPSON	$300.00	Edging - small	$100.00
East	WEBBER	$550.00	Watering	$85.00
East	WEBBER	$550.00	Waste Removal	$60.00
West	BABCOCK	$550.00	Planting - Med	$120.00
West	ABRAMS	$400.00	Watering	$85.00
West	ABRAMS	$400.00	Mulching	$80.00

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1/19/2012[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4/30/2012[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5/15/2012[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]6/11/2012[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6/7/2012[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1/18/2012[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]6/1/2012[/TD]

</tbody>

short version​

And here's the ideal pivot table that the company wants to see, but that I'm not able to generate:
Excel 2007

	G	H	I	J
Region	Total Pre-Paid Received	Total Contractor Cost	Net Loss/Gain
East	$850 (i.e. Simpson-$300; Webber-$550)	$305	$545 (i.e. $850-$305)
West	$950 (i.e. Babckock-$550; Abrams-$400)	$285	$665 (i.e. $950-$285)

<tbody>
[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

</tbody>

short version​

The "Total Contractor Cost" makes sense. It's just the total amount that the company has paid out, broken down according to the pivot table definition. The problem is generating the Total Pre-Paid Recieved -- the 2 customers in the East Region have paid $300 and $550. So the income from the East Region should be $850, but when I do this as a Pivot table, that value is $3,050 - the sum of all the pre-paid values every time they appear in the data sheet. Then of course there's the problem of generating that final column, which is the difference between the
totals in Columns H and I.

Any ideas?

 

[Smitty]

Welcome to the Board!

A Pivot Table doesn't have that functionality (although you can do it with PowerPivot), but you can add a field to the data:

Count Unique Items in Excel Pivot Table

HTH,

 

[arveearv]

Wow, Smitty! Thanks.

OK, so from the link you provided, I modified the formula to match my data: (=IF(SUMPRODUCT(($B$2:$B2=B2)*($C$2:$C2=C2))>1,0,1). I don't know how it works (yet) but it works, so I have put a marker of "1" in my source data for every first occurrence of a unique value. Great.

So, when I select for the unique values only, I get this pivot table so far:
Excel 2007

	G	H	I	J
Row Labels	Sum of Pre-Paid Plan Amount
East
West
Grand Total

<tbody>
[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]

[TD="align: right"]850[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]

[TD="align: right"]950[/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1800[/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

short version​

Column "I" looks great. And now I want to add columns that are based on all the values (not just the unique ones), as in Column "I" in my ideal pivot table in my original post. Will this be possible in the same pivot table?

Thanks again.

 

[Smitty]

This is probably the closest you would get with a normal pivot table:

Excel 2012

	A	B	C	D
Row Labels	Pre-Paid Plan Amount	Contractor Cost	Net Loss/Gain
East
SIMPSON
WEBBER
West
ABRAMS
BABCOCK
Grand Total

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] $1,700.00 [/TD]
[TD="align: right"] $305.00 [/TD]
[TD="align: right"] $1,395.00 [/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]$600.00 [/TD]
[TD="align: right"]$160.00 [/TD]
[TD="align: right"]$440.00 [/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]$1,100.00 [/TD]
[TD="align: right"]$145.00 [/TD]
[TD="align: right"]$955.00 [/TD]

[TD="align: center"]7[/TD]

[TD="align: right"] $1,350.00 [/TD]
[TD="align: right"] $285.00 [/TD]
[TD="align: right"] $1,065.00 [/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]$800.00 [/TD]
[TD="align: right"]$165.00 [/TD]
[TD="align: right"]$635.00 [/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]$550.00 [/TD]
[TD="align: right"]$120.00 [/TD]
[TD="align: right"]$430.00 [/TD]

[TD="align: center"]10[/TD]

[TD="align: right"] $3,050.00 [/TD]
[TD="align: right"] $590.00 [/TD]
[TD="align: right"] $2,460.00 [/TD]

</TBODY>

Sheet4​

With a Calculated Field for Net Loss/Gain. Note that this method wouldn't require an intermediate column in the data.

 

[arveearv]

Hi:

Hmm... It makes sense, but the Pre-Paid Plan column is wrong because it assumes that the customer paid for a full-year plan on EACH contractor visit. We'd need a table that somehow contains Column "I" of the example in my previous post, and Column "C" in your reply above.

What if we generated this table (where "1" represents the first unique occurrence of a customer):
Excel 2007

	A	B	C	D	E	F	G
Column Labels
Sum of Pre-Paid Plan Amount	Sum of Contractor Cost	Total Sum of Pre-Paid Plan Amount	Total Sum of Contractor Cost
Row Labels
East
SIMPSON
WEBBER
West
ABRAMS
BABCOCK
Grand Total

<tbody>
[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]850[/TD]
[TD="align: right"]850[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"]305[/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]160[/TD]

[TD="align: center"]28[/TD]

[TD="align: right"]550[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]145[/TD]

[TD="align: center"]29[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"]950[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]1350[/TD]
[TD="align: right"]285[/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]165[/TD]

[TD="align: center"]31[/TD]

[TD="align: right"][/TD]
[TD="align: right"]550[/TD]
[TD="align: right"][/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]120[/TD]

[TD="align: center"]32[/TD]

[TD="align: right"]1250[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]3050[/TD]
[TD="align: right"]590[/TD]

</tbody>

AlmostIdeal​

...and could then somehow hide columns "B", "D", "E" and "F"? Is that possible in a pivot table?

 

[Smitty]

If you add your column I to the original data set then you can include it in the PT. If you want to hide PT columns and still need the column itself to be in the PT, then it's as easy as just hiding the column.