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):
For strict inequality comparisons use SQRT(_)^2
as pointed out by @Tom Sharpe.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…