Excel Rules of Recalculation within "If x=y then Function A else Function B"

[MJPPaba]

anyone know?

If you have a function as follows:
=If(A1=B1,CountA(C1:C1000000),Sum(D11000000))

when excel performs a recalc does Excel recalc both the true and the false portions of the statement or is it the same as the creating your own User Definied Function and only recalcs the True or False function? I am trying to improve performance on a workbook and if I can get the functions to only recalc where necessary that will save me oodles of time.

Cheers

M

 

[combo]

Definitely, the UDF is slower, firstly, it's volatile, secondly, vba is slower than native excel.
You can find some tips here: [link]

http://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx

[/url].

combo

 

[byundt]

combo,
Why do you say a UDF is volatile? That's not my understanding, and it seems contrary to the occasional need to insert Application.Volatile in a UDF to force it to recalculate.

Brad

 

[byundt]

The IF formula only evaluates the third parameter if the criteria is FALSE.

To prove it, I created the following UDF:

Function test(rg As Range) As Double
MsgBox "UDF Test was called"
test = Application.Sum(rg)
End Function

I then used the following data:
A1 = 3
B1 = 3
D14 =1
F1 formula =IF(A1=B1,SUM(D13),TEST(D14))

As I expected, the formula returned 3. But when I changed B1 to 4, I got a messagebox and then the formula returned 4.

 

[combo]

byundt,
You are right, by default, UDF is not volatile.

combo