Shorter IF formula when condition and Value-If-True are the same

[mrblister]

This happens to me once in a while, and I'm wondering if there's a simple way around it.

The standard If-then function is: If(Condition, True, False).
But sometimes the Condition and the True-path are the same. For example, I want to test if cell B1 is 5. If so, that's the answer, otherwise so whatever else. ie IF(B1=5, B1, C5).

It becomes an issue if the condition is huge (with long, nested formula), you are then writing the same formula twice. E.g. IF([very_long_formula_A]<>0, [very_long_formula_A], 2).

Is there a way around this so you don't need to write the same formula twice?

 

[jasonb75]

Check result IF [very_long_formula_A]>[some_value].
For example, if [some_value]>2.
Then this SHOULD work:

IFERROR(1/(1/(max(2-[very long formula],0))),2)

And so on. Thanks a bunch!

Isn't that just an over complicated alternative to

=MIN([very long formula],2)

 

[mrblister]

Isn't that just an over complicated alternative to

=MIN([very long formula],2)

Actually, the "2" at the end represents "do something else" (e.g. another calculation). The idea of the function is to not have to repeat the [very long formula] section in an IF-THEN scenario.

There's actually an error with my last formula. Here's the fixed version:

Check result IF [very_long_formula_A]>=[some_value].
For example, if [some_value]>=2.
Then this SHOULD work:

=IFERROR(2-(1/(1/(max(2-[very long formula],0)))),"Run calculation 2")

@Eric W Something that has me stumped is the scenario IF [very_long_formula_A] Is Blank (if result is blank/""/empty, then "do something else", otherwise keep the result, without duplicating the [very_long_formula] section).

 

[Eric W]

The double reciprocal trick only works on numeric data. I'm afraid I don't know an equivalent trick for character data, unless you want to try the V function that Dave Patton suggested. If you have a specific [very long formula] you want to show us, we might be able to find a way to shorten it.

 

[jasonb75]

the scenario IF [very_long_formula_A] Is Blank (if result is blank/""/empty, then "do something else"

The original suggestion of

=IFERROR(1/(1/([very long formula])),"do something else")

will still work with blank / empty cells if the valid result of the original long formula is numeric.

If the valid, not blank result of [very long formula A] is a text string then you would need to use one of the other alternative methods (helper column, LET, v() UDF, etc).