Using an IF statement without repeating a long formula

Question

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?

Answer 1

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:

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)

Answer 2

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.