Forum Discussion
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.
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!
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)))
3 Replies
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 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!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)))
