PremSivakanthan
New Member
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
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