Aug 18 2022 07:02 AM
Hi, I am new here and I have a problem with a project regarding a formula. Excel is saying #Value, which off course is not a good sign.
I have a list with values below each other from: 1 to 25 (25 = max).
The classification is as follows:
< 6 = Low
between 5 and 12 = Medium
> 12 = High
What I want Excel to do: Suppose in cell B2 there is the value 9, then in cell C2 there should be the result according to the classification. Result in C2 will then be: Medium.
Antother example: In cell B2 is the value 4, than in cell C2 Excel should be showing: Low.
The used formula (which is fault):
=IF(AND(M4>=5;M4<=12);"Medium")*(IF(M4<=6;"Low")*(IF(M4>=12;"High")))
Row Risk number (B) Risk in words (C)
2 12 High
3 4 Low
4 9 Medium
If above is not possible in Excel, please let me know. It would be much appreciated.
Sincerely,
Dominique de Jong
Aug 18 2022 07:10 AM
Aug 18 2022 07:16 AM
SolutionYou mentioned "between 5 and 12" = medium and ">12" = high. So 12 should be medium.
(Also, I think it should be "between 6 and 12")
You can use
=LOOKUP(B2;{1\6\13};{"Low"\"Medium"\"High"})
or create a lookup list:
And use either
=VLOOKUP(B2;$G$2:$H$4;2)
or
=XLOOKUP(B2;$G$2:$G$4;$H$2:$H$4;"")
The advantage of the lookup list is that it's easy to change the thresholds - you have to do it only in one place instead of in each formula.
Aug 18 2022 07:20 AM
Aug 18 2022 07:26 AM
Aug 18 2022 07:48 AM
Aug 18 2022 11:25 PM - edited Aug 18 2022 11:34 PM
Goodmorning,
Many thanks for the reply. When I was reading your formula, it sounded very logical to me. Only too late :) :)
Aug 18 2022 07:16 AM
SolutionYou mentioned "between 5 and 12" = medium and ">12" = high. So 12 should be medium.
(Also, I think it should be "between 6 and 12")
You can use
=LOOKUP(B2;{1\6\13};{"Low"\"Medium"\"High"})
or create a lookup list:
And use either
=VLOOKUP(B2;$G$2:$H$4;2)
or
=XLOOKUP(B2;$G$2:$G$4;$H$2:$H$4;"")
The advantage of the lookup list is that it's easy to change the thresholds - you have to do it only in one place instead of in each formula.