Excel nested "IF" returning "FALSE" but I cannot work out why
I want to do something quite simple in Excel:
If Y13 is greater than 0% of X13 and less than 33% of X13, display "Strong".
If Y13 is greater than 67% of X13 and less than 100% of X13, display "Weak".
If Y13 is greater than 33% of X13 and less than 67% of X13, display "Medium".
I must have tried 20 different ways, trying to do *0.67
to try to fool it into doing the percentage some other way, to no avail.
This doesn't create any errors but it wrongly comes up with FALSE
in the cell, when Y13 is 9% of X13 (56/616) which should fall into the "Strong" category:
=IF(AND(Y13>0%<33%)X13,"Strong",IF(AND(Y13>67%<100%)X13,"Weak",IF(AND(Y13>33%<67%)X13,"Medium")))
Instead of saying "Strong" it's throwing up the default Excel FALSE
for some reason.
I have also tried these and none work:
=IF(AND(Y13>0%<33%X13),"Strong",IF(AND(Y13>67%<100%X13),"Weak",IF(AND(Y13>33%<67%X13),"Medium")))
=IF(AND(Y13>0%,X13,<33%,X13),"Strong",IF(AND(Y13>67%,X13,<100%,X13),"Weak",IF(AND(Y13>33%,X13,<67%,X13),"Medium")))
=IF(AND(Y13>0%X13<33%X13),"Strong",IF(AND(Y13>67%X13<100%X13),"Weak",IF(AND(Y13>33%X13<67%X13),"Medium")))
This is the one I thought should work but also didn't:
=IF(AND(Y13>0%<33%),X13,"Strong",IF(AND(Y13>67%<100%),X13,"Weak",IF(AND(Y13>33%<67%),X13,"Medium")))
That last one creates the error "Too many arguments" but removing (some) commas didn't fix it.
Thanks in advance to anyone that can see what's going wrong.
The problem is your AND
s. AND(Y13>0%<33%)X13
is not proper. It should be written as:
AND(Y13>0.0*X13,Y13<0.33*X13)
So:
=IF(AND(Y13>0.0*X13,Y13<0.33*X13),"Strong",IF(AND(Y13>0.67*X13,Y13<1*X13),"Weak",IF(AND(Y13>0.33*X13,Y13<0.67*X13),"Medium")))
But there is an easier way with CHOOSE and MATCH:
=CHOOSE(MATCH(Y13/X13,{0,0.33,0.67}),"Strong","Medium","Weak")