Pivot table: Count text to decide rate of sale

[MartinL]

I have a data table with 300000+ rows of data so have decided on a pivot table to solve all my problems...(lol)

everything has gone fine until I needed to solve ros (Rate of sale) which is determined by Total Sales / Number of stores a product is sold in.

I have been unable, so far to get the pivot table to tell me the number of stores.

The table looks like this:

Excel Workbook
	A	B	C	D	E
1	Store_Name	Prod_UPC	Prod_Name	Period_End	Sales
2	NEWTON MEARNS	265263	BEANSPROUTS	30-Apr-11	1.00
3	SOLIHULL	265263	BEANSPROUTS	30-Apr-11	1.00
4	PRESTWICH	265263	BEANSPROUTS	30-Apr-11	1.00
5	DOUGLAS	472630	UK RUNNER BEANS LOOSE	30-Apr-11	1.02
6	TOTTENHAM COURT	733106	BROCCOLILOOSE	30-Apr-11	1.03
Sheet2

and it should be taken into consideration that the date range covers 12 weeks. So in this instance 12 can be hardcoded if necessary into a calculated formula.

In total there are 380 store names
and 260 unique products
not every product is sold in every store.

How can I get ROS in my pivot table

Martin

 

[Trevor G]

Have a look at the formula called GetPivotData, there are plenty of examples to see how it works, but I believe it should give you a solution.

 

[MartinL]

Have a look at the formula called GetPivotData, there are plenty of examples to see how it works, but I believe it should give you a solution.

Thanks Trevor<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
That GetPivotData blew my mind, I spent all weekend trying to plough my way through it and came up with zilch. I guess you are quite right in your suggestion, but it didn’t work out for me.<o></o>
<o></o>
Thanks for taking the time to give me the heads up though it was appreciated.<o></o>

I did find a long winded way round it, using two seperate pivot tables and then a lookup between the two.

Martin