array entered percentile IF formula

[PremSivakanthan]

Hi all, I have a question on array formulas. I have come up with a formula that achieves the desired outcome, but I wondered if there was a more elegant solution. Essentially I want to calculate a conditional percentile. My data is structured as follows:


A B C

Name Grade Score

John A 45.67

Tim B 67.9

Jimmy D 31.0

Jack A 66.67

and so on.


My data is obviously alot larger than this, and will be refreshed every week - so I dont want to be mucking around with filtering the data in anyway. There will be many records where the score is equal to 0 that I want to exclude from all calculations.


I want to calculate the 25th percentile of grade A students, where the score is not equal to 0.


assuming I'm using named ranges, this does the trick:

{=PERCENTILE(IF((grade="A")*(score>0), score, ""), 0.25)}


I wondered if there is a better way to ahcieve the same outcome? In other words, is there another way to create an array in memory without the use of the IF formula?


thanks

 

[PremSivakanthan]

my above post came out a bit funny,

in column A you have all of the names

in column B you have all of the grades

in column C you have all of the scores

 

[jeremymjp]

Without using IF specifically? Yes. However, the ways I can come up with are still using similar formulas (COUNTIF and SUMIF).

 

[Luke M]

Looks good to me. =)

 

[Hui]

PremSivakanthan

Just one question

Won't by default the A graders have a score >0

and so the "Score>0" is not required,

or

Do you pre-populate the Names and Grades without a score?

 

[PremSivakanthan]

Hey Hui, it is possible (in my dataset) for somebody to have an A grade, but a zero score...! I know it doesnt make sense when you first think about it, but it does make sense if I were to explain it fully (that would take too long to do).


Im actually working with sales data, but to keep it generic I thought people might understand grade based data easier.