Evaluate Sub-formula Only Once

Hi,

Is there any way to evaluate a subformula which appears more than once within a formula only once?

For example, a common thing which I do is to replace an error value with some default like this:

=IF(ISERROR(f()), 0, f())

where f() is some excel formula.

Is there a way to only evaluate f() once? I tried using named formulae but it seems not to work (at least, for UDFs it didn't).

Alternatively, is there a function similar to the sql ISNULL() function to specify an alternative value if the value of a sub-formula is an error?

Thanks

Invest in Excel 2007/2010.

It has an IFERROR function.

For example, =IFERROR(yourfunction, "error message ... or "" ... or whatever")

Or you could look at this approach:

http://www.stutzman.org/index.php/20...for-excel-2003

Regards

Prior to XL2007 you can ape IFERROR via a UDF.

Alternatively if as implied in OP the output of the formula is a consistent data type (eg number) then you can also adopt an approach along the lines of:

Please Login or Register  to view this content.

here the f() formula [whatever it is - we assume non-UDF] is evaluated only once.

for a text output with say default of Null you would use:

Please Login or Register  to view this content.