IF + SWITCH formula with a non-numeric True / False value break subsequent formulas

IF + SWITCH formula can’t have a non-numeric value as True / False argument. If it does, the result of this formula is going to be ALWAYS non-numeric and thus can’t never be used as an input for calculation in subsequent formulas.

For instance, if I have IF(Column1=“ABCD”,999999,“nonsense”)… the result of this formula is always non-numeric, no matter what the result. Even if the result is TRUE (that is Column contains ABCD), the result of the formula is non-numeric text 999999.

To get numeric result, I have to change the formula to IF(Column1=“ABCD”,999999,“000000000000000”) and then I’ll get 999999 as numeric result.

Same goes for SWITCH formula.

This is an issue when the result of the IF or SWITCH formula is an input or variable in a subsequent formula because the result of the subsequent formula(s) produces an #ERROR!

This is not a show-stopper but reduces the usability if I can’t have text “nonsense” as the result if the result of first formula is FALSE.

What we need is the first formula to produce a numeric result 999999 and text result “nonsense”, so that the subsequent formula(s) will still work when it receives the input 999999 from the first formula.

Hope I expressed it clearly.

OK solved.
Incredible, why do I need another formula to convert it to number. Crazy. It only increases the size to already massive formulas I have. It will take a rocket scientist to fix the formulas one day if they break …

Here is the solution … Need another formula…

VALUE({NumberIWant}&’’)

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.