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.

Year	Size
2003	136
2003	146
2003	107
2003	139
2003	152
2003	125
2003	102
2004	147
2004	106
2004	119
2004	125
2004
2004	152
2004	107
2004	116
2005	129
2005	106
2005	102
2005	114
2005	120
2005	126
2005	118
2006	119
2006	108
2006	149
2006	143
2006	132
2006	128
2006	135
2006	113

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

 

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

Year	Size	Quartile
2003	136	75
2003	146	75
2003	107	25
2003	139	75
2003	152	75
2003	125	50
2003	102	25
2003	147	75
2003	106	25
2003	119	50
2003	125	50

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

Year	25	50	75
2003	112	120	131
2004	112	118	124
2005	113	121	128
2006	104	111	118
2007	101	105	110
2008	109	116	124
2009	110	117	125
2010	110	117	124
2011	105	111	117
2012	109	116	125
2013	108	115	125
2014	102	111	122
2015	103	111	121
2016	100	109	118

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

 

[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