Calculating our "average order" from sales data set

[lewisgmorris]

Hi all,

I have a dataset which includes. Invoice No, Stock Reference, Qty All stock references are batteries with codes 301 to 399

Is there an easy way to determine the AVERAGE order?

I have tried calculating the average count of each invoice numbers (which is 8) and then then sorting the popularity of each stock reference, selected the top 8 products. Then I have taken the most used qty for each stock reference and created an "average order".

But in my result doesn't seem right.

Any ideas or methods to work this out?

sales data can be found here. https://docs.google.com/spreadsheets/d/1AloOpzrcDYpQGzfh6NVGcM_Akn75rzpU3VMfKw2tbCk/edit?usp=sharing

Lewis

 

[shg]

Is this the type of result you're looking for?

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

I​

[/td][td="bgcolor:#C0C0C0"]

J​

[/td][td="bgcolor:#C0C0C0"]

K​

[/td][td="bgcolor:#C0C0C0"]

L​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#DCE6F1"]Row Labels[/td][td="bgcolor:#DCE6F1"]Sum of Quantity[/td][td="bgcolor:#F3F3F3"]

Per Order​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]301[/td][td]

1343​

[/td][td="bgcolor:#E5E5E5"]

0.3​

[/td][td]K2: =J2/4439[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]303[/td][td]

2512​

[/td][td="bgcolor:#E5E5E5"]

0.6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]309[/td][td]

257​

[/td][td="bgcolor:#E5E5E5"]

0.1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]315[/td][td]

7468​

[/td][td="bgcolor:#E5E5E5"]

1.7​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]317[/td][td]

20961​

[/td][td="bgcolor:#E5E5E5"]

4.7​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]319[/td][td]

10707​

[/td][td="bgcolor:#E5E5E5"]

2.4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]321[/td][td]

42637​

[/td][td="bgcolor:#E5E5E5"]

9.6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]329[/td][td]

4093​

[/td][td="bgcolor:#E5E5E5"]

0.9​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]335[/td][td]

4153​

[/td][td="bgcolor:#E5E5E5"]

0.9​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]337[/td][td]

7175​

[/td][td="bgcolor:#E5E5E5"]

1.6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]339[/td][td]

578​

[/td][td="bgcolor:#E5E5E5"]

0.1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]341[/td][td]

1370​

[/td][td="bgcolor:#E5E5E5"]

0.3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]344[/td][td]

1169​

[/td][td="bgcolor:#E5E5E5"]

0.3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]346[/td][td]

1837​

[/td][td="bgcolor:#E5E5E5"]

0.4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]350[/td][td]

287​

[/td][td="bgcolor:#E5E5E5"]

0.1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]357[/td][td]

10318​

[/td][td="bgcolor:#E5E5E5"]

2.3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]361[/td][td]

1614​

[/td][td="bgcolor:#E5E5E5"]

0.4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]362[/td][td]

11834​

[/td][td="bgcolor:#E5E5E5"]

2.7​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]364[/td][td]

222485​

[/td][td="bgcolor:#E5E5E5"]

50.1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]365[/td][td]

367​

[/td][td="bgcolor:#E5E5E5"]

0.1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]366[/td][td]

285​

[/td][td="bgcolor:#E5E5E5"]

0.1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]370[/td][td]

32807​

[/td][td="bgcolor:#E5E5E5"]

7.4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]371[/td][td]

91717​

[/td][td="bgcolor:#E5E5E5"]

20.7​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25​

[/td][td]373[/td][td]

11090​

[/td][td="bgcolor:#E5E5E5"]

2.5​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

26​

[/td][td]376[/td][td]

415​

[/td][td="bgcolor:#E5E5E5"]

0.1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27​

[/td][td]377[/td][td]

443623​

[/td][td="bgcolor:#E5E5E5"]

99.9​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

28​

[/td][td]379[/td][td]

69438​

[/td][td="bgcolor:#E5E5E5"]

15.6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29​

[/td][td]380[/td][td]

169​

[/td][td="bgcolor:#E5E5E5"]

0.0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

30​

[/td][td]381[/td][td]

1131​

[/td][td="bgcolor:#E5E5E5"]

0.3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

31​

[/td][td]384[/td][td]

2371​

[/td][td="bgcolor:#E5E5E5"]

0.5​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

32​

[/td][td]386[/td][td]

1104​

[/td][td="bgcolor:#E5E5E5"]

0.2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

33​

[/td][td]389[/td][td]

2882​

[/td][td="bgcolor:#E5E5E5"]

0.6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

34​

[/td][td]390[/td][td]

9211​

[/td][td="bgcolor:#E5E5E5"]

2.1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

35​

[/td][td]391[/td][td]

1799​

[/td][td="bgcolor:#E5E5E5"]

0.4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

36​

[/td][td]392[/td][td]

4360​

[/td][td="bgcolor:#E5E5E5"]

1.0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

37​

[/td][td]393[/td][td]

4595​

[/td][td="bgcolor:#E5E5E5"]

1.0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

38​

[/td][td]394[/td][td]

14481​

[/td][td="bgcolor:#E5E5E5"]

3.3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

39​

[/td][td]395[/td][td]

36397​

[/td][td="bgcolor:#E5E5E5"]

8.2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

40​

[/td][td]396[/td][td]

2416​

[/td][td="bgcolor:#E5E5E5"]

0.5​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

41​

[/td][td]397[/td][td]

5217​

[/td][td="bgcolor:#E5E5E5"]

1.2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

42​

[/td][td]399[/td][td]

13921​

[/td][td="bgcolor:#E5E5E5"]

3.1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

43​

[/td][td="bgcolor:#DCE6F1"]Grand Total[/td][td="bgcolor:#DCE6F1"]

1102594​

[/td][td="bgcolor:#E5E5E5"]

248.4​

[/td][td][/td][/tr]
[/table]

(There are 4439 distinct orders)

 

[lewisgmorris]

I was looking more for the average order also relating to lines per invoice. I.E what would an order of 10 lines look like on average? Or what would a 20 line order look like? (what batteries and what qty would be in there?)

Do you know how i might work this out?

 

[shg]

You could easily figure how many line items are on the average order, and the fraction of orders that include each part number, but I don't grok the concept of an 'average order.'

 

[shg]

... short of some exotic cluster analysis.