New Contributor

# Multiple If(and( formula not working

Hello.  I am trying to have excel calculate the quandrant that something falls in.  I have the criteria and I can get the formul to work for 3 of the sections, but when I add the fourth, it fails.  I have tried multiple things and searched for a solution and have not found one yet.  Hoping you can figure out what I am doing wrong.

This formula works, but it is only capturing three of the quandrants:

 Position X Position Y Formula 2026.35 1869.63 Worked Well 2214.06 1704.04 Worked Well 2026.35 2041.75 Worked Well 2390.35 1704.04 Worked Well 2214.06 1880.63 Worked Well 2193.78 2041.63 Worked Well 2032.78 2202.75 Worked Well 2551.35 1704.04 Worked Well 2390.35 1880.63 Worked Well 2199.96 2202.63 Worked Well 2026.35 2377.01 Worked Well 2390.35 2046.8 Worked Well 2557.76 1880.63 Worked Well 2811.53 1697.15 Needs to Change 2187.35 2377.01 Worked Well 2187.35 2377.51 Worked Well 2384.17 2207.8 Worked Well 2551.35 2046.8 Worked Well 2958.14 1692.14 Needs to Change 2811.53 1858.15 Needs to Change
3 Replies

# Re: Multiple If(and( formula not working

1. You did not show your current formula.

2. Your definitions exclude the values 2571 for x and 2719 for y.

``````=LET(
a,COUNTIFS(\$A4,"<2571",\$B4,"<2719")*1,
b,COUNTIFS(\$A4,"<2571",\$B4,">2719")*2,
c,COUNTIFS(\$A4,">2571",\$B4,"<2719")*3,
d,COUNTIFS(\$A4,">2571",\$B4,">2719")*4,
e,SUM(a,b,c,d),
CHOOSE(e,"Worked well","Needs to change","Unanswered questions","New ideas to try"))``````

# Re: Multiple If(and( formula not working

@Detlef Lewin  Sorry.  It must have got deleted before I submitted.  These were the formulas I was trying, but I could only get it to do three of the quadrants.  When I added the fourth, it did not work.

1. This works, but only contains three of the quadrants: =IF(AND(U26<2571,T26<2719),"Worked Well",IF(AND(U26<2571,T26>2719),"Needs to Change",IF(AND(U26>2571,T26<2719),"Unanswered Questions”),""")))
2. This works, but it is only the quadrant that will not work in the above formula: =IF(AND(U27>2571,T27>2719),"New Ideas to Try")
3. This does NOT work, and it contains all four quadrants:  =IF(AND(U26<2571,T26<2719),"Worked Well",IF(AND(U26<2571,T26>2719),"Needs to Change",IF(AND(U26>2571,T26<2719),"Unanswered Questions”,IF(AND(U26>2571,T26>2719),"New Ideas to Try"),""”))))

I need number three to work, but cannot figure out what Excel does not like☹

# Re: Multiple If(and( formula not working

``=IF(AND(U26<2571,T26<2719),"Worked Well",IF(AND(U26<2571,T26>2719),"Needs to Change",IF(AND(U26>2571,T26<2719),"Unanswered Questions",IF(AND(U26>2571,T26>2719),"New Ideas to Try",""))))``

An alternative to the solution with the LET formula could be this formula.