Search code examples
excelerror-handlingexcel-formulaboolean

How do I avoid repeating long formulas in Excel when working with comparisons?

I know that something like the following

=IF(ISERROR(LONG_FORMULA), 0, LONG_FORMULA)

can be replaced with

=IFERROR(LONG_FORMULA, 0)

However I am looking for an expression to avoid having to type REALLY_LONG_FORMULA twice in

=IF(REALLY_LONG_FORMULA < threshold, 0, REALLY_LONG_FORMULA)

How can I do this?

Solution

  • I was able to come up with the following:

    =IFERROR(EXP(LN(REALLY_LONG_FORMULA – threshold)) + threshold, 0)

    It works by utilizing the fact that the log of a negative number produces an error and that EXP and LN are inverses of each other.

    The biggest benefit of this is that it avoids accidentally introducing errors into your spreadsheet when you change something in one copy of REALLY_LONG_FORMULA without remembering to apply the same change to the other copy of REALLY_LONG_FORMULA in your IF statement.

    Greater than comparisons as in

    =IF(REALLY_LONG_FORMULA>=threshold,0,REALLY_LONG_FORMULA)

    can be replaced with

    =IFERROR(threshold-EXP(LN(threshold-REALLY_LONG_FORMULA)),0)

    Example below (provided by @Jeeped):

    Greater or equal than example

    For strict inequality comparisons use SQRT(_)^2 as pointed out by @Tom Sharpe.