Basic question about what formulas look at.

[Randy]

Does Excel look at every cell in the range while performing calculations? I
have the following array formula and am wondering if it will slow things down
if I change the 12000s to 65536s. By changing the number I would not have to
worry about missing any data but then again if it is going to burn my cpu up
I will leave it as is.

=AVERAGE(IF((SCMatrix!$C2:$C12000="NOW")*(SCMatrix!$Y2:$Y12000>0),SCMatrix!$Y2:$Y12000))

Thanks!

Randy

 

[Toppers]

Out of curiosity I tried the formula with 65536 cells and it was almost
instantaneous on my laptop which is a Pentium 4 processor (2.93GHZ) with 512
MB DDR RAM (so nothing special).

So try it!

 

[Randy]

It will run if I calculate one sheet at a time (2 sheets total). What I did
not tell you is that I have over 250 of these array formulas in one sheet and
over 5000 other formulas in the second sheet. That is why I am wondering if
Excel looks at the whole range in the array or stops at the end of the data.

Randy

 

[T. Valko]

Does Excel look at every cell in the range while performing calculations?

An array formula like the one in your example will process every cell
referenced in the array, whether they're empty or not.

Some functions will only process the used range. =COUNTIF(A:A,B1)

If your data ends on row 1000 and you use references to row 65336 in an
array formula you're wasting calculation time by having to evaluate all
those empty cells.

Instead of using an arbitrary end of reference to ensure you "get" all the
data use dynamic ranges.

http://contextures.com/xlNames01.html#Dynamic

Biff