[XL07] Pivot Table - Divide one field with the count of another one

[Gesu`]

Hello all,

First of all a big thank you for all the help this board has given me as a lurker
Now I've small issue and I thought I could finally register and ask for help directly. I searched for an answer already but I couldn't find anything, probably 'cause the terms I searched for are too vague.

My problem is related to a formula to add in a pivot table, basically I'd like to know how to divide one field with the count of another field.
In my original table I have three columns with, let's say, names of costumers, country, and the money they spent.
What I want to do is to show, in the pivot table, how much a costumer of a certain country spends on average.
I can't figure out what function exactly I should insert in "Formulas | Calculated Field", I guess I need to insert something like "=Money spent/Count of costumers", but I can't find out how to put the count there.

I hope I have been clear enough (in case I can add an example of data but shouldn't be necessary), and that it's not a FAQ, as I said I already looked for an answer but I couldn't find anything.

Thank you in advance

 

[PJones1]

try

<TABLE style="WIDTH: 308pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=410 border=0><COLGROUP><COL style="WIDTH: 155pt; mso-width-source: userset; mso-width-alt: 3766" width=206><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 1389" width=76><COL style="WIDTH: 96pt" width=128><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 155pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=206 height=20>Sum of Quanity</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 57pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=76> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Price</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Total</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>25</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">8</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=J14/I14</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>52</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">5</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=J15/I15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>76</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">6</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=J16/I16</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>84</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">12</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=J17/I17</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>92</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">4</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">=J18/I18</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Grand Total</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">35</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

 

[baleshst]

If you are using excel 2007 then please do the following
1) click on the pivot table
2) Go on ribbion Pivottable Tools>>Options>>Formulas>>Calculated field
3) In "Insert Calculated Field" box, enter the "Name" appropriately (eg: Average etc...)
4) Formula = (Enter the appropriate formula by selecting the columns from the fields below)
5) Click Ok

Here as you can see

 

[Gesu`]

Hi,

First of all thank you both for your replies!

@PJones1: That's the easiest way but I would like the formula to be embedded in the Pivot Table, in this way I just need to click refresh to have it updated every time

@baleshst: My problem is that I don't know what to put in the "Formula" field there, since I can't simply insert "=money/costumer", but I need a "=money/count of costumers". My problem is how that formula must be formatted (if what I want to do is possible).

I guess I could tweak the original source of data adding a column, but I'd like to find a more elegant solution, if I can.

Thank you again.