assigning calculated priority to high, medium or low

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