Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
799 views
in Technique[技术] by (71.8m points)

error handling - 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...