Apr 20 2022 05:04 PM
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!
Apr 20 2022 05:39 PM
@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.
Apr 20 2022 05:50 PM - edited Apr 20 2022 05:53 PM
Great! Also, lets say if we only have first two column and we need the output, how can we get it? For example:
Apr 20 2022 08:18 PM