Counting the Occurence of each Quartile

moanuch

New Member
Joined
Dec 5, 2015
Messages
5
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.

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 500"]
<tbody>[TR]
[TD]Value[/TD]
[TD]Quartile 1[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="width: 109"]Quartile 1 Count[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Quartile 2[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="width: 109"]Quartile 2 Count[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Quartile 3[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="width: 109"]Quartile3 Count[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68, align: right"]1.25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]2.5[/TD]
[TD][/TD]
[TD]4.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
Any help would be greatly appeciated
 
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
 
Upvote 0
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
 
Upvote 0
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
ABCDEFG
1ValueQuartile 1Quartile 1 CountQuartile 2Quartile 2 CountQuartile 3Quartile3 Count
211.2522.524.752
31
42
52
63
73
810
915
Sheet
 
Upvote 0
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?

*ABCDEFG
******
******
******
******
******
******
******

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0"]Value[/TD]
[TD="bgcolor: #c0c0c0"]Quartile 1[/TD]
[TD="bgcolor: #c0c0c0"]Quartile 1 Count[/TD]
[TD="bgcolor: #c0c0c0"]Quartile 2[/TD]
[TD="bgcolor: #c0c0c0"]Quartile 2 Count[/TD]
[TD="bgcolor: #c0c0c0"]Quartile 3[/TD]
[TD="bgcolor: #c0c0c0"]Quartile3 Count[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1.25[/TD]
[TD="bgcolor: #ffff00, align: right"]2[/TD]
[TD="align: right"]2.5[/TD]
[TD="bgcolor: #ffff00, align: right"]2[/TD]
[TD="align: right"]4.75[/TD]
[TD="bgcolor: #ffff00, align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]15[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0
To get the count of the 4th quartile (based on the example above).
Code:
=COUNTIF(A2:A9,">"&F2)
 
Upvote 0
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
[TABLE="width: 377"]
<tbody>[TR]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Key[/TD]
[TD]Student Number[/TD]
[TD]Residence[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]710100565[/TD]
[TD]100565[/TD]
[TD]710[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]708100565[/TD]
[TD]100565[/TD]
[TD]708[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]726100565[/TD]
[TD]100565[/TD]
[TD]726[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]712100565[/TD]
[TD]100565[/TD]
[TD]712[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]700100565[/TD]
[TD]100565[/TD]
[TD]700[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]713100565[/TD]
[TD]100565[/TD]
[TD]713[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]711100565[/TD]
[TD]100565[/TD]
[TD]711[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]715100565[/TD]
[TD]100565[/TD]
[TD]715[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]727100565[/TD]
[TD]100565[/TD]
[TD]727[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]724100565[/TD]
[TD]100565[/TD]
[TD]724[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]704100565[/TD]
[TD]100565[/TD]
[TD]704[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]722100565[/TD]
[TD]100565[/TD]
[TD]722[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]718100565[/TD]
[TD]100565[/TD]
[TD]718[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]725100565[/TD]
[TD]100565[/TD]
[TD]725[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]730100565[/TD]
[TD]100565[/TD]
[TD]730[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]725100565[/TD]
[TD]100565[/TD]
[TD]725[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]725100565[/TD]
[TD]100565[/TD]
[TD]725[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]714100565[/TD]
[TD]100565[/TD]
[TD]714[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]712100565[/TD]
[TD]100565[/TD]
[TD]712[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]724100565[/TD]
[TD]100565[/TD]
[TD]724[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]715100565[/TD]
[TD]100565[/TD]
[TD]715[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]722100565[/TD]
[TD]100565[/TD]
[TD]722[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]707100565[/TD]
[TD]100565[/TD]
[TD]707[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]703100565[/TD]
[TD]100565[/TD]
[TD]703[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]723100565[/TD]
[TD]100565[/TD]
[TD]723[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]710100565[/TD]
[TD]100565[/TD]
[TD]710[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]704100565[/TD]
[TD]100565[/TD]
[TD]704[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]704100565[/TD]
[TD]100565[/TD]
[TD]704[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]723100565[/TD]
[TD]100565[/TD]
[TD]723[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]711100565[/TD]
[TD]100565[/TD]
[TD]711[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]713100565[/TD]
[TD]100565[/TD]
[TD]713[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]706100565[/TD]
[TD]100565[/TD]
[TD]706[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]707100565[/TD]
[TD]100565[/TD]
[TD]707[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]708100565[/TD]
[TD]100565[/TD]
[TD]708[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]711100565[/TD]
[TD]100565[/TD]
[TD]711[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]700100565[/TD]
[TD]100565[/TD]
[TD]700[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]701100565[/TD]
[TD]100565[/TD]
[TD]701[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]703100565[/TD]
[TD]100565[/TD]
[TD]703[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]718100565[/TD]
[TD]100565[/TD]
[TD]718[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]712100565[/TD]
[TD]100565[/TD]
[TD]712[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]713100565[/TD]
[TD]100565[/TD]
[TD]713[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]700100565[/TD]
[TD]100565[/TD]
[TD]700[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]717100565[/TD]
[TD]100565[/TD]
[TD]717[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]722100565[/TD]
[TD]100565[/TD]
[TD]722[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]712100565[/TD]
[TD]100565[/TD]
[TD]712[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]706100565[/TD]
[TD]100565[/TD]
[TD]706[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]702100565[/TD]
[TD]100565[/TD]
[TD]702[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]706100565[/TD]
[TD]100565[/TD]
[TD]706[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]713100565[/TD]
[TD]100565[/TD]
[TD]713[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]725100565[/TD]
[TD]100565[/TD]
[TD]725[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]713100565[/TD]
[TD]100565[/TD]
[TD]713[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD]711100565[/TD]
[TD]100565[/TD]
[TD]711[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD]700100565[/TD]
[TD]100565[/TD]
[TD]700[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD]701100565[/TD]
[TD]100565[/TD]
[TD]701[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD]714100565[/TD]
[TD]100565[/TD]
[TD]714[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD]722100565[/TD]
[TD]100565[/TD]
[TD]722[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]58[/TD]
[TD]711100565[/TD]
[TD]100565[/TD]
[TD]711[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]59[/TD]
[TD]723100565[/TD]
[TD]100565[/TD]
[TD]723[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]705100565[/TD]
[TD]100565[/TD]
[TD]705[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]700100565[/TD]
[TD]100565[/TD]
[TD]700[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]62[/TD]
[TD]724100565[/TD]
[TD]100565[/TD]
[TD]724[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]63[/TD]
[TD]714100565[/TD]
[TD]100565[/TD]
[TD]714[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]64[/TD]
[TD]730100565[/TD]
[TD]100565[/TD]
[TD]730[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD]700100565[/TD]
[TD]100565[/TD]
[TD]700[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]708100565[/TD]
[TD]100565[/TD]
[TD]708[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]67[/TD]
[TD]708100565[/TD]
[TD]100565[/TD]
[TD]708[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]68[/TD]
[TD]717100565[/TD]
[TD]100565[/TD]
[TD]717[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]69[/TD]
[TD]711100565[/TD]
[TD]100565[/TD]
[TD]711[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]70[/TD]
[TD]704100565[/TD]
[TD]100565[/TD]
[TD]704[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]71[/TD]
[TD]707100565[/TD]
[TD]100565[/TD]
[TD]707[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]72[/TD]
[TD]704100565[/TD]
[TD]100565[/TD]
[TD]704[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]713100565[/TD]
[TD]100565[/TD]
[TD]713[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]74[/TD]
[TD]723100565[/TD]
[TD]100565[/TD]
[TD]723[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]75[/TD]
[TD]726100565[/TD]
[TD]100565[/TD]
[TD]726[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]76[/TD]
[TD]713100565[/TD]
[TD]100565[/TD]
[TD]713[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]77[/TD]
[TD]704100565[/TD]
[TD]100565[/TD]
[TD]704[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]78[/TD]
[TD]706100565[/TD]
[TD]100565[/TD]
[TD]706[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]79[/TD]
[TD]715100565[/TD]
[TD]100565[/TD]
[TD]715[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]710100565[/TD]
[TD]100565[/TD]
[TD]710[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]81[/TD]
[TD]723100565[/TD]
[TD]100565[/TD]
[TD]723[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]82[/TD]
[TD]705100565[/TD]
[TD]100565[/TD]
[TD]705[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]83[/TD]
[TD]713100565[/TD]
[TD]100565[/TD]
[TD]713[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]712100565[/TD]
[TD]100565[/TD]
[TD]712[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]85[/TD]
[TD]702100565[/TD]
[TD]100565[/TD]
[TD]702[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]86[/TD]
[TD]717100565[/TD]
[TD]100565[/TD]
[TD]717[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]87[/TD]
[TD]726100565[/TD]
[TD]100565[/TD]
[TD]726[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]715100565[/TD]
[TD]100565[/TD]
[TD]715[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]89[/TD]
[TD]722100565[/TD]
[TD]100565[/TD]
[TD]722[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]701100565[/TD]
[TD]100565[/TD]
[TD]701[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]91[/TD]
[TD]718100565[/TD]
[TD]100565[/TD]
[TD]718[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]92[/TD]
[TD]712100565[/TD]
[TD]100565[/TD]
[TD]712[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]93[/TD]
[TD]708100565[/TD]
[TD]100565[/TD]
[TD]708[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]94[/TD]
[TD]716100565[/TD]
[TD]100565[/TD]
[TD]716[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD]711100565[/TD]
[TD]100565[/TD]
[TD]711[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]96[/TD]
[TD]702100565[/TD]
[TD]100565[/TD]
[TD]702[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]97[/TD]
[TD]710100565[/TD]
[TD]100565[/TD]
[TD]710[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]98[/TD]
[TD]726100565[/TD]
[TD]100565[/TD]
[TD]726[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]725100565[/TD]
[TD]100565[/TD]
[TD]725[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]708100565[/TD]
[TD]100565[/TD]
[TD]708[/TD]
[TD]28[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2

I created the 3 quartiles and hitting CTRL+SHIFT+ENTER
Code:
=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.



[TABLE="width: 783"]
<tbody>[TR]
[TD]*[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Key[/TD]
[TD]Quartile 1[/TD]
[TD]Quartile 2[/TD]
[TD]Quartile 3[/TD]
[TD]Count of Quartile 1[/TD]
[TD]Count of Quartile 2[/TD]
[TD]Count of Quartile 3[/TD]
[TD]Max[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]710100565[/TD]
[TD]4.25[/TD]
[TD]13[/TD]
[TD]26.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]708100565[/TD]
[TD]13[/TD]
[TD]15.5[/TD]
[TD]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]726100565[/TD]
[TD]4[/TD]
[TD]7.5[/TD]
[TD]21.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]712100565[/TD]
[TD]4[/TD]
[TD]17.5[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]700100565[/TD]
[TD]8.5[/TD]
[TD]21.5[/TD]
[TD]24.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]713100565[/TD]
[TD]16.5[/TD]
[TD]21.5[/TD]
[TD]27.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]711100565[/TD]
[TD]9[/TD]
[TD]17[/TD]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]715100565[/TD]
[TD]4.75[/TD]
[TD]10.5[/TD]
[TD]22.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]727100565[/TD]
[TD]#NUM![/TD]
[TD]11[/TD]
[TD]#NUM![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]724100565[/TD]
[TD]5[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]704100565[/TD]
[TD]7.5[/TD]
[TD]16.5[/TD]
[TD]21.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]722100565[/TD]
[TD]5.5[/TD]
[TD]11[/TD]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]718100565[/TD]
[TD]8[/TD]
[TD]16[/TD]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]725100565[/TD]
[TD]5[/TD]
[TD]16[/TD]
[TD]22.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]730100565[/TD]
[TD]#NUM![/TD]
[TD]14.5[/TD]
[TD]#NUM![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]714100565[/TD]
[TD]20[/TD]
[TD]24[/TD]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]707100565[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]703100565[/TD]
[TD]#NUM![/TD]
[TD]7[/TD]
[TD]#NUM![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]723100565[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]706100565[/TD]
[TD]2.5[/TD]
[TD]14.5[/TD]
[TD]24.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]701100565[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]717100565[/TD]
[TD]2[/TD]
[TD]18[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]702100565[/TD]
[TD]6[/TD]
[TD]16[/TD]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]705100565[/TD]
[TD]#NUM![/TD]
[TD]5.5[/TD]
[TD]#NUM![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]716100565[/TD]
[TD]#NUM![/TD]
[TD]26[/TD]
[TD]#NUM![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
ABCDEFGH
1KeyQuartile 1Quartile 2Quartile 3Count of Quartile 1Count of Quartile 2Count of Quartile 3Max
27101005654.251326.2511126.25
37081005651315.52812328
472610056547.521.52014
5712100565417.52512217.5
67001005658.521.524.512221.5
771310056516.521.527.7522227.75
87111005659171922219
97151005654.7510.522.2511122.25
10727100565#NUM!11#NUM!00011
117241005655121311113
127041005657.516.521.2512216.5
137221005655.5112212111
147181005658162211122
1572510056551622.512116
16730100565#NUM!14.5#NUM!00014.5
1771410056520242711127
18707100565282911129
19703100565#NUM!7#NUM!0007
2072310056538231218
217061005652.514.524.2511124.25
2270110056511123011130
237171005652182511125
247021005656162111121
25705100565#NUM!5.5#NUM!0005.5
26716100565#NUM!26#NUM!00026
Sheet
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top