CODEWITHSUNDEEP
excelexcel-formula
Sumit Mishra
Sumit Mishra

Reputation: 43

Using an IF statement without repeating a long formula

Can IF check and return the result from a formula without using it a second time inside the cell?

I am talking about using something like this:

IF( LARGE(A2:A21) > 0, LARGE(A2:A21), "No Data Entered")

This is an example/placeholder. My actual formula is a bit longer. I do not want to rewrite the formula (i.e. LARGE() ) within the IF function a second time.

Does anyone know if there is a way to do this?

Upvotes: 4

Views: 2027

Answers (4)

Josh Moore
Josh Moore

Reputation: 381

The LET formula can be used for this exact scenario. You can define the formula as a variable and then within that same cell you can reference the variable in your formula.

The LET formula format looks like this:

=LET(name,name_value,calculation)

Your Example

Original formula:

=IF( LARGE(A2:A7,2) > 0, LARGE(A2:A7,2), "No Data Entered")

Here's how it would work using LET so that you don't have to repeat the formula: Screenshot of LET formula

New formula:

=LET(runnerup,LARGE(A2:A7,2),IF(runnerup>0,runnerup,"No Data Entered"))

Explanation

We create a variable and use the LARGE formula as the value of that variable. We then write our IF statement using the variable name instead of rewriting the formula multiple times.

=LET(name,name_value,calculation)

Variable name: runnerup

Variable value: LARGE(A2:A7,2)

Calculation: IF(runnerup>0,runnerup,"No Data Entered")

Put together it looks like this:

=LET(runnerup,LARGE(A2:A7,2),IF(runnerup>0,runnerup,"No Data Entered"))

This LET function can be used in any Excel formula, and is very useful for shortening long formulas that have repetition.

Optional: Extra complexity

If you want to you can get extra complicated by naming multiple variables.

=LET(name,name_value,name2,name_value2,calculation)

Upvotes: 3

Chronocidal
Chronocidal

Reputation: 7951

Use MAX, Double-Division, and an IfError:

=IFERROR(1/(1/MAX(0,COMPLICATED_FORMULA_HERE)),"No Data Entered")

If COMPLICATED_FORMULA_HERE is >0, then the MAX means that you calculate 1/(1/COMPLICATED_FORMULA_HERE), which is the same as COMPLICATED_FORMULA_HERE (because dividing by a fraction is the same as multiplying by its inverse - 1*(COMPLICATED_FORMULA_HERE/1))

If COMPLICATED_FORMULA_HERE is ≤0, then the MAX means that you calculate 1/(1/0), which is a #DIV/0! error. The IFERROR then changes this into "No Data Entered"

(As I hope is obvious, COMPLICATED_FORMULA_HERE is a placeholder for whatever your actual formula is, much as you have used LARGE(A2:A21) - which is invalid, because LARGE requires 2 arguments)

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152485

You can use an array form of LARGE() that will return an error if no value is greater than 0:

=IFERROR(LARGE(IF(A2:A21>0,A2:A21),1),"No Data Entered")

This would need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

The only way of which I can think would be to first put the LARGE formula into another cell, say A1, and then refer to that cell, e.g.

A1: =LARGE(A2:A21)

=IF(A1 > 0, A1, "No Data Entered")

This avoids having to repeat the possibly long code for the formula, but at the cost of using an intermediate column, which you might not want to do for a number of reasons.

Upvotes: 2

Related Questions