CODEWITHSUNDEEP
excel-formulaexcel-2010
Bubba
Bubba

Reputation: 13

Find the range of cells that contain a particular value

I am trying use the percentile method on a table similar to this:

+--------+-------------+-------+
| Grade  | StudentName | Score |
+--------+-------------+-------+
| Grade1 | StudentA    | 55    |
| Grade1 | StudentB    | 65    |
| Grade1 | StudentC    | 46    |
| Grade2 | StudentA    | 77    |
| Grade2 | StudentB    | 55    |
| Grade2 | StudentC    | 62    |
| ...    | ...         | ...   |
+--------+-------------+-------+

I am trying the calculate the quartile points for each grade in the following matrix. 

+---------+-----------------+-----------------+-----+ 
|         | 25th Percentile | 50th Percentile | ... |
+---------+-----------------+-----------------+-----+
| Grade 1 |                 |                 |     |
| Grade 2 |                 |                 |     |
| Grade 3 |                 |                 |     |
| ...     |                 |                 |     |
+---------+-----------------+-----------------+-----+

The problem is that percentile formula needs an array of cells. How do I dynamically look for the array/range of cells that contain Grade1?

Upvotes: 1

Views: 71

Answers (1)

zx8754
zx8754

Reputation: 56004

Formula for F2:

=QUARTILE(IF($A$2:$A$7=$E2,$C$2:$C$7,""),F$1)

Then CTRL+SHIFT+ENTER to make it array formula with curly brackets.

enter image description here

Upvotes: 1

Related Questions