I’ve fixed it using the Rank function
The RANK function in Excel returns the order (or rank) of a numeric value compared to other values in the same list. In other words, it tells you which value is the highest, the second highest, etc.
Then I do a COUNTA to find out how many non-blank cells there are and divide that number by 3 and then I simply check how the rank compares to the count with the following logic:
Rank > (Count / 3) * 2 (so more than 2/3) = Low Rank > (Count / 3) (so more than 1/3) = Medium Rank < (Count / 3) (so less than 1/3) = High
CLICK HERE to find out more related problems solutions.