Results 1 to 9 of 9
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 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!

  2. #2
    Super Moderator WebGenii's Avatar
    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using A Range Name to Contain an Array Formula (2000 and above)


  4. #4
    Super Moderator WebGenii's Avatar
    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

  5. #5
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  7. #7
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using A Range Name to Contain an Array Formula

    Great, useful addition.

  9. #9
    Super Moderator WebGenii's Avatar
    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.
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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