Too much data for excel to process... is there a VBA solution for this formula?

[ozelegend]

Hi,

I have 190,000 rows of data and I want to run one formula down to the bottom and run a pivot table off the frequency of the outcomes.

The formula below is put into column H.

IFERROR(IF(B31=$I$1,MAX(D2:D31),"")-IF(B31=$I$1,MIN(E2:E31),""),"")

Column B is Time formatted to text. Column D are High values, column E are low values. What I'm wanting to know is the range of the prior 30 rows (max value in D - min value in E).

The problem is that there seems to be too many rows for the formula to be filled down to the bottom and my computer can't hack it. Is there any way I can do this in VBA so the task in manageable by my PC?

Thanks

 

[mikerickson]

Re: relative reference D2:D31

What cell do you want that whole formula in? H1?

Do you want it dragged down so the formula in the cell below would be
=IFERROR(IF(B32=$I$1,MAX(D3:D32),"")-IF(B32=$I$1,MIN(E3:E32),""),"")

 

[ozelegend]

yes that's correct