Find Percentile of values partitioned by KEY in EXCEL
I want to find the percentile of the value column grouped by the KEY. For example, A1 Key values- should be grouped by and values corresponding to A1: 12 4 3 1 3 5
should be used for calculating percentile. Similarly it should go on for all the keys present. How can I achieve it in MS Excel. Currently I am doing it manually for every single key, but how can i do it for n number of keys. I have attached the screenshot as:
Not the cleanest solution but this array formula may work:
=IF(A2=A1;"";PERCENTILE(IF($A$2:$A$14=A2;$B$2:$B$14);0,9))
Becaue it's an array formula, it must be entered pressing CTRL+SHIFT+ENTER.
Just drag it down trough whole column of data: