Array Formula

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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.
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-04-09 12:43
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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/viewtopic.php?topic=4558&forum=2&3

This was the formula I used.

_________________<font color = green> 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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top