2 Questions regarding Excel Efficiency

[UniMord]

I have 2 questions regarding Excel efficiency.

1. Do Excel's logical functions always evaluate every argument? Specifically, let's say I have a formula like this:
   

   [B]=IF([COLOR=navy]AND([/COLOR][/B]A2>A1[B][COLOR=navy],[/COLOR][/B] VLOOKUP(B2, RANGE_IN_DIFFERENT_WORKBOOK, 7) > MIN(SOME_RANGE)[B][COLOR=navy],[/COLOR][/B] SOME_CRAZY_EXPENSIVE_VOLATILE_FORMULA[B][COLOR=navy])[/COLOR][/B], etc...[B])[/B]

   Normally, I'd just multiply the conditions, rather than invoke AND (just looks cooler ), however, if AND evaluates from left to right, and quits after the first FALSE, then AND would be the much cheaper way to go.
   
   
2. Is using a formula in an Advanced Filter any cheaper than just manually applying the formula to every row and then filtering? For example, I can add a column with a multi-conditioned formula that evaluates to TRUE/FALSE, then filter that column, or I can use that formula just once in Advanced Filter. Is this more efficient, or is Excel still applying the formula, under the hood, to every row, so nothing is gained?

 

[Derek Brown]

There are tools available that will give you timings for workbooks/worksheets.

I use FastExcel:
http://www.decisionmodels.com/fxlfaq.htm
(you will also find some useful information about optimisation)

Also have a look at the following Podcast:
http://www.youtube.com/watch?v=yolP3Bapc-w&playnext=1&list=PL4427661B2B879B7F

 

[RoryA]

1. Yes. (try it with a UDF)
2. Depends what you mean by cheaper.

 

[shg]

If you use a sequential construct, Excel only evaluates as far as needed:

=if(cond1, if(cond2, answA, answB), answC)

cond2 only gets evaluated if cond1 is true.

 

[UniMord]

2. Depends what you mean by cheaper.

Rorya: Regarding the Advanced Filter, I'm talking cheaper in terms of calculation time, which, upon further consideration, it would seem to me (correct me if I'm wrong), that the Advanced Filter would be cheaper since it doesn't automatically recalculate, unlike formulas in a table column.

 

[RoryA]

Correct, though for the same reason it is probably more expensive at filter time. The single formula is also better in terms of file size.