Forum Discussion
Solved
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...
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)
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?
Thanks
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?
Thanks
Since 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.