Aug 23 2022 07:31 PM
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 |
Aug 23 2022 08:44 PM
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"))
Aug 24 2022 09:38 AM
@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.
I need number three to work, but cannot figure out what Excel does not like☹
Aug 24 2022 11:11 AM
=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.