Max Risk with IF condition

[dhrumilpatel02]

Hello everyone!

I have two columns, Software_ID and Risk_Level. I want to see how can I fetch the maximum risk level per software_id in a new column. The data sample is as follows:

Software_ID, Risk_Level, Max_Risk_Level

A3B0, High, High

A3B0, Medium, High

A3B0, Low, High

B9S1, Medium, Medium

B9S1, Low, Medium

C2J7, Low, Low

D5K8, High, High

D5K8, Low, High

I have tried Maxifs, simple max with nested if, but it doesn't work. Can you please help? Let me know if you need more information. Thanks! 

[#2]

@dhrumilpatel02 So there are a few ways.  The easiest would have been if you used 1,2,3 instead of "Low", "Medium", "High".  But basically you can convert them into 1-3, find the max, and then convert them back:

=CHOOSE(MAX(MATCH(TRIM(FILTER(  [ Risk_Level] ,[@[Software_ID]]=[Software_ID],"")),{"Low","Medium","High"},0)),"Low","Medium","High")

I put the TRIM in because when I copied and paste I got some spaces in them that caused issues.  

I did a could examples in the attached.

[#3]

Great! Also, lets say if we only have first two column and we need the output, how can we get it? For example:

Original data we have:
Software_ID, Risk_Level
A3B0, High
A3B0, Medium
A3B0, Low
B9S1, Medium
B9S1, Low
C2J7, Low
D5K8, High
D5K8, Low


Expected Output:

Software_ID, Risk_Level, Max_Risk_Level
A3B0, High, High
A3B0, Medium, High
A3B0, Low, High
B9S1, Medium, Medium
B9S1, Low, Medium
C2J7, Low, Low
D5K8, High, High
D5K8, Low, High

[#4]

that is what I did. See formula above and example file attached above