Array Formula

[Acropolis]

I have an array formula in a sheet, which looks through a table of data to find the MAX value from this data, if a serial number matches a serial number for that particular site.

The formula itself works absolutely fine and returns the correct value. The problem I have having is when I try and copy it for all the sites in on the sheet (241 in total). If I copy it one at a time it works fine, it I try and copy more than that, then the whole PC hangs, not just Excel, windows crashes. I have an I5 Sony Vaio with 8gb RAM, it pushes the use to 7.91gb then Windows freezes completely and I have to turn off and back on again.

Its looking through a lot of data for the values I appreciate, but it shouldn't be crashing out like this, when I copy only one cell it does it fine, only when I try and copy more than one at a time, and the resources don't spike when I copy only 1.

Anyone have any idea's on what could be causing this?

Formula is - {=IF(A9="","",MAX(IF(Readings!F:F=F9,Readings!H:BC)))}

 

[Rx_]

Well, that might be because you have auto-updating (calculation) turned on?
Before adding a bunch of code values like the array below, the code:
3560 ObjXL.Calculation = xlAutomatic
3570 ObjXL.ActiveWorkbook.PrecisionAsDisplayed = False
570 ObjXL.DisplayAlerts = False
The solution is to turn off automatic calculations, which you can do from the Options dialog box. To get there in Excel 2010, click the File tab, and then select Options in the left pane. In Excel 2007, click the round Office button, and then click the Excel Options button at the bottom of the drop-down menu.

Once you're in the Options dialog box, select Formulas in the left pane. For Workbook Calculations, select Manual.

Just remember that the numbers won't be correct until you either save the file or press F9. (or go turn Automatic Calculations back on)

ObjXL.Selection.FormulaArray = _
"= AVERAGE(IF((SUBTOTAL(3,OFFSET(R[4]C[20]:R[" & intMaxRecordCount & "]C[20], ROW(R[4]C[20]:R[" & intMaxRecordCount & "]C[20])-MIN(ROW(R[4]C[20]:R[" & intMaxRecordCount & "]C[20])),0,1)))*(R[4]C[15]:R[" & intMaxRecordCount & "]C[15]= ""Original""),R[4]C[20]:R[" & intMaxRecordCount & "]C[20]))"
In one case, my code creates about 12 of these in sequence.

 

[NBVC]

You should never use whole column references with Array Formulas... it's way too expensive. Try to use the minimum number of rows you think you need, or create a Dynamic Named Range.

Also, if add a helper column, say in BD with formula like:

=MAX(H2:BC2) copied down, then you can use

=IF(A9="","",MAX(IF(Readings!F$2:F$1000=F9,Readings!BD$2:BD$1000)))

this limits the array formula to a single column to search through for max instead of almost 50 columns.