Excel running out of resources - Unable to evaluate formula

[spydey]

So I have run into a scenario that I have never had.

I have a formula, which I have used before but on a lesser population of data, and it has been working fine.

However, I now have a much larger volume of data I need to run it on.

When I run the formula, after about 5 minutes, it comes back with an error message stating that Excel has run out of resources and is no longer able to evaluate the formula.

Here is the formula:

{=IF(OR(ISBLANK(B2),ISBLANK(C2),ISBLANK(D2)),"",SUM(--(MMULT((ROW(INDIRECT(B2&":"&D2))>=TRANSPOSE(IF(Events!$A$2:$A$5405=A2,IF(Events!$B$2:$B$5405="Yes",Events!$C$2:$C$5405))))*(ROW(INDIRECT(B2&":"&D2))<=TRANSPOSE(IF(Events!$A$2:$A$5405=A2,IF(Events!$B$2:$B$5405="Yes",Events!$D$2:$D$5405)))),Events!$C$2:$C$5405^0)>0)))}

Entered with CSE.


The Items sheet has about 6900 records.
The Events sheet has about 5400 records.

Items is comprised of:

ID
Start
Middle
End
# Days


Events is comprised of:

ID
Status
Start
End

When I have run it previously, the Items had like 1500 records and the Events had like 2000 records. It would take like 20 mins but then give me the results.

I am running Excel x64, on windows 7 pro x64, i7-4600M, 8GB ram, 40GB of HDD free.

I did notice that excel maxes out at about 2GB of ram usage and then just stops.

I have tried to limit the number of cores it uses, I have tried manual calculations, etc.

Any ideas on what might be causing it and how I can resolve it?????


-Spydey

 

[spydey]

I think I might have found possibly part of the issue.

It appears that some of my data has some inconsistencies that I did not foresee.

For example: In my Items sheet, some of the Ends are dates prior to the Starts.

Start: 10/24/2011
End: 03/27/1967

And sometimes there wouldn't be a start date but there would be an end date.

I have corrected those inconsistencies and am going to run the formula in smaller batches, then larger and larger until (hopefully) I can run it for the entire population in one go.

Wish me luck!!!

-Spydey

 

[spydey]

Yup, that was it. The dates being incorrect caused the whole system to lock up.

I fixed the dates (24 records out of 6900+ records) and it runs smooth as butter now......

I can't believe I wasted a day and a half trying to figure this out.

Uninstalled Office, re-installed office, restart my PC, assigned only 2 out of 4 cores to excel, disabled HT, a number of other items.

But in the end it turned out to be the dates being the issue.

How frustrating!!!

-Spydey