If High Medium Low then result

dhrumilpatel02

New Member
Joined
Apr 20, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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 follow:



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!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
What are your expected results?
Actually the above data sample with three columns is the expected output, my bad. The original data we have just has two columns (software_id and Risk_Level). My expected output is three columns (Software_ID, Risk_Level, Max_Risk_Level). So just to reiterate:

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
 
Upvote 0
Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this would do.

22 04 21.xlsm
ABC
1Software_IDRisk_LevelMax_Risk_Level
2A3B0HighHigh
3A3B0MediumHigh
4A3B0LowHigh
5B9S1MediumMedium
6B9S1LowMedium
7C2J7LowLow
8D5K8HighHigh
9D5K8LowHigh
HML
Cell Formulas
RangeFormula
C2:C9C2=IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"H*"),"High",IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"M*"),"Medium","Low"))
 
Upvote 0
Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this would do.

22 04 21.xlsm
ABC
1Software_IDRisk_LevelMax_Risk_Level
2A3B0HighHigh
3A3B0MediumHigh
4A3B0LowHigh
5B9S1MediumMedium
6B9S1LowMedium
7C2J7LowLow
8D5K8HighHigh
9D5K8LowHigh
HML
Cell Formulas
RangeFormula
C2:C9C2=IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"H*"),"High",IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"M*"),"Medium","Low"))
[/RANGE
Cell Formulas
RangeFormula

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this would do.

22 04 21.xlsm
ABC
1Software_IDRisk_LevelMax_Risk_Level
2A3B0HighHigh
3A3B0MediumHigh
4A3B0LowHigh
5B9S1MediumMedium
6B9S1LowMedium
7C2J7LowLow
8D5K8HighHigh
9D5K8LowHigh
HML
Cell Formulas
RangeFormula
C2:C9C2=IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"H*"),"High",IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"M*"),"Medium","Low"))
Sure yes! I have updates my Account Details now. I tried the formula you sent and it is working for most Software IDs (B9S1, C2J7 and D5K8) but it is not working for (A2B0). C2 gives me the correct expected value (i.e. "High") but C3 and C4 should have "High" too, but rather it returns "Medium". I am attaching a screenshot of Formula in C3 for you to take a look at. Let me know if you need any more information, thanks a lot!!
 

Attachments

  • imgonline-com-ua-CompressToSize-JxRb9zJZa9NN.jpg
    imgonline-com-ua-CompressToSize-JxRb9zJZa9NN.jpg
    192.1 KB · Views: 7
Upvote 0
C2 gives me the correct expected value (i.e. "High") but C3 and C4 should have "High" too,
Why should C3 and C4 be the same as C2? In your picture, the software id in row 2 is A2B0 while in rows 3 and 4 it is A3B0
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top