Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Named Ranges aren't working in Array formulas?!

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to replace cell ranges with named ranges in my array formulas, but I keep getting #NUM errors.

    example: trying to replace A1:A1000 with Range1

    why won't my arrays accept named ranges?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-15 13:19, Big Blue wrote:
    I am trying to replace cell ranges with named ranges in my array formulas, but I keep getting #NUM errors.

    example: trying to replace A1:A1000 with Range1

    why won't my arrays accept named ranges?
    Care to post the array formula in which you want to use range names, along with the type of data that each range houses?

    Aladin

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    {=SUM((salesbase2!$E$2:$E$1000>='02 Holidays'!$A13)*(salesbase2!$E$2:$E$1000<='02 Holidays'!$B13))}

    col E is dates in ddmmyy format, as is
    "02 Holidays' which is a 2 column worksheet of start/end dates for every week in 2002

    I have named E:E as DATEIN and want to use it to replace salesbase2!$E$2:$E$1000

    this formula effectively subtotals a count of log entries by work week.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-15 13:57, Big Blue wrote:
    {=SUM((salesbase2!$E$2:$E$1000>='02 Holidays'!$A13)*(salesbase2!$E$2:$E$1000<='02 Holidays'!$B13))}

    col E is dates in ddmmyy format, as is
    "02 Holidays' which is a 2 column worksheet of start/end dates for every week in 2002

    I have named E:E as DATEIN and want to use it to replace salesbase2!$E$2:$E$1000

    this formula effectively subtotals a count of log entries by work week.
    Array formulas do not accept whole columns as range arguments. DATEIN is E:E, so it cannot be used in your array formula.

    May I propose a different scheme, which allows the relevant range in E to change by additions or deletions.

    I'll assume that both worksheets salebase2 and 02 Holidays are in the same workbook.

    Activate salebase2.
    Activate the option Insert|Name|Define.
    Enter as name DateRecs in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =MATCH(9.99999999999999E+307,salebase2!$E:$E)

    Activate Add (don't leave the Define Name window yet).

    Enter as name DATEIN in (or select it if already available from) the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(salebase2!$E$2,0,0,DateRecs-1,1)

    Activate OK.

    Now go to the worksheet 02 Holidays and in C13 enter either your array formula modified as

    {=SUM((DATEIN>=$A13)*(DATEIN<=$B13))}

    or, ordinarily entered,

    =SUMPRODUCT((DATEIN>=$A13)*(DATEIN<=$B13))

    However, if dates in A from A13 on and in B from B13 on are dates that define effectively calendar weeks, I'd suggest replacing them by a single range in A from A3 on where you create the 52 week numbers and using the following formula in B13:

    =SUMPRODUCT((WEEKNUM(DATEIN)=$A13)+0)

    Note. WEEKNUM is available from Analysis Toolpak (which is available thru Tools|Add-Ins).

    Aladin

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you Aladin
    I think that may be an elegant solution.
    However, I was surprised when I replaced other references to salesbase2!$E2:$E1000 with DateIn, and I wound up getting #N/A

    example below:

    {=SUM((DateIn>='02 Holidays'!$A13)*(DateIn<='02 Holidays'!$B13)*(salesbase2!$C$2:$C$1000="POC"))}

    any ideas?

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 06:53, Big Blue wrote:
    Thank you Aladin
    I think that may be an elegant solution.
    However, I was surprised when I replaced other references to salesbase2!$E2:$E1000 with DateIn, and I wound up getting #N/A

    example below:

    {=SUM((DateIn>='02 Holidays'!$A13)*(DateIn<='02 Holidays'!$B13)*(salesbase2!$C$2:$C$1000="POC"))}

    any ideas?
    The ranges need in the array need to be the same, I suspect salesbase2!$C$2:$C$1000 is not the same size range as DateIn.

    you can do the same as before with col C

    =OFFSET(salebase2!$C$2,0,0,DateRecs-1,1)
    and use a name appropriate to the column.

    Then use:

    {=SUM((DateIn>='02 Holidays'!$A13)*(DateIn<='02 Holidays'!$B13)*(AppropriateName="POC"))}

    or

    =SUMPRODUCT((DateIn>='02 Holidays'!$A13)*(DateIn<='02 Holidays'!$B13)*(AppropriateName="POC"))




    "Have a good time......all the time"
    Ian Mac

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-16 06:53, Big Blue wrote:
    Thank you Aladin
    I think that may be an elegant solution.
    However, I was surprised when I replaced other references to salesbase2!$E2:$E1000 with DateIn, and I wound up getting #N/A

    example below:

    {=SUM((DateIn>='02 Holidays'!$A13)*(DateIn<='02 Holidays'!$B13)*(salesbase2!$C$2:$C$1000="POC"))}

    any ideas?
    It is not a good idea to mix fixed ranges with dynamic names ranges in an array or SUMPRODUCT formula, because it may lead to the problem of unequal sized ranges.

    Give also a name, say, Crange, to the range $C$2:$C$1000 in salesbase2 following the proc I described by using the following formula in the Refers to box:

    =OFFSET(salebase2!$C$2,0,0,DateRecs-1,1)

    and change the array formula:

    {=SUM((DateIn>='02 Holidays'!$A13)*(DateIn<='02 Holidays'!$B13)*(Crange="POC"))}


    By the way, In which sheet is this formula?

    Aladin

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Again.
    The formulae are actually ina 3rd worksheet titled SUMMARY.

    I take it then, that one has to use either all named ranges, or all cell ranges, but not a combination of both?

    Any advantages (ie speed) to using SUMPRODUCT instead of Array?

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    [i]
    On 2002-04-16 07:53, Big Blue wrote:
    Thanks Again.
    The formulae are actually ina 3rd worksheet titled SUMMARY.

    I take it then, that one has to use either all named ranges, or all cell ranges, but not a combination of both?

    The main reason not to mix up is that the OFFSET formulas (if set up correctly) will pick out the exact used range which might not be equal in size to the size fixed range, making the formula run havoc.

    Any advantages (ie speed) to using SUMPRODUCT instead of Array?

    Presumably not in speed. When both applicable, with SUMPRODUCT you don't have the hassle of control+shift+enter when entering and after an edit. You might experience some improvement in performance because of dynamic name ranges, but not enough. If your data is date-oriented (consists of ascending dates) and since you're doing counts and totals, you can attempt to further reduce the ranges the array or SUMPRODUCT formulas look at. You can find an example for this approach at this board in a reply to a problem posted by Andonny. If interested, use Search.

    BTW, I asked for the sheet location of the formula in case it was in 02 Holidays in order to further simplify the formula. But, no luck.

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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