QUARTILE array 'look-up'?

[Nash]

I have a list of last month's supply orders for our 1900 stores. This list contains the sum of item sales ('August 2015 Order Qty see Query1) for all 1300 + items for all stores.

I have concerns about abuse, so I'm wanting to utilize my ability to set monthly, item based allocation quantities. This is the max # of units per item a store can order within a rolling 30 day period. This value can be calculated, in part, using the QUARTILE function in excel. The formula is QUARTILE(array,quart). My issue is the array field. I need to be able to do the calc for each item, but the array should only include quantities specific to that item. Looking to find a way around manually selecting the array.

The equation is:
Allocation = 1.5*(3rd Quartile-1st Quartile)

EXAMPLE: Using Part Number 10-6000-015 from the "Query1" data set below or the attached Book3.xlsx, I'm looking to do the following, except for each of my 1300+ items.

FormulaValue
1st Quartile =QUARTILE((C14,C22,C27,C42,C48,C54,C59,C67,C72,C78,C85,C91,C98),1)2
3rd Quartile=QUARTILE((C14,C22,C27,C42,C48,C54,C59,C67,C72,C78,C85,C91,C98),3)4
Allocation=1.5*(3rd Quartile-1st Quartile)3

Basically, I want to put together a list of part numbers and allocations similar to the following. The final product should look something like:

Part Number Allocation
10-6000-015

3​

10-6000-016
10-6000-017
10-6100-000
10-6100-004

Sample reference data:
Query1
Column
Row A B C
1 Store # Part Number August 2015 Order Qty
2 4 10-6000-004 1
3 4 10-6000-005 1
4 4 10-6000-011 1
5 4 10-6000-014 3
6 7 10-6000-005 1
7 7 10-6000-011 2
8 7 10-6000-013 2
9 7 10-6000-014 2
10 8 10-6000-014 3
11 10 10-6000-002 2
12 10 10-6000-005 3
13 10 10-6000-011 1
14 10 10-6000-015 4
15 10 10-6000-016 2
16 11 10-6000-003 1
17 11 10-6000-005 2
18 11 10-6000-011 1
19 11 10-6000-012 2
20 11 10-6000-013 1
21 11 10-6000-014 2
22 11 10-6000-015 2
23 12 10-6000-000 1
24 12 10-6000-012 1
25 12 10-6000-013 1
26 12 10-6000-014 1
27 12 10-6000-015 4
28 12 10-6000-016 1
29 13 10-6000-005 3
30 13 10-6000-014 2
31 13 10-6000-016 1
32 14 10-6000-011 1
33 14 10-6000-013 1
34 15 10-6000-005 1
35 15 10-6000-013 1
36 15 10-6000-014 2
37 15 10-6000-016 2
38 16 10-6000-000 2
39 16 10-6000-003 1
40 16 10-6000-007 3
41 16 10-6000-012 1
42 16 10-6000-015 1
43 17 10-6000-000 1
44 17 10-6000-012 1
45 18 10-6000-002 2
46 18 10-6000-005 4
47 18 10-6000-014 1
48 18 10-6000-015 2
49 19 10-6000-000 1
50 20 10-6000-003 1
51 20 10-6000-004 1
52 20 10-6000-013 1
53 20 10-6000-014 2
54 20 10-6000-015 1
55 21 10-6000-011 2
56 21 10-6000-012 1
57 21 10-6000-014 1
58 23 10-6000-014 1
59 23 10-6000-015 1
60 23 10-6000-016 1
61 25 10-6000-000 2
62 25 10-6000-013 1
63 25 10-6000-016 2
64 27 10-6000-000 1
65 27 10-6000-002 2
66 27 10-6000-014 5
67 27 10-6000-015 8
68 28 10-6000-003 1
69 28 10-6000-004 1
70 28 10-6000-005 1
71 28 10-6000-014 6
72 28 10-6000-015 4
73 28 10-6000-016 2
74 29 10-6000-000 4
75 29 10-6000-011 1
76 29 10-6000-016 1
77 30 10-6000-014 1
78 30 10-6000-015 2
79 30 10-6000-016 1
80 31 10-6000-007 3
81 31 10-6000-008 3
82 31 10-6000-011 3
83 31 10-6000-013 2
84 31 10-6000-014 4
85 31 10-6000-015 2
86 31 10-6000-016 1
87 32 10-6000-000 1
88 32 10-6000-004 2
89 32 10-6000-005 1
90 32 10-6000-007 1
91 32 10-6000-015 4
92 32 10-6000-016 2
93 34 10-6000-000 1
94 34 10-6000-007 1
95 34 10-6000-011 1
96 34 10-6000-013 1
97 34 10-6000-014 4
98 34 10-6000-015 2
99 34 10-6000-017 2

I'm probably overcomplicating this, so my apologies and thanks in advance for your help!
Nash

 

[p45cal]

2 things:
1. You should include links at all sites you've cross posted this to. I came across http://www.mrexcel.com/forum/excel-questions/884750-using-array-quartile-function-mixed-dataset.html. Are there any more?
For why, see http://www.excelguru.ca/content.php?184

2. I tried to open your attachment but it's telling me it might be corrupt. Could you re-attach or link to it in the cloud somewhere?

 

[p45cal]

Using your data in the body of your message, see attached and see if it helps.