Array Formula

[Mark O'Brien]

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 ]

[Mark W.]

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

[Mark O'Brien]

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 ]

[Mark W.]

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.

[Mark O'Brien]

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.

[Mark O'Brien]

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.

[Mark W.]

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 ]