Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Setting Named Ranges

    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Yes COUNT counts only numbers. COUNTA counts non-empty cells: text, numbers, and even errors.

    Steve

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just as an FYI, from 2007 onwards you can use whole columns in array formulas, though it is still not a good idea!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •