Forum Discussion
Using IFS statements not working
Hi
Please can anyone help. I am trying to use IFS statements to be able to compare data from two different columns.
For example I want to say IF A3<B3 return "Below" IF A3=B3 return "Met" IF A3>B3 return "Above"
The formula I have used is =IFS(AT3>AS3,"Above",AT3=AS3,"Met",AT3<AS3,"Below")
It works for the top cell but when I drag the formula down to cover the rest of the cells in that column it just returns "Below" every time even though that's not the correct return.
Please can anyone tell me where I am going wrong?
Thanks
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)
8 Replies
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)
- Formula seems correct. You may have extra hidden characters to your data.
