Formula efficiency

[cmb80]

I'm trying to streamline my workbook.

I have one cell that has 4 or 5 "AND" formulas. Is it more efficient to have and formula's with several conditions all in one cell, or have separate formula's individually in separate cells?

[TMS]

I guess we'd be able to give a more informed answer if we could see the formula, ideally with some sample data.

[MrShorty]

I agree with TMS, it is going to be difficult to give any good suggestions without seeing this in context.

That said, instinct suggests to me that the AND() function is pretty fast, and there will be little difference between =AND(cell reference,cell reference, cell reference) and =AND(boolean expression,boolean expression,boolean expression). I would expect that, if you have traced your bottleneck to this cell/formula, the real bottleneck is in the formulas/expressions that make up the boolean expressions and not the AND() function itself.

One way that separating the arguments out into separate cells can impact calculation speed is when one or more of the arguments is very slow to calculate. If you have 10 slow formulas as arguments to the AND() function and something changes so that only one of the arguments needs to change or be evaluated, Excel cannot separate out only one argument for evaluation. Excel must evaluate all 10 arguments. If you separate each formula/argument into separate cells, then Excel can evaluate only the cell that needs to change. The AND() function can then quickly look at the 10 cells before returning its result.