Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Location
    Detroit, Michigan, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array formulas (Excel 2000)

    We are modifying a workbook that contains multiple worksheets. One of which is a listing of all the stop records that occured last week. I have added a column to the worksheet that contains an identifier called MetalGrp and added the range name MetalGrps for it. The original array formula {=-SUM(IF(FODS=D$5,IF(LEFT(ZONES,1)="1",IF(cats<>"COM P",stops,0),0),0))} works just fine and returns the proper information. When I add {=-SUM(IF(MetalGrps=$B6,IF(FODS=D$5,IF(LEFT(ZONES,1)= "1",IF(cats<>"COMP",stops,0),0),0),0))} it refuses to acknowledge the new range and returns #/NA. What is going on? I have moved the new MetalGrps range to different positions in the formula to no avail. Thank you for any and all help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Array formulas (Excel 2000)

    Are all your range names the same size?

    It might help if you provide a sample copy so we can test the formula.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array formulas (Excel 2000)

    Sorry, but I have to ask to be certain...

    I assume you're entering the array formula with Ctl Shift Enter and not just typing in the {

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Array formulas (Excel 2000)

    FYI,An alternative array (without so many IFs is:
    <pre>=-SUM(IF((MetalGrps=$B6)*(Fods=D$5)*(LEFT(Zones,1)=" 1")*(Cats<>"COMP"),Stops))</pre>


    Steve

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Array formulas (Excel 2000)

    Hi BAN,

    Typing the '{' before the formula (and '}' after it) would convert it to text. In that case, the cell would simply display the formula, not the '#/NA' result the OP referred to.

    I too have had array formulae fall over when named ranges are used in conjunction with IF tests, whilst using the cell addresses the range refers to works.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Array formulas (Excel 2000)

    I created a test sample and it worked with the rangenames ok (in XL97).

    I did also confirm my suspicion, that if one of the named ranges is a different size than the others, it will yield a #NA error.

    Steve

  7. #7
    New Lounger
    Join Date
    Sep 2001
    Location
    Detroit, Michigan, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array formulas (Excel 2000)

    Thank you all for your suggestions and ideas. I went back and redefined each range in the source area and the formulas started working. The range size was the key. Thank you all, again.

Posting Permissions

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