Cell Temp Variable

[bmgpub]

I have a complex cell function of the form:

=if(iserr(complex_thing), error_message, complex_thing)

Is there a way to avoid the function performing the "complex_thing" twice?
Is Excel's internal processing smart enough to pick up on this?

Ideally, I would like to use a temp variable such as
=if(iserr(tmpv=complex_thing), error_message, tmpv)
or some such syntax as
=let(tmpv,complex_thing)&if(iserr(tmpv), error_message, tmpv)

This is just an example. I've run across a number of situations where I'm using a check for validity before using the results where being able to store the results temporarily and only within the context of the cell's function calculation would be beneficial (at least performance wise).

Anyone also need this? Anyone have a workaround?
Can we lobby Microsoft to add such a feature?

Mason

 

[Juan Pablo González]

There is such a thing now, a function called IFERROR. If you don't have it, then use another column to calculate the complex function, then you can reference that column so that "complex function" gets calculated only once.

 

[bmgpub]

Ok, That may be useful for error situations, but I was just using error as an example. What if it were

=if(and(complex_thing>1,complex_thing<10), complex_thing, other_thing)

I undestand that using an additional cell will work, but having so many
interim calc cells gets messy if every cell in a range has to have one or more "temp" cells and you have to string a compound decision tree across a bunch of things.

 

[Juan Pablo González]

The addin MoreFunc.xll has two functions called GETV() and SETV() which could be used then:

IF(AND(SETV(complex)>1, GETV()<10), GETV(), other_thing)