Named Ranges aren't working in Array formulas?!

Big Blue

Board Regular
Joined
Mar 24, 2002
Messages
52
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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
{=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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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"))
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top