SOLVED

# Using IFS statements not working

Copper Contributor

# 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

8 Replies

# Re: Using IFS statements not working

Formula seems correct. You may have extra hidden characters to your data.

# Re: Using IFS statements not working

@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!

# Re: Using IFS statements not working

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
best response confirmed by HansVogelaar (MVP)
Solution

# Re: 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)``

# Re: Using IFS statements not working

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)

# Re: Using IFS statements not working

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.

# Re: Using IFS statements not working

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.

# Re: Using IFS statements not working

@LucyPEOffice Glad to hear it! You're very welcome :)

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: 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)``