Results 1 to 9 of 9

20060929, 21:55 #1
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Using A Range Name to Contain an Array Formula (2000 and above)
It's not too good to be true. A named formula behaves as an array formula (if appropriate).
Another example: the nonarray formula
=SUM(B2:B10*C2:C10)
results in #VALUE, but if you enter is as an array formula, it results in the same value as
=SUMPRODUCT(B2:B10,C2:C10)
If you create a named formula MySumProduct that refers to
=SUM(SheetName!$B$2:$B$10*SheetName!$C$2:$C$10)
and enter the following as a nonarray formula in a cell
=MySumProduct
it will return the value of the array formula. Very handy!

20060929, 21:57 #2
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Re: Using A Range Name to Contain an Array Formula (2000 and above)
Lovely!
[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20060929, 22:05 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Using A Range Name to Contain an Array Formula (2000 and above)
John Walkenbach mentions it in Excel Oddities: A Named Range is Really a Named Formula

20061002, 03:05 #4
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Re: Using A Range Name to Contain an Array Formula
Hey, great link!
[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20061002, 07:05 #5
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Using A Range Name to Contain an Array Formula
I mention the fact that a named range is a formula here too.
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20061002, 07:16 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Using A Range Name to Contain an Array Formula
True, but WebGenii's main point is that a named formula acts as an array formula.

20061002, 12:40 #7
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Using A Range Name to Contain an Array Formula
I know. Page edited to include that information.
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20061002, 12:48 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Using A Range Name to Contain an Array Formula
Great, useful addition.

20071023, 18:48 #9
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Using Range Name to Contain An Array Formula (all)
<!post=This post,563,589>This post<!/post> began a thread in March, where I was asking about an Array formula from John Walkenbach's Power Programming with VBA (Excel 2000).
Since that time I've been refining my example and thought how useful it would be to have the Student names with the Min and Max change highlighted by conditional formatting. In the attached file you'll find I did this by creating an Array formula for Min and Max value which is then referenced by the conditional formatting. At the time, I couldn't find a method to "get" the Array formula into the conditional formatting dialog box.
I've just taken another run at this using a formula contained by a Range Name. I entered the formula in the Range Name dialog box, and while I can not create the curly brackets used by an Array formula, by creating the formula using absolute references the Named formula will behave as if it is an Array formula. I can then reference the named formula in my conditional formatting without the intervening step of creating formulas in the sheet.[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile