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

0 votes

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?

Dec 15, 2022 in Others by Kithuzzz
• 29,060 points
52 views

1 answer to this question.

0 votes

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 :

Greater or equal than example

answered Dec 15, 2022 by narikkadan
• 53,920 points

Related Questions In Others

0 votes
1 answer

How do I run a VBA Sub routine continuously when working in a Workbook and not only when the Workbook is opened?

on Thisworkbook, put: Private Sub Workbook_Open() Call checkPW(True) End Sub Then ...READ MORE

answered Jan 14 in Others by narikkadan
• 53,920 points
45 views
0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 53,920 points
216 views
0 votes
1 answer

How do I set the default paste special in excel to paste only values

I paste the values with a keyboard ...READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 53,920 points
471 views
0 votes
1 answer

How to get rid of a #value error in Excel?

Changing the format to "Number" doesn't actually ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,920 points
137 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
215 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
395 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,630 points
177 views
0 votes
1 answer

How do I protect all worksheet in an Excel workbook with a single click?

VBA Code : Dim ws as Worksheet Dim pwd ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 53,920 points
73 views
0 votes
1 answer

How do I combine the first character of a cell with another cell in Excel?

Try this: =CONCATENATE(LEFT(A1,1), B1) READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 53,920 points
105 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP