Setting Named Ranges

Topic

New Reply

I have a worksheet in which there are 3 columns; e.g., M, N, & O, with each column as a separate range. Currently there are data from row 1 to row 356. I will periodically add data to the bottom of these columns.

Is there any downside to setting my ranges as $M:$M, $N:$N, and $O:$O? This would allow me to not have to reset the range every time I add data.

If it makes any difference, the ranges are used in array formulas; e.g.,
{=PERCENTILE(IF((Water_Year_Sequential_Month=$B2)*(ISNUMBER(Total_Rainfall)),Total_Rainfall),0.05)}.

Thanks.

Reply | Quote

Replies

One big downside is that array formulas do not work with full columns….

Even if you do the calculations with row2 to the last row, another downside is that with all those unneeded calculations in the array formula the spreadsheet will get sluggish or even stop to calculate at cell entries. Why not use dynamic range names that expand to accomodate the data using the offset function?

For example for column N you could use:
=OFFSET($M$1,0,0,COUNTA($M:$M),1)

This will expand as items are added to col M…

Steve

Reply | Quote

Thanks! I figured setting a whole column as a range was a bad idea.

If I understand correctly COUNTA will work for both text and numbers, whereas COUNT will work for numbers only.

BTW, for those who read this link, this is a good explanation of OFFSET
http://support.microsoft.com/kb/830287

Reply | Quote

Yes COUNT counts only numbers. COUNTA counts non-empty cells: text, numbers, and even errors.

Steve

Reply | Quote

Just as an FYI, from 2007 onwards you can use whole columns in array formulas, though it is still not a good idea!

Reply | Quote