Multiple If(and( formula not working

New Contributor

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: 

 

KathrynBentson_0-1661306928010.png

Position XPosition YFormula
2026.351869.63Worked Well
2214.061704.04Worked Well
2026.352041.75Worked Well
2390.351704.04Worked Well
2214.061880.63Worked Well
2193.782041.63Worked Well
2032.782202.75Worked Well
2551.351704.04Worked Well
2390.351880.63Worked Well
2199.962202.63Worked Well
2026.352377.01Worked Well
2390.352046.8Worked Well
2557.761880.63Worked Well
2811.531697.15Needs to Change
2187.352377.01Worked Well
2187.352377.51Worked Well
2384.172207.8Worked Well
2551.352046.8Worked Well
2958.141692.14Needs to Change
2811.531858.15Needs to Change
3 Replies

@Kathryn Bentson 

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"))

 

@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☹

@Kathryn Bentson 

=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.

nested if.JPG