Problem in Conditional Formatting making Excel Slow.

Limit this range:

$B:$B

Hi,

I would think that if you can sort the data on column B, you could use a more efficient formula. If not, you should probably separate the two tests using
=IF($O14="P",COUNTIF($B:$B,$B14)=COUNTIF($B$14:$B14,$B14),FALSE)
so that the COUNTIF part is only executed if column O contains P.

Formula:

Please Login or Register  to view this content.

Mentioned actual data range. For example $B$2:$B$200

Formula:

Please Login or Register  to view this content.

In my experience there is little to no benefit to be had by limiting the range passed to COUNTIF as it only looks at the used range anyway.

Originally Posted by xlnitwit

In my experience there is little to no benefit to be had by limiting the range passed to COUNTIF as it only looks at the used range anyway.

Genuine question, as I really don't know the answer: how does it know what the used range is without looking at the entire array if the whole column is referenced in the formula?

I don't know, is the simple answer but I have never seen much performance difference when passing entire columns to most worksheet functions. There is an obvious hit if you use an operator on an entire column (like * or / for example) but worksheet functions themselves seem to have some sort of short-circuiting to limit the range they consider, whether that be in the function code itself or in the way that the range is passed to the function.

Thanks. Nonetheless, it is well-known that some formulae that reference entire columns are slower than the same formulae with a limited range. Seems to be a problem with some array formulae.

Yes that is certainly true. Many array formulas use operators on arrays, which are affected by entire columns, but also I think the array nature of the formula leads to multiple evaluations dependent upon the number of input cells, and this, I think, bypasses the short-circuiting feature.

I'm sure you're right. All very interesting.