Quartile and Deciles in Excel

[thisisrahul]

Hi,
Is there a way through which i can get values as D1, D2, D3 ....D10 for a data series depends if its belong to D1 deciles or D2 deciles etc.

Quartile function of excel gives each quartile no but how can i divide my data series in Q1, Q2, Q3 and Q4 depends on which quartile they fall in.

I need this for Quartile and Decile both.


-----------
Rahul

 

[mole999]

theres a PERCENTILE function, could that be in TENs ?

 

[AhoyNC]

You could also use PERCENTRANK with LOOKUP function as below.
This example shows quartiles, but can be adjusted for deciles.

Formula in B2 would just be copied down as needed.

Excel Workbook
	A	B
1	Data	Quartile
2	74	3
3	82	4
4	83	4
5	19	1
6	84	4
7	30	1
8	89	4
9	66	3
10	68	3
11	32	2
12	46	2
13	51	2
14	24	1
15	76	3
16	17	1
17	78	3
18	20	1
19	92	4
20	42	2
21	37	2
Sheet