Using 'array' in QUARTILE Function for a mixed dataset

[wngammill]

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, I'm looking to do the following, except for each of my 1300+ items.

	Formula	Value
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

<tbody>
</tbody>

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

<colgroup><col><col></colgroup><tbody>
</tbody>

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

<colgroup><col width="64" span="4" style="width: 48pt;"></colgroup><tbody>
</tbody>

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

 

[DRSteele]

If I understand correctly, this might help you.

Excel 2012

	A	B	C	D	E	F	G
1	Store #	Part Number	August 2015 Order Qty
2	4	10-6000-004	1		part number	allocation
3	4	10-6000-005	1		10-6000-015	3
4	4	10-6000-011	1		10-6000-016	1.5
5	4	10-6000-014	3		10-6000-017	0
6	7	10-6000-005	1		10-6100-000	#NUM!	not in list
7	7	10-6000-011	2		10-6100-004	#NUM!	not in list

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet29​

Worksheet Formulas Cell Formula F3 =(AGGREGATE(17,6,$C$2:$C$99/($E3=$B$2:$B$99),3)-AGGREGATE(17,6,$C$2:$C$99/($E3=$B$2:$B$99),1))*1.5 F4 =(AGGREGATE(17,6,$C$2:$C$99/($E4=$B$2:$B$99),3)-AGGREGATE(17,6,$C$2:$C$99/($E4=$B$2:$B$99),1))*1.5 F5 =(AGGREGATE(17,6,$C$2:$C$99/($E5=$B$2:$B$99),3)-AGGREGATE(17,6,$C$2:$C$99/($E5=$B$2:$B$99),1))*1.5 F6 =(AGGREGATE(17,6,$C$2:$C$99/($E6=$B$2:$B$99),3)-AGGREGATE(17,6,$C$2:$C$99/($E6=$B$2:$B$99),1))*1.5 F7 =(AGGREGATE(17,6,$C$2:$C$99/($E7=$B$2:$B$99),3)-AGGREGATE(17,6,$C$2:$C$99/($E7=$B$2:$B$99),1))*1.5 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>