Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Range and Array Formula (Excel 2003 SP2)

    I am attempting to perform Array math on a dynamic range
    My range named count is =OFFSET(Data!$A$2,0,1,COUNT(Data!C:C),1)
    My range named nme is =OFFSET(Data!$A$2,0,2,COUNT(Data!C:C),1)

    I want to compare every value in nme to a constant and then multipy the result times the count, and then summing the whole thing.

    {=sum(("BOB" = NME)*cnt)}

    This works fine using a standard named range, but not if I use a dynamic range as above.
    Did I miss something or is that just the way it is? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Range and Array Formula (Excel 2003 SP2)

    As you indicate, the formula should be an array formula. Are you sure you confirmed it with Ctrl+Shift+Enter? It works OK for me. You can also use

    =SUMPRODUCT(("Bob"=nme)*cnt)

    as a normal (non-array) formula.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Range and Array Formula (Excel 2003 SP2)

    Thanks. I found it. Stupid mistake
    My range named count is =OFFSET(Data!$A$2,0,1,COUNT(Data!C:C),1)
    Changed to
    My range named count is =OFFSET(Data!$A$2,0,1,COUNT(Data!$C:$C),1)

    Note the absolute range reference in $C:$C in the new version. I thought I had done array math on dynamic ranges before!

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Range and Array Formula (Excel 2003 SP2)

    You made your named range a bit too dynamic indeed.
    If you defined the range as

    =OFFSET(Data!$A$2,0,1,COUNT(Data!C:C),1)

    whilst in cell A1 and then had the formula in cell B1, to cell B1 your named range is:

    =OFFSET(Data!$A$2,0,1,COUNT(Data!D),1)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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