I'm having difficulty with trying to improve the efficiency of a
nested formula.

I'm using Excel 2007 and a third party COM add-in that provides
additional functions written in C++ (not a free product).

The .XLSB workbook contains two worksheets.

Sheet1 has about 1,000,000 cells, each containing the nested
formula.

Sheet2 is the source of the names (On_Setting, Primary,
Secondary and Tertiary) using cell references, not formulas.

Cust_Func is a third party function and can return a number or a
text based #N/A value - not an Excel #N/A.

Below is an example of the formula.



=IF(On_Setting=FALSE,"",IF(IFERROR(PRODUCT(Cust_Fu nc($A1,Primary),1),
0)0,Cust_Func($A1,Primary),IF(IFERROR(PRODUCT(Cus t_Func($A1,Secondary),
1),0)0,Cust_Func($A1,Secondary),IF(IFERROR(PRODUC T(Cust_Func($A1,J
$2,Tertiary),1),0)0,Cust_Func($A1,Tertiary),"Erro r"))))

=IF(On_Setting=FALSE is linked to an ON/OFF radio button that is
used to disable calculation in case of cells that need to be edited
beforehand.

IFERROR(PRODUCT(Cust_Func($A1,Primary),1),0) is used to test
whether a zero or an #N/A value is returned by Cust_Func($A1,Primary).

If Cust_Func($A1,Primary) evaluates to zero or #N/A, the IF
statement moves to the next IF statement.

If Cust_Func($A1,Primary) evaluates to 0, then
Cust_Func($A1,Primary) is returned.

The only variables that change with each nested IF statement are
the names Primary, Secondary and Tertiary.


*** ISSUES***

1) It takes almost 40 minutes for these calculations to complete.
The option of building an Intel octo-core, hyper-threaded and
overclocked desktop isn't immediately possible.

2) It has been speculated that all IF statements in a nested
formula are evaluated, regardless if the second IF statement evaluates
TRUE. Is this correct ?

3) Is it possible to write a UDF that calls the Cust_Func function
(supported) and iterates through the names until a value greater than
zero is returned and then stops iterating to prevent unnecessary
calculations ?

Easy, right ?



- Ronald K.