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?

 

[Joe4]

One way is to use a "helper" column to do the calculation, then the IF can reference the "helper" column addess instead of having the whole long formula.

There may be other options too, depending on the relationship between the values in the cells.
Can you tell us a little bit about what is in the two cells?
Is there any pattern?
You might be able to use something like a MAX or MIN function, if there is a certain pattern or rule that will always be true.

 

[jasonb75]

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

The only consistent way around it is to use the LET function which you will not have in excel 2019.
There may be possible workarounds depending on the specific long formula and whether or not there are known limits to the potential results, but there would be no generic fix.

 

[Eric W]

If you're checking to see if the result of [very long formula] is 0, you could try this ugly hack clever trick:

=IFERROR(1/(1/[very long formula]),2)

The reciprocal of 0 gives a #DIV/0! error, which triggers the IFERROR. If it's non-zero, the second reciprocal changes it back. You could also use EXP(LN( to check for negative numbers, but that's really out there.

 

[jasonb75]

You could also use EXP(LN( to check for negative numbers, but that's really out there.

Or something on the basis of, =IFERROR(TEXT([very long formula],"0;;;")+0,2)

 

[Dave Patton]

Consider
a) Let which is available in Excel 365 or
b) the UDF V(). I first saw this UDF on the forum in about 2002.
Let is more advanced than the UDF V.
If a value is used multiple times in a formula and it is long, using the available Let or V() will make the formula more efficient and/or easier to read. With this overly simple example, the first example calculates the sum 3 times versus E3 and E4 that calculate the sum once.
As mentioned above, alternatives including a helper cell can be considered.

T202108b.xlsm
	A	B	C	D	E
1
2		Product	Amount		142.80
3		A	10	With UDF V()	142.80
4		B	12	With LET Excel 365	142.80
5		C	14
6		D	16
7		E	18
8		F	20
9		G	22
10		H	24
2bb

Cell Formulas
Range	Formula
E2	E2	=IF(SUM(C3:C10)>100,SUM(C3:C10)*1.05,SUM(C3:C10)*0.95)
E3	E3	=IF(V(SUM(C3:C10))>100,V()*1.05,@V()*0.95)
E4	E4	=LET(Amt, SUM(C3:C10), IF(Amt>100,Amt*1.05,Amt*0.95))

The UDF follows

Public Function V(Optional vrnt As Variant) As Variant
' Stephen Dunn
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

 

[mrblister]

Is there any pattern?

[very_long_formula_A] appears twice; in the Condition and the If_True (or If_False), so trying to reduce that to one instance.

The IF Condition varies, but variants that seem to appear often are: [very_long_formula_A]=[some_value] (or >, <>, ect.), and [very_long_formula_A]=0.

 

[mrblister]

@Dave Patton thanks for the examples. Never seen "Let" in action before; hopefully it will come to desktop Excel in the future.

 

[jasonb75]

hopefully it will come to desktop Excel in the future.

Not for existing versions but it will possibly be in the next version, based on the release cycle of previous versions that should be excel 2022 which should be due out in the next month or so. I haven't seen anything to confirm this but it may have been announced somewhere.

 

[mrblister]

If you're checking to see if the result of [very long formula] is 0, you could try this ugly hack clever trick:

=IFERROR(1/(1/[very long formula]),2)

The reciprocal of 0 gives a #DIV/0! error, which triggers the IFERROR. If it's non-zero, the second reciprocal changes it back. You could also use EXP(LN( to check for negative numbers, but that's really out there.

OOOHH. That IS a clever trick! Yeah If [very long formula] is 0 is a commonly recurring variant case for me.

Hmmm... Let's see if we can expand on this.

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

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

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!