Possible to have a named array formula?

[Kel Good]

Is it possible somehow to define a array formula and give it a name like you
do normal named formulas, so it can be used in a cell like so:

=MyNamedArrayFormula

Thanks.

 

[Tom Ogilvy]

Yes.

Insert Name Define
Name: MyNamedArrayFormula
RefersTo: =Sum(($A$1:$A$10=5)*($B$1:$B$10=2)*$C$1:$C$10)

If you want to use relative addresses, then these will be relative to the
activecell. With named ranges, best to use absolute references in most
cases.

 

[Kel Good]

Hi Tom,

Thanks for your reply.

What makes this an array formula rather than just a normal formula? It looks
like the latter, without the { } around it.

Kel

 

[Tom Ogilvy]

It is written as an array formula - it isn't written as a normal formula.

Granted, in column C, entered in cell C2 without array entry, it will use
implicit intersection, but in a named range, implicit intersection isn't an
issue.

 

[Guest]

I think so. Take a look here: http://j-walk.com/ss/excel/odd/odd10.htm

 

[Peter Huang [MSFT]]

Hi Kel,

Does Tom's suggestion help you?
If you still have any concern, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.