I was needing to calculate percentile rank in a dataset with multiple top values (ensuring percentiles of 100%) This formula was super helpful in getting the right percentile calculation, However, I need to be able to do this based on separate data groups and I cannot figure it out. My formula is
=((COUNTIF(C$2:C$13828,"<="&C2)-1)/COUNT(C$2:C$13828))
But, I need this to be based on groupings that are listed in column B. So -for example there are 100 separate groups with 100 people in each unique group in column B, and I need the above formula to calculate the percentiles (of the value in column C) to based on each group in column B. Can someone help?
1 Answer
You need to add more criteria to your functions :
=((COUNTIFS(C$2:C$13828,"<="&C2, B$2:B$13828, B2)-1)/COUNTIF(B$2:B$13828, B2))
