This formula:
=IFS(CELL("CONTENTS",D6) <5 , "YES" + STYLE("YES") , CELL("CONTENTS", D6) > 10 , "NO" + STYLE("NO") )
returns the text “#VALUE!” rather than “YES” or “NO”. The Style selected is correct though.
Is there a way to fix this? Thank you.
This is covered in the respective help.
You may see the problem easily, if before creating a complex formula, you check its parts:
="YES" + STYLE("YES")
which is equivalent to "YES"+0, asking Calc to perform a sum of a string and a number, and the arithmetic operation converts the string to number and fails.
Do not use the STYLE() function unless there is a very compelling reason to do so (i.e. to use its functionality with asynchronous Add-In functions), use conditional formatting instead.
And using CELL("CONTENTS";D6) is completely unnecessary, use just the cell reference D6 instead.