Array Formula
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Array Formula

  1. #1
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm seeing a lot of array formulas posted here these days.

    Out of interest, is there any kind of guideline as to when array formulas will cause a GPF in Excel?

    EDIT:: For example, I tried a couple of array formulas in a blank workbook and rather unscientifically crashed excel by copying the formula down past row 4000.
    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-09 12:43 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm afraid to ask, but what's GPF?

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    General Protection Fault.

    Excel go bye bye taking data with it.

    For example, I tried your Array Formula for that 9*1 + 8*2 etc etc problem, then I copied A1:B1 all the way down to row A4300:B4300. It wasn't actually a GPF, but it did return a memory error and Excel died. *sniff.

    http://www.mrexcel.com/board/viewtop...4558&forum=2&3

    This was the formula I used.

    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-09 12:47 ]

    [ This Message was edited by: Mark O'Brien on 2002-04-09 12:48 ]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In my 18 some years of using Excel I don't believe that I've ever crashed Excel with an array formula, but then I haven't tried to populate all 16,777,216 cells with data either.

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I just know that you should use them judiciously and it's not recommended to throw them in a spreadsheet willy nilly.

    I am the kind of person that tries to fill in 16,777,216 cells with data, just to see what happens. (usually the machine freezes for a day or so, sometimes it results in a small electrical fire)

    Actually, it would take over a day, I think I once worked out how long it would take using VBA.



  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This doesn't really answer my question, but here's what I pulled from the Knowledge Base:

    http://support.microsoft.com/search/...;en-us;Q256567

    65,472 which is 65,536 minus 64 is the maximum in XL2000.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-09 12:53, Mark O'Brien wrote:
    I just know that you should use them judiciously and it's not recommended to throw them in a spreadsheet willy nilly.
    Really, the only thing that you need to guard against is the excessive use of array formulas that are directly or indirectly dependant upon cells whose formulas are volatile (e.g., OFFSET, NOW, etc. -- see http://www.decisionmodels.com/calcsecretsi.htm for more). Such formulas can set off a cascade of recalculations that can involve array formulas themselves and lead to excessive recalculation time.

    Once this is understood the problem is manageable. Like most computing problems there are tradeoffs among speed, size, and capability. Beware of naysayers who deal in absolutes!

    [ This Message was edited by: Mark W. on 2002-04-09 15:59 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com