Forum Discussion

miniding89's avatar
miniding89
Copper Contributor
Mar 18, 2022
Solved

IFS formula in Excel 2016

Hello all!    I have created a spreadsheet that contains multiple IFS formulas. They work perfect in Office 365 Excel, Google sheets, etc. However, they seem to return a #NAME in Excel 2016.    D...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Mar 18, 2022

    miniding89 

    That would be

     

    =IF(E4>89, 5, IF(E4>69, 4, IF(E4>39, 3, IF(E4>0, 2))))

     

    If E4 will never contain a number <=0, you can shorten it to

     

    =IF(E4>89, 5, IF(E4>69, 4, IF(E4>39, 3, 2)))

HansVogelaar's avatar
HansVogelaar
MVP
Mar 18, 2022

miniding89 

IFS was introduced in Excel 2019, so it should be avoided in Excel 2016 and earlier.

You can change

=IFS(condition1, value1, condition2, value2, condition3, value3)

to

=IF(condition1, value1, IF(condition2, value2, IF(condition3, value3)))

and similar for more or fewer conditions.

  • miniding89's avatar
    miniding89
    Copper Contributor
    Mar 18, 2022

    Hello HansVogelaar ,

    Thank you for the quick reply!
    Im not sure I fully understand how to separate them.

    I tried: =IF(E4>89, 5), IF(E4>69, 4), IF(E4>39, 3),IF(E4>0, 2) but get a #value return.


    Would it be possible to help with this example?
    =@IFS(E4>89, 5, E4>69, 4, E4>39, 3, E4>0, 2)


    Thanks in advance!

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Mar 18, 2022

      miniding89 

      That would be

       

      =IF(E4>89, 5, IF(E4>69, 4, IF(E4>39, 3, IF(E4>0, 2))))

       

      If E4 will never contain a number <=0, you can shorten it to

       

      =IF(E4>89, 5, IF(E4>69, 4, IF(E4>39, 3, 2)))

Resources