How to simplify/consolidate IF(AND( code

[Enzo_Matrix]

I want to consolidate my IF(AND formula and am having issues. Even though this is messy, it does work for my needs:

=IF(AND($J18<=5,$K18=1),"Low",IF(AND($J18<=2,$K18=2),"Low",IF(AND($J18<=2,$K18=3),"Low",IF(AND($J18=1,$K18=4),"Low",IF(AND($J18>=3,$K18=5),"High",IF(AND($J18=5,$K18>=3),"High",IF(AND($J18>=3,$K18=2),"Medium",IF(AND($J18>=4,$K18>=4),"High",IF(AND($J18>=2<4,$K18=3),"Medium",IF(AND($J18>=2<4,$K18=4),"Medium",""))))))))))



This is what I tried to clean it to but it doesn't work anymore:

=IF(AND($J17<=5,$K17=1,$J17<=2,$K17>=2<=3,$J17=1,$K17=1),"Low",IF(AND($J17>=3,$K17=2,$J17>=3<=4,$K17=3,$J17>=2<=3,$K17=4,$J17<=2,$K17=5),"Medium",IF(AND($J17=5,$K17=3,$J17>=4,$K17=4,$J17>=3,$K17=5),"High","")))

 

[Marcílio_Lobão]

Enzo_Matrix, Good evening.

Since we do not know what you're doing, the only way to help you at this point is to correct the syntax of your formula that you said is working.

Try this one:

=IF(OR(AND($J18<=5,$K18=1),AND($J18<=2,$K18=2),AND($J18<=2,$K18=3),AND($J18=1,$K18=4)),"Low", IF(OR(AND($J18>=3,$K18=5),AND($J18=5,$K18>=3),AND($J18>=4,$K18>=4)),"High",IF(OR(AND($J18>=3,$K18=2),AND($J18>=2<4,$K18=3),AND($J18>=2<4,$K18=4)),"Medium","")))

Please, tell us if it works as you want.

I hope it helps.

 

[Enzo_Matrix]

It did not work out and came up with the message 'Numbers between 1-5 must be chosen'.

this is what I am trying to accomplish. The numbers on each side of the grid will generate the 'High''Medium''Low' in a cell depending on which configuration you choose.
A=1, B=3, C='High' (this is what I want to be changeable.).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]Low[/TD]
[TD]Medium[/TD]
[TD]High[/TD]
[TD]High[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]Low[/TD]
[TD]Medium[/TD]
[TD]Medium[/TD]
[TD]High[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]Low[/TD]
[TD]Medium[/TD]
[TD]Medium[/TD]
[TD]Medium[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]4[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Medium[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]5[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Low[/TD]
[TD]Medium[/TD]
[TD]High[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I modified my code slightly and now it is:

=IF(AND($J101<=5,$K101=1),"Low",IF(AND($J101<=2,$K101=2),"Low",IF(AND($J101<=2,$K101=3),"Low",IF(AND($J101=1,$K101=4),"Low",IF(AND($J101>=3,$K101=5),"High",IF(AND($J101=5,$K101>=3),"High",IF(AND($J101>=3,$K101=2),"Medium",IF(AND($J101>=4,$K101>=4),"High",IF(AND($J101>=2<4,$K101=3),"Medium",IF(AND($J101>=2<4,$K101=4),"Medium",""))))))))))

As I mentioned, it's functional, just messy

 

[thisoldman]

I don't know if this is any better. Try this and see if it works:

=IF(OR($K101=1,AND($K101<=3,$J101<=2),AND($K101=4,$J101=1)),"Low",IF(OR(AND($K101=3,$J101=5),AND($K101=4,$J101>=4),AND($K101=5,$J101>=3)),"High","Medium"))

 

[Marcílio_Lobão]

Enzo_Matrix, Good evening.

If you are getting this error message it is because you have a data validation associated with cell that will only accept numbers from 1 to 5 in it. Only that. No formula.

Your table looks like a Risk Matrix, which is easy to work with.

We just can not understand these letters A and B on the sides.

"...As I mentioned, it's functional, just messy..."
Are you sure your formula is working?

I did a little test to help us understand what you really need.
A simple combination of Index and Match functions solves the question.

Take a look and tell us if this is what you wanted:
https://www.sendspace.com/file/rpm0iw

I hope it helps.

 

[Enzo_Matrix]

It is a Risk Matrix that I am trying to set up. Might have been easier if I had mentioned that right off the go.

Your code worked perfectly, thank you. I have modified it slightly to remove the #N/A error that popped up and now use this:

=IFERROR(INDEX($G$4:$K$8,MATCH($J17,$F$4:$F$8,0),MATCH($K17,$G$9:$K$9,0)),"")

Thanks again. This is a lot simpler than what I started with.

 

[Marcílio_Lobão]

Enzo_Matrix, Good afternoon.

I am very happy to have been able to assist you in your work.

Have a nice day!