Counting the Occurence of each Quartile

[moanuch]

Hello All,

I hope I have a simple question.

I have a set of data like the following. I would like to know the formula on how to see how many times the value in Quartile 1, 2, & 3 occurred.

Value Quartile 1 Quartile 1 Count <tbody> </tbody> Quartile 2 Quartile 2 Count <tbody> </tbody> Quartile 3 Quartile3 Count <tbody> </tbody> 1 1.25 <tbody> </tbody> 2.5 4.75 1 2 2 3 3 10 15 <tbody> </tbody>

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

Any help would be greatly appeciated

 

[wynn89]

Hi monauch,

I don't quite get your question.

perhaps you would like to provide more data and the expected outcome as well.

thanks

Regards,
wynn

 

[moanuch]

Hi monauch,

I don't quite get your question.

perhaps you would like to provide more data and the expected outcome as well.

thanks

Regards,
wynn

Column "Values" has the values I want getting quartiles 1-3 from. The piece I am missing is the count of values that were taken in consideration to make quarticles 1,2,3. I hope that helps

 

[AhoyNC]

If you just want a count of the values in each quartile look at the COUNTIFS & COUNTIF functions.
Also, shouldn't your 3rd quartile be 8.25?

Excel Workbook
	A	B	C	D	E	F	G
1	Value	Quartile 1	Quartile 1 Count	Quartile 2	Quartile 2 Count	Quartile 3	Quartile3 Count
2	1	1.25	2	2.5	2	4.75	2
3	1
4	2
5	2
6	3
7	3
8	10
9	15
Sheet

 

[moanuch]

If you just want a count of the values in each quartile look at the COUNTIFS & COUNTIF functions.
Also, shouldn't your 3rd quartile be 8.25?

*	A	B	C	D	E	F	G
1	Value	Quartile 1	Quartile 1 Count	Quartile 2	Quartile 2 Count	Quartile 3	Quartile3 Count
2	1	1.25	2	2.5	2	4.75	2
3	1	*	*	*	*	*	*
4	2	*	*	*	*	*	*
5	2	*	*	*	*	*	*
6	3	*	*	*	*	*	*
7	3	*	*	*	*	*	*
8	10	*	*	*	*	*	*
9	15	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:70px;"><col style="width:69px;"><col style="width:74px;"><col style="width:77px;"><col style="width:73px;"><col style="width:89px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =COUNTIF(A2:A9,"<"&B2) E2 =COUNTIFS(A2:A9,">="&B2,A2:A9,"<"&D2) G2 =COUNTIFS(A2:A9,">="&D2,A2:A9,"<"&F2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Great - now my only question is the count total is less than the total amount of values. In column A there are 8 values but the sum of counts in C, D, % G is only 6. What I am trying to find out is how many values make up each quartile.

Thanks for everything

 

[AhoyNC]

To get the count of the 4th quartile (based on the example above).

=COUNTIF(A2:A9,">"&F2)

 

[moanuch]

Hi All

Thank you for your help so and I would like your help with this. Here are some of the requirements needed.

The following is the data set in Sheet 1

• I need to see how a student fairs at each residence in terms of quantity
• I created a column Key to make a unique identifier
• I created sheet 2 and removed duplicates see below to get the quartiles 1 through 3 see below

Sheet 1

*	A	B	C	D
1	Key	Student Number	Residence	Quantity
2	710100565	100565	710	4
3	708100565	100565	708	10
4	726100565	100565	726	4
5	712100565	100565	712	21
6	700100565	100565	700	9
7	713100565	100565	713	21
8	711100565	100565	711	10
9	715100565	100565	715	4
10	727100565	100565	727	11
11	724100565	100565	724	13
12	704100565	100565	704	20
13	722100565	100565	722	11
14	718100565	100565	718	8
15	725100565	100565	725	21
16	730100565	100565	730	26
17	725100565	100565	725	24
18	725100565	100565	725	8
19	714100565	100565	714	27
20	712100565	100565	712	28
21	724100565	100565	724	5
22	715100565	100565	715	14
23	722100565	100565	722	2
24	707100565	100565	707	29
25	703100565	100565	703	5
26	723100565	100565	723	20
27	710100565	100565	710	5
28	704100565	100565	704	8
29	704100565	100565	704	21
30	723100565	100565	723	26
31	711100565	100565	711	3
32	713100565	100565	713	24
33	706100565	100565	706	1
34	707100565	100565	707	8
35	708100565	100565	708	15
36	711100565	100565	711	20
37	700100565	100565	700	23
38	701100565	100565	701	30
39	703100565	100565	703	9
40	718100565	100565	718	16
41	712100565	100565	712	5
42	713100565	100565	713	21
43	700100565	100565	700	26
44	717100565	100565	717	2
45	722100565	100565	722	25
46	712100565	100565	712	14
47	706100565	100565	706	25
48	702100565	100565	702	6
49	706100565	100565	706	7
50	713100565	100565	713	29
51	725100565	100565	725	2
52	713100565	100565	713	15
53	711100565	100565	711	19
54	700100565	100565	700	20
55	701100565	100565	701	11
56	714100565	100565	714	20
57	722100565	100565	722	19
58	711100565	100565	711	17
59	723100565	100565	723	8
60	705100565	100565	705	10
61	700100565	100565	700	24
62	724100565	100565	724	12
63	714100565	100565	714	24
64	730100565	100565	730	3
65	700100565	100565	700	7
66	708100565	100565	708	28
67	708100565	100565	708	14
68	717100565	100565	717	18
69	711100565	100565	711	18
70	704100565	100565	704	6
71	707100565	100565	707	2
72	704100565	100565	704	22
73	713100565	100565	713	8
74	723100565	100565	723	5
75	726100565	100565	726	25
76	713100565	100565	713	22
77	704100565	100565	704	13
78	706100565	100565	706	22
79	715100565	100565	715	25
80	710100565	100565	710	28
81	723100565	100565	723	1
82	705100565	100565	705	1
83	713100565	100565	713	30
84	712100565	100565	712	24
85	702100565	100565	702	16
86	717100565	100565	717	25
87	726100565	100565	726	11
88	715100565	100565	715	7
89	722100565	100565	722	9
90	701100565	100565	701	12
91	718100565	100565	718	22
92	712100565	100565	712	1
93	708100565	100565	708	16
94	716100565	100565	716	26
95	711100565	100565	711	9
96	702100565	100565	702	21
97	710100565	100565	710	21
98	726100565	100565	726	4
99	725100565	100565	725	16
100	708100565	100565	708	28

<tbody>
</tbody>

Sheet 2

I created the 3 quartiles and hitting CTRL+SHIFT+ENTER

=QUARTILE.EXC(IF(Sheet1!$B$3:$B$101=Sheet2!B3,Sheet1!$E$3:$E$101),1)
=QUARTILE.EXC(IF(Sheet1!$B$3:$B$101=Sheet2!B3,Sheet1!$E$3:$E$101),2)
=QUARTILE.EXC(IF(Sheet1!$B$3:$B$101=Sheet2!B3,Sheet1!$E$3:$E$101),3)

What is needed to figure out columns E through H

• E-G is the count of Quantity (Sheet 1 column D) that made quartile 1,2 and 3(Sheet 2 columns B-D respectively)
• Column H (Max) would need to take the Maximum of columns E-H and return the corresponding B-C Max Quartile value.
• Any help would be greatly appreciated.

*	A	B	C	D	E	F	G	H
1	Key	Quartile 1	Quartile 2	Quartile 3	Count of Quartile 1	Count of Quartile 2	Count of Quartile 3	Max
2	710100565	4.25	13	26.25
3	708100565	13	15.5	28
4	726100565	4	7.5	21.5
5	712100565	4	17.5	25
6	700100565	8.5	21.5	24.5
7	713100565	16.5	21.5	27.75
8	711100565	9	17	19
9	715100565	4.75	10.5	22.25
10	727100565	#NUM!	11	#NUM!
11	724100565	5	12	13
12	704100565	7.5	16.5	21.25
13	722100565	5.5	11	22
14	718100565	8	16	22
15	725100565	5	16	22.5
16	730100565	#NUM!	14.5	#NUM!
17	714100565	20	24	27
18	707100565	2	8	29
19	703100565	#NUM!	7	#NUM!
20	723100565	3	8	23
21	706100565	2.5	14.5	24.25
22	701100565	11	12	30
23	717100565	2	18	25
24	702100565	6	16	21
25	705100565	#NUM!	5.5	#NUM!
26	716100565	#NUM!	26	#NUM!

<tbody>
</tbody>

 

[AhoyNC]

You didn't say what you wanted if the count was the same for each quartile, so the max formula below will return the the 3rd quartile value.
If there is only 1 value for the student it returns the 2nd quartile (median).

Excel Workbook
	A	B	C	D	E	F	G	H
1	Key	Quartile 1	Quartile 2	Quartile 3	Count of Quartile 1	Count of Quartile 2	Count of Quartile 3	Max
2	710100565	4.25	13	26.25	1	1	1	26.25
3	708100565	13	15.5	28	1	2	3	28
4	726100565	4	7.5	21.5	2	0	1	4
5	712100565	4	17.5	25	1	2	2	17.5
6	700100565	8.5	21.5	24.5	1	2	2	21.5
7	713100565	16.5	21.5	27.75	2	2	2	27.75
8	711100565	9	17	19	2	2	2	19
9	715100565	4.75	10.5	22.25	1	1	1	22.25
10	727100565	#NUM!	11	#NUM!	0	0	0	11
11	724100565	5	12	13	1	1	1	13
12	704100565	7.5	16.5	21.25	1	2	2	16.5
13	722100565	5.5	11	22	1	2	1	11
14	718100565	8	16	22	1	1	1	22
15	725100565	5	16	22.5	1	2	1	16
16	730100565	#NUM!	14.5	#NUM!	0	0	0	14.5
17	714100565	20	24	27	1	1	1	27
18	707100565	2	8	29	1	1	1	29
19	703100565	#NUM!	7	#NUM!	0	0	0	7
20	723100565	3	8	23	1	2	1	8
21	706100565	2.5	14.5	24.25	1	1	1	24.25
22	701100565	11	12	30	1	1	1	30
23	717100565	2	18	25	1	1	1	25
24	702100565	6	16	21	1	1	1	21
25	705100565	#NUM!	5.5	#NUM!	0	0	0	5.5
26	716100565	#NUM!	26	#NUM!	0	0	0	26
Sheet

 

[moanuch]

Absolutely outstanding AhoyNC. Thank you!

 

[AhoyNC]

You're welcome. Thanks for the feedback.