How To Use Quartile Function Conditionally?

[xlmaniac]

Dear All,

I do have the following sample data set for 2 regions.

Based on the data points, I would like to calculate Quartile conditionally within the individual region.

The formula should check each & every data point within the region(East or North) and then assign Q1(Quartile 1), Q2(Quartile 2), Q3(Quartile 3 & Q4(Quartile 4) adjacent to the data points under Column:-C

Could somebody help me out with the formula under Column C which can populate the desired result? I am using Excel 2010 version.

Book1
	A	B	C
1	Region	Value	Quartile
2	East	19,53,161
3	East	35,98,698
4	East	25,72,615
5	East	35,09,037
6	East	19,90,461
7	East	13,06,940
8	East	5,76,580
9	East	52,01,267
10	East	10,83,777
11	East	33,16,229
12	East	14,55,057
13	East	20,02,636
14	North	85,28,643
15	North	31,27,305
16	North	55,85,107
17	North	1,46,109
18	North	15,10,508
19	North	37,59,945
20	North	14,61,318
21	North	35,98,227
22	North	47,82,466
23	North	39,02,372
24	North	21,09,296
25	North	37,75,630
Sheet1

 

[Sangar_338]

Dear All,

I do have the following sample data set for 2 regions.

Based on the data points, I would like to calculate Quartile conditionally within the individual region.

The formula should check each & every data point within the region(East or North) and then assign Q1(Quartile 1), Q2(Quartile 2), Q3(Quartile 3 & Q4(Quartile 4) adjacent to the data points under Column:-C

Could somebody help me out with the formula under Column C which can populate the desired result? I am using Excel 2010 version.

Book1
	A	B	C
1	Region	Value	Quartile
2	East	19,53,161
3	East	35,98,698
4	East	25,72,615
5	East	35,09,037
6	East	19,90,461
7	East	13,06,940
8	East	5,76,580
9	East	52,01,267
10	East	10,83,777
11	East	33,16,229
12	East	14,55,057
13	East	20,02,636
14	North	85,28,643
15	North	31,27,305
16	North	55,85,107
17	North	1,46,109
18	North	15,10,508
19	North	37,59,945
20	North	14,61,318
21	North	35,98,227
22	North	47,82,466
23	North	39,02,372
24	North	21,09,296
25	North	37,75,630
Sheet1

I am busy working on a broadly similar question so I believe one part of the solution you're looking for is the following formula, which will assist with giving you the relevant quartile based on two criteria:
=QUARTILE(IF(($A$2:$A$100=E2),IF(($B$2:$B$100=F2),$C$2:$C$100),""),1)