# Thread: Array formulas (Excel 2000)

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

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