assigning calculated priority to high, medium or low-CodePudding

I have created a formula that automatically calculates priorities of certain items based on the relative benefit & risk of those items. Inspired by the article found here:

CodePudding user response：

I did not read the pdf. Just to get a "High" for a 3 you can use this:

=CHOOSE(C4,"Low","Medium","High")

CodePudding user response：

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