There are various approaches but it all rather depends on the nature of "Complex Formula" ... ie what it returns (data type), what it is you're looking to account for.
Double Evaluation is often inefficient... from XL2007+ onwards MS finally decided to give us the looooooong overdue IFERROR function which means we can handle errors without need for double evaluation irrespective of data type consistency of value being returned.
Prior to XL2007 avoiding double evaluation with formulae alone can be cumbersome and the techniques available will only be applicable in certain situations...
Continuing the error theme - assume your calculation returns a number or an error and you wish to return 0 instead of an error value:
Here we add the handler without need for double evalution of complex formula
The same logic can be applied where text is being returned (as long as string does not exceed the char limit)
However, it follows that if the data type returned is inconsistent (might be number, might be text) and you wish to preserve the value in it's original form (ie if number keep as number) then the above approach will not work.
Similarly if you're looking to handle a specific (valid) value then the above might not be feasible either [need to know full logic]
The most basic (yet best of all) approach is to split the calc. into two separate cells:
ie A1 acts as an intermediate cell and may/may not be visible to end user - B1 is the final result cell (references purely hypothetical of course)
This approach not only ensures [complex formula] is calculated only once but also allows for greater flexibility in terms of B1 being able to manipulate the resulting A1 value in a multitude of ways.
Bookmarks