Calculating Quartiles

[heimdaloz]

I have a list of data contain approximately 800 rows and about 15 columns. I have been asked to show the quartile and value falls into, in a particular column. I know how to calculate the quartile values but don't know how I put a flag against each value to indicate which quartile it falls into? I have to do this for 4 of the columns. The columns represents score on a survey.

 

[kevin9999]

Difficult with any sample data - could you post a sample of your data using the XL2BB add in, or alternatively share your file via Google Drive, Dropbox or similar file sharing platform? In the meantime, here's a small sample of what you might be looking for?

quartiles.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Value 1	Quartile	Value 2	Quartile	Value 3	Quartile	Value 4	Quartile		Quartile	Value
2	27	2nd	94	4th	32	2nd	39	2nd		1st	0
3	57	3rd	4	1st	87	4th	66	3rd		1st	25.75
4	61	3rd	94	4th	92	4th	43	2nd		2nd	50
5	13	1st	46	2nd	27	2nd	13	1st		3rd	69.25
6	38	2nd	87	4th	8	1st	27	2nd		4th	100
7	68	3rd	90	4th	9	1st	74	4th
8	28	2nd	8	1st	75	4th	70	4th
9	56	3rd	44	2nd	16	1st	72	4th
10	91	4th	44	2nd	5	1st	2	1st
11	40	2nd	34	2nd	18	1st	28	2nd
12	69	3rd	26	2nd	22	1st	29	2nd
13	39	2nd	51	3rd	18	1st	45	2nd
14	50	3rd	51	3rd	97	4th	23	1st
15	88	4th	81	4th	29	2nd	72	4th
16	76	4th	100	4th	12	1st	53	3rd
17	53	3rd	25	1st	58	3rd	72	4th
18	99	4th	70	4th	29	2nd	21	1st
19	26	2nd	65	3rd	37	2nd	15	1st
20	69	3rd	8	1st	97	4th	11	1st
Sheet2

Cell Formulas
Range	Formula
K3	K3	=QUARTILE($A:$A,1)
K4	K4	=QUARTILE($A:$A,2)
K5	K5	=QUARTILE($A:$A,3)
K6	K6	=QUARTILE($A:$A,4)
B2:B20,H2:H20,F2:F20,D2:D20	B2	=IF(A2=MAX($K$2:$K$6),"4th",LOOKUP(A2,$K$2:$K$6,{"1st","2nd","3rd","4th"}))

Named Ranges
Name	Refers To	Cells
myRange	=Sheet2!$A$2:$A$101,Sheet2!$C$2:$C$101	K3:K6, B2