Array Formulas in Large Worksheet

[AndyPaff]

How might I get around the problem of formulas in a large worksheet taking ages to calculate or causing Excel to hang?


I have a large worksheet (300,000 lines x 50 columns) of horse racing data. The data are arranged one line per horse per race with column A holding the race identifiers. I need to normalise the data within each race on a scale from 0 to 1. For small amounts of data I use the formula x-min(r)/max(r)-min(r) in an array formula (where x= the item of data and r =the same data for the whole race).


My problem is, for such a large amount of data, the worksheet just hangs. For a smaller subset (5,000 rows) I switched off auto calc, copied the formulas as normal formulas and then converted them to array formulas. Even this took several hours. Once calculated the data can be saved as csv, the formulas don't need to be recalculated. Should I be looking to do this programatically rather than copying down the rows?

 

[Akuini]

It’s an interesting case. I'm not sure I can help but can you post some sample data & also show the formula?

 

[pvr928]

Hi AndyPaff

I'm with Akuini in wanting to see some sample data and formulae.

However, without that, although 300,000 rows x 50 columns is not a large dataset relatively speaking (less than 0.1% of the total cells on each worksheet - each worksheet has 2 ^ 14 columns (16,384) and 2 ^ 20 rows (1,048,576) or 2 ^ 34 cells (17,179,869,184)), that you are occupying all 15m of these cells with formulae, regardless of whether they are volatile or non-volatile (PS array worksheet formulae are great, but they do impose a large processing penalty), the speed will probably not get much faster by simply re-jigging the formulae.

You could try upgrading to Excel 64bit, but this may not be a total solution either.

You are on the right track with thinking about doing it with VBA. The fastest methods involve arrays, collections and the dictionary object. As long as you are not looping through your 15m cells continuously, my experience suggests the running time for any VBA based calculation would be into the seconds, rather than minutes, and certainly not hours.

The key to all of this, though, is to minimise the number of times you refer to the worksheet. The general philosophy is to read once from the worksheet, perform the calculations in memory, and then write the results once back to the worksheet.

Cheers

pvr928

 

[FDibbins]

You have shown the basis of your formula, but not an example of your actual formula?

Also, things to consider would be using helper columns to try and eliminate the array formulas, and for formulas based on older data that wont change now, convert those formulas to values (copy/paste values)

 

[AndyPaff]

Thanks for your replies everyone. Here's an example of the formula. For simplicity it's just referring to 100 rows.


{=IFERROR((D2-MIN(IF($A$2:$A$100=$A2,D$2:D$100)))/(MAX(IF($A$2:$A$100=$A2,D$2:D$100))-MIN(IF($A$2:$A$100=$A2,D$2:D$100))),"")}


The race identifiers are in column A
The data this cell refers to is in column D
Some races have no data in some columns so I need to trap the errors and leave the cell blank.


In light of what you have said, I can already see where I'm going wrong. I'm doing everything in one hit and calculating the same max and min figures over and over again. I would need 100 helper columns (max and min x 50) but if I could populate these first, all the information would be within the row and I could use normal formulas. However, I bet there's an easier way that I just can't see right now.


Typical output looks like this,

RaceID	aDS	bDS	mDS	aDS-Norm	bDS-Norm	mDS-Norm
201015	61	96	58	0.91	0.93	0.88
201015	67	103	66	1.00	1.00	1.00
201015	40	53	40	0.60	0.51	0.61
201015	46	46	46	0.69	0.45	0.70
201015	54	81	59	0.81	0.79	0.89
201015	51	51	51	0.76	0.50	0.77
201015	0	0	0	0.00	0.00	0.00
201019	0	0	0	0.00	0.00	0.00
201019	71	79	75	1.00	0.98	1.00
201019	60	70	60	0.85	0.86	0.80
201019	71	80	68	1.00	0.99	0.91
201019	71	81	71	1.00	1.00	0.95
201019	54	54	54	0.76	0.67	0.72
201019	57	67	55	0.80	0.83	0.73

<colgroup><col width="64" style="width:48pt" span="7"> </colgroup><tbody>
</tbody>

<strike></strike>

 

[FDibbins]

Not quite sure why you feel you would need that many helper columns? I see 1 for MIN and 1 for MAX? Something like...
IF(A2=ID,D2,"")

I can see if you have a lot of different ID's then yes, you would need extra columns, but those formulas could then be values once you have your answer.
Another thing to consider is using a would be to use a Pivot Table for this?

 

[AndyPaff]

Thanks Ford.

I wasn't very clear but to normalise each of the 50 columns of data within each race I would need to calculate the max and min for each of those 50 columns of data within each race. There are tens of thousands of races.

I had considered a pivot table but again, this would be quite large. I think the best solution would be nested loops in VBA (or indeed any language that will process a csv). These would loop through each column within each race calculating the max and min and the normalised figures. My problem is the last time I did anything like that was in Quick Basic! Time to brush up my skills I think.

 

[AndyPaff]

Sorry to re-surface this one but I haven't been able to crack it and it's driving me mad. Can anyone help?

 

[Aladin Akyurek]

@AndyPaff

Do you have MINIFS and MAXIFS functions on your system?

If you do, try:

=IFERROR((D2-MINIFS(D$2:D$100,$A$2:$A$100,$A2))/(MAXIFS(D$2:D$100,$A$2:$A$100,$A2)-MINIFS(D$2:D$100,$A$2:$A$100,$A2)),"")

which needs just enter.

Otherwise, control+shift+enter and copy down:

=IFERROR((D2-V(MIN(IF($A$2:$A$100=$A2,D$2:D$100))))/(MAX(IF($A$2:$A$100=$A2,D$2:D$100))-V()),"")

where V() is a udf. For the foregoing formula to work, we need to install the V() function using Alt+F11 of which the code is as follows:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

 

[AndyPaff]

Thanks Aladin. I don't have MINIFS and MAXIFS but your array formula and udf are certainly tidier.


Unfortunately, the calculation still hangs due I think, to the huge ranges in the array formula (sheet is 300,000 x 56) . I feel this could be solved by using the evaluate function in VBA. If I could make all the cell references dynamic with the ranges limited to say 100 rows, I could loop through the cells evaluating them one at a time. The array could then be written to the worksheet when the looping has finished. So, the first iteration would be,


var = Evaluate("=IFERROR((D2-V(MIN(IF($A$2:$A$100=$A2,D$2:D$100))))/(MAX(IF($A$2:$A$100=$A2,D$2:D$100))-V()),"")")
Array (row,col)=var


Followed by
var = Evaluate("=IFERROR((D3-V(MIN(IF($A$3:$A$101=$A3,D$3:D$101))))/(MAX(IF($A$3:$A$101=$A3,D$3:D$101))-V()),"")")
Array (row,col)=var


And so on.
This should speed up the calculation as only one cell in a range of 100 is being calculated


My problem is, I don't know how to make the cell/range references in the formula dynamic. I've tried using R1C1 notation in " & Cells() & " and " & Range() & " but it doesn't seem to work.
Does the idea make any sense, I'm a complete VBA Novice?