Forum Discussion
Using IFS statements not working
LucyPEOffice The formula you shared in column AT is returning text strings, not numbers. "9" is a text string... 9 is a number. This is what I was hinting at in my first reply about cells containing numbers formatted as text. Try ditching the double-quotation marks in your formula to see if that solves it...
=IFS(AQ3>=158,9,AQ3>=135,8,AQ3>=120,7,AQ3>=106,6,AQ3>=91,5,AQ3>=69,4,AQ3>=46,3,AQ3>=23,2,AQ3>=0,1)
LucyPEOffice There's nothing wrong with the formula as written that would explain the incorrect results when dragging down. Do you have the calculation mode set to "Manual" by chance? On the ribbon, go to Formulas > Calculation Options and make sure it's set to "Automatic".
Calculation Options
Results:
IFS Function Results
The only other thing I can think of that might explain the problem would be if the values in column AS are text and the values in column AT are numbers:
IFS with Numbers formatted as Text
Other variations of the same formula you might want to try include:
=IFS(AT3<AS3, "Below", AT3=AS3, "Met", TRUE, "Above")
=SWITCH(TRUE, AT3<AS3, "Below", AT3=AS3, "Met", "Above")
=IF(AT3<AS3, "Below", IF(AT3=AS3, "Met", "Above"))
However, these formulas will likely return the same incorrect results if you don't identify and solve the underlying problem first. Best of luck!
- Thanks thats really helpful
The calculation button was set to 'Automatic'
The only thing I can think of is that the values in column AT3 already have formula in them from a previous calculation. This is the formula in AT3 - =IFS(AQ3>=158,"9",AQ3>=135,"8",AQ3>=120,"7",AQ3>=106,"6",AQ3>=91,"5",AQ3>=69,"4",AQ3>=46,"3",AQ3>=23,"2",AQ3>=0,"1")
I am trying to get it to say if the number in AT3 is smaller than the number in AS three return 'x' and so on but I'm wondering if it cant 'read' AT3 as the value in there is created from a formula as opposed to manual input.
Does that make sense? In which case is there a way to overcome this?
ThanksSince you have quotes around the return values such as "9", the formula returns text, not a number.
Remove those quotes:
=IFS(AQ3>=158,9,AQ3>=135,8,AQ3>=120,7,AQ3>=106,6,AQ3>=91,5,AQ3>=69,4,AQ3>=46,3,AQ3>=23,2,AQ3>=0,1)
Thank you so much this has now solved the problem!
I only learnt to IF and IFS statements yesterday so i knew it must be something I was doing but I couldn't work it out.
LucyPEOffice The formula you shared in column AT is returning text strings, not numbers. "9" is a text string... 9 is a number. This is what I was hinting at in my first reply about cells containing numbers formatted as text. Try ditching the double-quotation marks in your formula to see if that solves it...
=IFS(AQ3>=158,9,AQ3>=135,8,AQ3>=120,7,AQ3>=106,6,AQ3>=91,5,AQ3>=69,4,AQ3>=46,3,AQ3>=23,2,AQ3>=0,1)- Thank you so much this has now solved the problem!
I only learnt to IF and IFS statements yesterday so i knew it must be something I was doing but I couldn't work it out.
