Hi guys, quick question - I was wondering if anyone could tell me whether using full column and row references in formulas will adversely affect performance in a spreadsheet, or does it not matter at all?
Eg.
Thanks=SUMIFS($A:$A,$C:$C,1,$D:$D,2) VS =SUMIFS($A$1:$A$1000,$C$1:$C$1000,1,$D$1:$D$1000,2) =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) VS =OFFSET($A$1,0,0,COUNTA($A1:$A$1000),COUNTA($A$1:$Z$1))
fullysic
If you're using full columns in Excel 2007, it will be looking at over a million cells.
Clearly, if you apply a formula to say 100 cells or 1000 cells, it will take less time than if you you apply it to a million cells.
In a recent response to a post, I tested some different options. The formula was only copied down over half a dozen cells ... but you could watch the calculation taking place cell by cell when I used whole column references.
Easy enough to test.
Regards, TMS
It depends on the formula. SUMIF and its ilk are supposed to be optimised to only look at the used range part of the sheet. Other functions will not. Definitely better to try and avoid such references in array formulas.
The formulas you have listed should not really be a problem (though I would generally suggest avoiding OFFSET as it is volatile).
Good luck.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks