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

Question

I know that the following formula can be replaced:

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

With this piece of code:

=IFERROR(LONG_FORMULA, 0)

However, I want 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?

Answer 1

Try this:

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

The fact that the log of a negative number results in an error and that EXP and LN are inverses of one another is used to achieve this.

The major advantage of this is that it prevents you from unintentionally adding mistakes to your spreadsheet when you modify one copy of REALLY LONG FORMULA in your IF statement without also remembering to modify the other copy of REALLY LONG FORMULA.

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 :