CODEWITHSUNDEEP
excelexcel-formula
Mason
Mason

Reputation: 229

Way to not duplicate Excel formula in IF statement

Say I have a relatively computationally expensive and long function f(.) dragged down a column. If the result of the function in any given cell is an arbitrary value x, then I want the value of the cell to be the arbitrary value y. Of course, this can be accomplished quite easily like this

=IF(f(.) = x, y, f(.))

I have two questions:

  1. If the output is x for a cell, will Excel compute f two times because of the IF statement?
  2. If so, is there a way to make the implementation more efficient both visually and computationally without helper columns?

Thank you.

Upvotes: 1

Views: 750

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Here is an example. Say the function is:

A1+A3+A5+A7+A11+A13

and our IF() is:

=IF(A1+A3+A5+A7+A11+A13=99,"top",A1+A3+A5+A7+A11+A13)

To avoid repetition:

=LET(f,A1+A3+A5+A7+A11+A13,IF(f=99,"top",f))

Upvotes: 1

Naresh
Naresh

Reputation: 3034

I guess we may use substitute like

=SUBSTITUTE(f(.),x,y)*1

Then it should not calculate the inner function twice.

enter image description here

Upvotes: 1

Related Questions