If value is one of the 20% highest values in range, then return"Critical"

[ArianeSchweiger]

Hi,
I am looking for a formula which will attribute a priority status to a score.

The logic is =
- If the score is one of the 20% highest values in range, then status should be "Critical"
- If the score is within the 80-60% range of highest values in range, then status should be "High"
- If the score is within the 60-30% range of highest values in range, then status should be "Medium"
- If the score is one of the 30% lowest values in range, then status should be "Low"
Attached the xlx with an example.

Ideally I would need something that automatically updates the range.
Other alternative is for me to calculate the range and then establish boundaries (e.g. is score > 3.8 then "Critical"). I am not too sure how to calculate the range of a weighted average though.

Criteria one = can score 1-4, weight = 2
Criteria two = can score -1-7, weight = 4

--> How do you find out the range of the weighted average of Criteria one and two (e.g. Score criteria1 * 2 + Score criteria2 * 4)

Thank you ! :D

[Technetium]

I think the first question is much easier to do with VBA where you can dump the column data into an array, sort it, and then just assign "Critical" to the bottom 20%, etc. But without VBA...

If it isn't too work intensive (not sure how many times you need to repeat this task), you can try something like this. I created a source column of data, copied the source column to a duplicate column, sorted the duplicate column largest to smallest, and then wrote a function to recognize which percent of the way down the column each entry is. The formula can be copy-pasted down as far as you need. You will likely need to make some changes, such as when sorting, you'll need to make sure to sort related columns with it.

[ArianeSchweiger]

Thank you ! That makes sense. I have no VBA knowledge unfortunately. How would you do it? (I'm also working on Gsheet)

[GeoffW283]

Here is a non-VBA solution.

scores.png

This is just a development of Technetium's idea, except that it cuts out the need for a manual step to sort the scores and it simplifies the formulas a little.

In B2 copied down:

Please Login or Register  to view this content.

Just a lookup into a table that defines the cutoff percentages

In F3 copied down to F5:

Please Login or Register  to view this content.

This calculates the score corresponding to the desired cutoff percentage and is used by the above lookup table. The small() replaces the need for a manually sorted score column.

See the attached workbook

[Technetium]

Geoff's answer is definitely better. I had a feeling there was a way to do it without sorting but I actually never knew about the SMALL() function so this is also really useful to me! Thank you Geoff!

[GeoffW283]

@Technetium: Thanks for the rep. Actually, on further thought, the formula in F3 further simplifies, I think, to:

Please Login or Register  to view this content.

Hopefully what we have done here is what ArianeSchweiger is looking for

[Technetium]

I just realized there may be some ambiguity and I'm not sure our two methods are calculating exactly the same thing. Consider the following data:
1
3
4
789
790
792
794
795
799
800

Now if I said "get the cells that are in the bottom 20% of the range" that could mean two different things depending on how you look at it, finding all cells <= (800-1)/5 (i.e. 1, 3, and 4), or finding the two cells (20% of 10 cells) that are lowest, which would be just 1 and 3.

My method was finding the latter, I think your method might be finding the former. Not sure. Also less certain now about what the original post is after.