Quartiles by Year

[hales231]

I have a large dataset (almost 300K rows) with multiple years of information (2003-2016). I'm trying to assign size into a Small, Medium, and Large category by year. My first attempt was to determine the quartiles then manually assign the size to a category but I'm sure there is a more efficient way of doing this. Thank you for your help.

[TABLE="width: 158"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD]Size[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]136[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]146[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]107[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]139[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]152[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]102[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD]147[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD]119[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD]152[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD]107[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD]116[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]129[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]102[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]114[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]126[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]118[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD]119[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD]108[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD]149[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD]143[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD]132[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD]135[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD]113[/TD]
[/TR]
</tbody>[/TABLE]

 

[CyrusTheVirus]

Hey hales,

Can you please show your expected results so we know what your end results should be? Thank you.

-C

 

[sandy666]

I think you need define range criteria first, for Small, Medium and Large and use it in PivotTable

 

[hales231]

Sorry for the vague question. I need to assign a quartile to each size based on the total range of sizes for the year. Every year has a different range of sizes so using a set criteria doesn't work with this dataset. I've also included the quartile table for reference.

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Year[/TD]
[TD="class: xl65, width: 64"]Size[/TD]
[TD="class: xl65, width: 64"]Quartile[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]136[/TD]
[TD="class: xl65"]75[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]146[/TD]
[TD="class: xl65"]75[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]107[/TD]
[TD="class: xl65"]25[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]139[/TD]
[TD="class: xl65"]75[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]152[/TD]
[TD="class: xl65"]75[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]125[/TD]
[TD="class: xl65"]50[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]102[/TD]
[TD="class: xl65"]25[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]147[/TD]
[TD="class: xl65"]75[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]106[/TD]
[TD="class: xl65"]25[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]119[/TD]
[TD="class: xl65"]50[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]125[/TD]
[TD="class: xl65"]50[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 344"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD]25[/TD]
[TD]50[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]112[/TD]
[TD]120[/TD]
[TD]131[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD]112[/TD]
[TD]118[/TD]
[TD]124[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]113[/TD]
[TD]121[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD]104[/TD]
[TD]111[/TD]
[TD]118[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD]101[/TD]
[TD]105[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]109[/TD]
[TD]116[/TD]
[TD]124[/TD]
[/TR]
[TR]
[TD]2009[/TD]
[TD]110[/TD]
[TD]117[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD]110[/TD]
[TD]117[/TD]
[TD]124[/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD]105[/TD]
[TD]111[/TD]
[TD]117[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]109[/TD]
[TD]116[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]108[/TD]
[TD]115[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]102[/TD]
[TD]111[/TD]
[TD]122[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]103[/TD]
[TD]111[/TD]
[TD]121[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]100[/TD]
[TD]109[/TD]
[TD]118[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.
Drag formula down and across as needed.

Excel Workbook
	A	B	C	D	E	F	G
1	Year	Size		Year	0.25	0.5	0.75
2	2003	136		2003	116	136	143
3	2003	146		2004	112	119	136
4	2003	107		2005	110	118	123
5	2003	139		2006	118	130	137
6	2003	152
7	2003	125
8	2003	102
9	2004	147
10	2004	106
11	2004	119
12	2004	125
13	2004
14	2004	152
15	2004	107
16	2004	116
17	2005	129
18	2005	106
19	2005	102
20	2005	114
21	2005	120
22	2005	126
23	2005	118
24	2006	119
25	2006	108
26	2006	149
27	2006	143
28	2006	132
29	2006	128
30	2006	135
31	2006	113
Sheet