Pivot Table Stats

[Arrogant-One]

Hi,

I work in a sales job and earn commissions on each sale I make.

I created a pivot table with 3 columns. Date - Commission Earnings - Number of Sales

The table is fantastic, and everyday I work I enter the date, the customer's name, the sale amount and the commission amount.

The pivot table automatically adds up the number of sales I have made, but ignores the fields of customer name and sale amount - which is what I want it to do.

What I would like your help with, however, is adding 3 new fields which automatically update whenever I go to Pivot table Tools - refresh all.

These fields would be:

average sale value - the commission total divided by the number of sales made.

average sales per day - the number of sales made divided by number of days I worked

daily average earned - the commission total divided by the number of days I worked

Can someone please tell me how to add this to my pivot table, or even beside my pivot table so I do not need to always do these manually?

Thanks for your help guys.

 

[Arrogant-One]

Is there anyone who can assist with my query please?

 

[Arrogant-One]

Please?

 

[Arrogant-One]

Pretty Please.

 

[Sandeep Warrier]

Hi,

Not sure how your Pivot Table is structured etc, but do you mean something like


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Date</td><td style=";">Sale Total</td><td style=";">Commission Total</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Average Sale Value</td><td style="text-align: right;;">2.347032</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">28-03-2011</td><td style="text-align: right;;">235</td><td style="text-align: right;;">556</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Average Sales per Day</td><td style="text-align: right;;">219</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">29-03-2011</td><td style="text-align: right;;">218</td><td style="text-align: right;;">484</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Daily Average Earned</td><td style="text-align: right;;">514</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">30-03-2011</td><td style="text-align: right;;">209</td><td style="text-align: right;;">500</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">31-03-2011</td><td style="text-align: right;;">214</td><td style="text-align: right;;">516</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Grand Total</td><td style="text-align: right;;">876</td><td style="text-align: right;;">2056</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M3</th><td style="text-align:left">=GETPIVOTDATA(<font color="Blue">"Commission Total",$G$3</font>)/GETPIVOTDATA(<font color="Blue">"Sale Total",$G$3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M4</th><td style="text-align:left">=GETPIVOTDATA(<font color="Blue">"Sale Total",$G$3</font>)/COUNT(<font color="Blue">$G:$G</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M5</th><td style="text-align:left">=GETPIVOTDATA(<font color="Blue">"Commission Total",$G$3</font>)/COUNT(<font color="Blue">$G:$G</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Arrogant-One]

Hi,

Not sure how your Pivot Table is structured etc, but do you mean something like


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Date</td><td style=";">Sale Total</td><td style=";">Commission Total</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Average Sale Value</td><td style="text-align: right;;">2.347032</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">28-03-2011</td><td style="text-align: right;;">235</td><td style="text-align: right;;">556</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Average Sales per Day</td><td style="text-align: right;;">219</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">29-03-2011</td><td style="text-align: right;;">218</td><td style="text-align: right;;">484</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Daily Average Earned</td><td style="text-align: right;;">514</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">30-03-2011</td><td style="text-align: right;;">209</td><td style="text-align: right;;">500</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">31-03-2011</td><td style="text-align: right;;">214</td><td style="text-align: right;;">516</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Grand Total</td><td style="text-align: right;;">876</td><td style="text-align: right;;">2056</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M3</th><td style="text-align:left">=GETPIVOTDATA(<font color="Blue">"Commission Total",$G$3</font>)/GETPIVOTDATA(<font color="Blue">"Sale Total",$G$3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M4</th><td style="text-align:left">=GETPIVOTDATA(<font color="Blue">"Sale Total",$G$3</font>)/COUNT(<font color="Blue">$G:$G</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M5</th><td style="text-align:left">=GETPIVOTDATA(<font color="Blue">"Commission Total",$G$3</font>)/COUNT(<font color="Blue">$G:$G</font>)</td></tr></tbody></table></td></tr></table><br />

Sir, I thank you very much. You are like the good Samaritan, helping someone who can now help himself. Many many thanks my friend.