Forum Discussion
Hello
If 4 different cells with 3 options (all cells have a value), there will be 15 outcome.
Green Orange Red 4 0 0 3 1 0 3 0 1 2 2 0 2 1 1 2 0 2 1 3 0 1 2 1 1 1 2 1 0 3 0 4 0 0 3 1 0 2 2 0 1 3 0 0 4 If the number of green * 1 + the number of orange * 10 + the number of red * 100, you will have a sorted value list {4,13,22,31,40,103,112,121,130,202,211,220,301,310,400}.
Assume the 4 different cells are A23:A26 and the result in A27.
The formula in A27 is
=INDEX( {"A","A-","A-","B","B","C","C","C","C","C","C","C","C","C","C"}, 1, MATCH( COUNTIF(A23:A26,"Green")+ COUNTIF(A23:A26,"Orange")*10+ COUNTIF(A23:A26,"Red")*100, {4,13,22,31,40,103,112,121,130,202,211,220,301,310,400}, 0 ))This approach is not good. I think there should be a better approach from other helpers.
If 4 different cells with 3 options (all cells have a value), there will be 15 outcome.
| Green | Orange | Red |
| 4 | 0 | 0 |
| 3 | 1 | 0 |
| 3 | 0 | 1 |
| 2 | 2 | 0 |
| 2 | 1 | 1 |
| 2 | 0 | 2 |
| 1 | 3 | 0 |
| 1 | 2 | 1 |
| 1 | 1 | 2 |
| 1 | 0 | 3 |
| 0 | 4 | 0 |
| 0 | 3 | 1 |
| 0 | 2 | 2 |
| 0 | 1 | 3 |
| 0 | 0 | 4 |
If the number of green * 1 + the number of orange * 10 + the number of red * 100, you will have a sorted value list {4,13,22,31,40,103,112,121,130,202,211,220,301,310,400}.
Assume the 4 different cells are A23:A26 and the result in A27.
The formula in A27 is
=INDEX(
{"A","A-","A-","B","B","C","C","C","C","C","C","C","C","C","C"},
1,
MATCH(
COUNTIF(A23:A26,"Green")+
COUNTIF(A23:A26,"Orange")*10+
COUNTIF(A23:A26,"Red")*100,
{4,13,22,31,40,103,112,121,130,202,211,220,301,310,400},
0
))
This approach is not good. I think there should be a better approach from other helpers.
Option 1 - recommendation affirmed (if all green)
Option 2 - investigate further (if 2 or more orange was selected regardless of green colour)
Option 3 if one or more red is selected regardless of orange then escalate it to manager