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.
<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:
<colgroup><col><col></colgroup><tbody>
</tbody>
Sample reference data:
<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
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