Ratio of Count againts Sum - Pivot Table

[MarkCBB]

Hi there,

I have a pivot table that has the following populated:
Row = Week 1 to Week 5
Column = Values
Values = Count of Customer, Sum of Sales

I am trying to establish the ratio between the number of customers and the number of sales on a Pivot Table, and what is the best way to display the relation?

Info: Using Excel 2010, The data is dynamic and updates daily. The end result will be a chart.

Can anyone assist?

 

[MarkCBB]

The only way I know how to do it is to create a table that is linked to the Count and SUM cells from the Pivot Table and them add a new column that divides to get the ratio. But I would really like to know how to do create it on a Pivot Table.

Any Ideas'?

 

[Matty]

You can use formulas within Pivot Tables. Have a look at the Calculated Field and Calculated Item options.

This may give you a start:

http://www.contextures.com/excel-pivot-table-calculated-field.html

Matty

 

[MarkCBB]

Thanks Matty,

But I think I am doing something wrong. Here is the formula I am using in the calculate field1 =SUM(TOTAL)/COUNTA('CUSTOMER NAME')

Total = Value
CUSTOMER NAME = Text

for some reason I get the same result as the TOTAL field? Is it the text that is the problem?

 

[Matty]

Could you post a small example of the raw data used by your PT?

I will then have a look to see if I can get something working for you.

Matty

 

[MarkCBB]

Hi Matty,

Below is a same piece of the data 17 Customers and 159 Sales, so the ratio should be 1 to 9.3.

<TABLE style="WIDTH: 132pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=176><COLGROUP><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 5120" width=120><COL style="WIDTH: 42pt" width=56><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 90pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 12pt; COLOR: white; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl70 height=16 width=120>CUSTOMER NAME</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 42pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl70 width=56>TOTAL</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 height=16>ABBY'S</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 align=right>24</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 height=16>EASY CORNER</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 align=right>8</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 height=16>SILVER SHOP</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 align=right>10</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 height=16>SUNRISE</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 align=right>20</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 height=16>TUCKS LUP</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 align=right>8</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 height=16>DUKUZA</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 align=right>30</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 height=16>SILVER SHOP 2</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 align=right>10</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 height=16>THOKO</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 align=right>8</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 height=16>DEDE</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 align=right>10</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 height=16>THETHO</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 align=right>20</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 height=16>DANIEL</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 align=right>10</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 height=16>FATS</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 align=right>20</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 height=16>WOZA MINI MARKET</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 align=right>10</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 height=16>MAOLENG</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 align=right>10</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 height=16>MBATHA</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 align=right>15</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 height=16>PINK HOUSE</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl71 align=right>24</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 height=16>MASHUDU</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DCE6F1 none; text-underline-style: none; text-line-through: none" class=xl71 align=right>12</TD></TR></TBODY></TABLE>


Thank you for assisting me with this. its been driving me crazy.

 

[MarkCBB]

Is that enough Data or do you need more?

 

[Matty]

Hi,

Is that a snippet of your raw data or of Pivot Table itself?

I also see 17 customers and a total of 249, not 159.

Could you illustrate what you would like the Pivot Table to display in terms of the ratio you mention? I aren't 100% clear on this yet.

Matty

 

[MarkCBB]

HI Matty,

Sorry about that I was really tierd when I sent that. it is 249 (I hit the 1 instead of the 2 and the 4 instead of the 5 on the keybored).

The data is from the table not the pivot.

Below is the pivot table I am trying to edit with the calculation.

<TABLE style="WIDTH: 358pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=477><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 4309" width=101><COL style="WIDTH: 153pt; mso-width-source: userset; mso-width-alt: 8704" width=204><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2730" width=64><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 4608" width=108><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 76pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 12.75pt; COLOR: white; FONT-SIZE: 9pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl73 height=17 width=101>DRIVER</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 153pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl68 width=204>(All)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: black; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl79 width=64> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 81pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=108></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=17> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: black; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: black; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: black; HEIGHT: 12pt; COLOR: white; FONT-SIZE: 9pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl74 height=16>Weeks </TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: black; COLOR: white; FONT-SIZE: 9pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl75>Count of CUSTOMER NAME</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: black; COLOR: white; FONT-SIZE: 9pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl75>Sales </TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: black; COLOR: white; FONT-SIZE: 9pt; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl76>Ratio </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 12pt; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl69 height=16>Week 49</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" align=right>570</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" align=right>16678</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl77 align=right>0.061674432</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 13.5pt; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl69 height=18>Week 50</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" align=right>1252</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" align=right>23491</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl77 align=right>0.052121145</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 12pt; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl69 height=16>Week 51</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" align=right>1498</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" align=right>25329</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl77 align=right>0.021618858</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 12.75pt; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl69 height=17>Week 52</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" align=right>1618</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" align=right>25895</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl77 align=right>0.020523538</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 12.75pt; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl69 height=17>Week 53</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" align=right>1444</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" align=right>18364</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: #dce6f1; FONT-SIZE: 9pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl77 align=right>0.016980497</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; FONT-FAMILY: Calibri; BACKGROUND: black; HEIGHT: 13.5pt; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl71 height=18>Grand Total</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: black; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl72 align=right>6382</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: black; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl72 align=right>109757</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: black; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 1pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl78 align=right>0.025910933</TD></TR></TBODY></TABLE>


Let me know if this infomation is sufficent. thanks

 

[Matty]

Hi,

I'm still struggling to understand how all of this is pieced together.

In your pivot example, you're showing a count of 570 customers in week 49 for 16678 sales, and this has a ratio of 0.061674432. How is this so?

I would really like to help, but I'm stuggling given the data provided thus far. Could you work up a small example with all the Columns in your real data, and I will try to help.

Matty