Named Ranges aren't working in Array formulas?!

[Big Blue]

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?

[Aladin Akyurek]

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

[Big Blue]

{=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.

[Aladin Akyurek]

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

[Big Blue]

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?

[Ian Mac]

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"))

[Aladin Akyurek]

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

[Big Blue]

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?

[Aladin Akyurek]

[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