Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Excel Array Formula Limitation (Excel 2003)

    I was creating an array formula in Excel 2003 yesterday. the formula looked like this:

    {=SUM(IF($A:$A=E$7,IF($B:$B=$D8,1,0)))}

    This formula did not work but gave a #NUM error. IS this out of range of the program? I eventually got it right by changing it to:

    {=SUM(IF($A$2:$A$65536=E$7,IF($B$2:$B$65536=$D8,1, 0)))}

    Any explanation on this?

    TX
    Regards,
    Rudi

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

    Re: Excel Array Formula Limitation (Excel 2003)

    According to Description of the limitations for working with arrays in Excel:
    <hr>The "entire column" rule

    Although you can create very large arrays in Excel, you cannot create an array that uses a whole column or multiple columns of cells. Because recalculating an array formula that uses a whole column of cells is time consuming, Excel does not allow you to create this kind of array in a formula.<hr>

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Array Formula Limitation (Excel 2003)

    Tx for the reply.

    Maybe I can quickly add that Excel 2007 (even with its 1 000 000+) rows does allow this formula. I first created it in 2007, and discovered the error when I transferred it into Excel 2003.

    Just if anyone was interested.

    Cheers
    (Tx Hans)
    Regards,
    Rudi

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

    Re: Excel Array Formula Limitation (Excel 2003)

    That's strange - the MSKB article claims that it applies to Excel 2000, 2002, 2003 and 2007...

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Array Formula Limitation (Excel 2003)

    Hmmm... then I have a sooped up muscle version of 2007... YIPPEEE!!

    See picture below!!
    Attached Images Attached Images
    Regards,
    Rudi

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

    Re: Excel Array Formula Limitation (Excel 2003)

    According to Office Excel 2007 features that are not supported in earlier versions of Excel array formulas referring to entire columns are indeed possible in Excel 2007. The MSKB article has probably not been updated correctly.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Array Formula Limitation (Excel 2003)

    TX again for that note!

    PS: Where you get all these links from!!! Your google must be extra powerful! [img]/forums/images/smilies/smile.gif[/img]
    Regards,
    Rudi

Posting Permissions

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