SOLVED

IFS formula in Excel 2016

New Contributor

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. 

 

Does anyone know how I can resolve this issue?

 

=@IFS(E4>89, 5, E4>69, 4, E4>39, 3, E4>0, 2) is one example of the formula that functions properly in everything EXCEPT Excel 2016.

 

Thanks in advance!

3 Replies

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

Hello @Hans Vogelaar ,

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!

best response confirmed by miniding89 (New Contributor)
Solution

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