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):

For strict inequality comparisons use `SQRT(_)^2`

as pointed out by @Tom Sharpe.

- Can Excel retain 2 paste special options for a linked cell?
- Excel - How can we replace multiple characters or whole words in a cell using LAMBDA()
- Is there a way to combine COUNTIF and FILTER?
- Powershell Excel Autofilter RGB Colors
- Registering COM Object on Windows 64-bit For Use In Excel-32 Bit Is Failing
- Shift data in excel Sheet to left to remove empty cells and include Header names
- If statement in Worksheet_Change not working with On Error Resume Next
- is there a excel formula which will give me the highest year from column F, corresponding to every company code in column c
- Parse Full Name Into Parts
- Transfer data to other sheets/ranges based on dropdown menu on home page
- Excel formula to check a text value from a cell against table's first column and return a value from the table second column
- Excel Multiple Ranges - need different answers for each range (step function)
- VBA Workbooks stop working with upgrade to Excel 365 - VBA Compiling Issue?
- How can I search a range of multiple rows and columns for a value and return it's header?
- If File = "False" Application.GetOpenFileName Error 13 Type Mismatch
- Calculating percent error of each element of a list given a list of ratios
- SWITCH Function with multiple hits - Power Query
- Recursive Lambda to join full parent-child hierarchy
- Excel Conditional Formatting with PowerShell
- How to sort an Excel array in place
- No Such Interface supported while copying worksheets from source Workbook to destination Workbook - Excel VSTO Addin
- Excel formula for complex 2d table sum
- Export Word document as PDF using Excel VBA
- Lookup multiple criteria from column 2 in a table and return the value in column 1 if all match
- Why is the page header not saved when I save and close a workbook?
- ADODB recordset recordcount always returns -1
- Sum cell values below until a blank cell with Python
- VBA empty rows getting filled after running code
- Laravel Excel upload and progressbar
- FileSystem.FileCopy vs objFSO.CopyFile