# Thread: Using Range Name to Contain An Array Formula (all)

1. ## 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 non-array 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 non-array formula in a cell

=MySumProduct

it will return the value of the array formula. Very handy!

Lovely!

5. ## Re: Using A Range Name to Contain an Array Formula

I mention the fact that a named range is a formula here too.

6. ## 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.

7. ## Re: Using A Range Name to Contain an Array Formula

I know. Page edited to include that information.

9. ## 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.

#### Posting Permissions

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