Forum Discussion
Solved
Searching for a formula to select the highest value
I need help creating a formula in Excel, as I have four values in the left column (Minor, Moderate, Major, Extreme) and on the right column, it should show in the cell of the highest value from the left column.
For example, the left column has all rates, and the right column shows the highest value as "Extreme":
In the following example, the left column has only two ratings only, and the formula selects the highest of them on the right column:
I hope you got the picture. Can someone help me with the right formula to do that?
=IF(COUNTIF(I2:I5,"extreme"),"extreme",IF(COUNTIF(I2:I5,"major"),"major",IF(COUNTIF(I2:I5,"moderate"),"moderate",IF(COUNTIF(I2:I5,"minor"),"minor",""))))Does this return the intended result?
As an alternative:
=LET( a,{"Minor","Moderate","Major","Extreme"}, INDEX(a,MAX(MATCH(A2:A5,a,0))))Detlef_Lewin sadly, shows an error
I suspect the reason is that you do not have access to Excel 365. Taken to the extreme 365 solutions bear little similarity to traditional spreadsheet solutions. For example
= LET( scale, {"Minor";"Moderate";"Major";"Extreme"}, TAKE(FILTER(scale, COUNTIFS(rate, scale)),-1) )sets up the scale and counts the number of occurrences for each level. Then filtering out levels that do not occur and returning the final value gives the desired result.
=IF(COUNTIF(I2:I5,"extreme"),"extreme",IF(COUNTIF(I2:I5,"major"),"major",IF(COUNTIF(I2:I5,"moderate"),"moderate",IF(COUNTIF(I2:I5,"minor"),"minor",""))))Does this return the intended result?
- Yes, its work.
Thanks a lot
