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?

	I​	J​	K​	L​
1​	Row Labels	Sum of Quantity	Per Order​	​
2​	301	1343​	0.3​	K2: =J2/4439
3​	303	2512​	0.6​
4​	309	257​	0.1​
5​	315	7468​	1.7​
6​	317	20961​	4.7​
7​	319	10707​	2.4​
8​	321	42637​	9.6​
9​	329	4093​	0.9​
10​	335	4153​	0.9​
11​	337	7175​	1.6​
12​	339	578​	0.1​
13​	341	1370​	0.3​
14​	344	1169​	0.3​
15​	346	1837​	0.4​
16​	350	287​	0.1​
17​	357	10318​	2.3​
18​	361	1614​	0.4​
19​	362	11834​	2.7​
20​	364	222485​	50.1​
21​	365	367​	0.1​
22​	366	285​	0.1​
23​	370	32807​	7.4​
24​	371	91717​	20.7​
25​	373	11090​	2.5​
26​	376	415​	0.1​
27​	377	443623​	99.9​
28​	379	69438​	15.6​
29​	380	169​	0.0​
30​	381	1131​	0.3​
31​	384	2371​	0.5​
32​	386	1104​	0.2​
33​	389	2882​	0.6​
34​	390	9211​	2.1​
35​	391	1799​	0.4​
36​	392	4360​	1.0​
37​	393	4595​	1.0​
38​	394	14481​	3.3​
39​	395	36397​	8.2​
40​	396	2416​	0.5​
41​	397	5217​	1.2​
42​	399	13921​	3.1​
43​	Grand Total	1102594​	248.4​

(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.