IFS formula in Excel 2016

[miniding89]

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!

[#2]

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

[#3]

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!

[#4 · Accepted 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)))