Excel ran out of resources trying to calculate one or more formulas

Question

I have a book to make smart charts for my expenses. It has been working for a year, and now there are many schedules and expenses. Excel now throws an error outside of resources when I change something or open a workbook. The fact that I have a lot of resources, and hardly any of them.

Win8 64bit w/ 8 core CPU and 32GB of ram Office 2013 64bit 

I have 2 sheets, the first sheet called Expenses has 3 columns [Date, Description, Amount] and about 1500 rows of data. The second sheet has LOT (500 or so) formulas that are all the same and are aimed at "Summarize all expenses between date X and Y, where the description matches - some needle -". My formula is as follows:

 = ABS( SUMPRODUCT( --(Expenses!A:A >= DATE(2011,12,1)), --(Expenses!A:A < DATE(2012,1,1)), --(ISNUMBER(FIND(C50,Expenses!B:B))), Expenses!C:C ) ) 

Can Excel provide more resources? (I'm glad that he used my entire plunger and launched my processor several times).

Is there a more efficient way to make this formula?

I understand that this formula creates a large grid and masks the list of expenses with it, and that for each formula this grid should be created. Should I create a macro to do this more efficiently? If I had a macro, I would like to call it from a cell, something like

 =sumExpenses(<startDate>, <endDate>, <needle>) 

Is it possible?

Thanks.

Answer 1

I had a desire to create a function that hopefully repeats what your current equation does in VBA with a few differences. Since I don’t know the features of your second sheet, caching may not help at all.

If your second sheet uses the same date range for all sumExpenses calls, then it should be a little faster, since it pre-sums everything on the first pass. If your date range changes all the time, it just does a lot; nothing works.

 Public Cache As Object Public CacheKey As String Public Function sumExpenses(ByVal dS As Date, ByVal dE As Date, ByVal sN As String) As Variant Dim Key As String Key = Day(dS) & "-" & Month(dS) & "-" & Year(dS) & "_" & Day(dE) & "-" & Month(dE) & "-" & Year(dE) If CacheKey = Key Then If Not Cache Is Nothing Then If Cache.Exists(sN) Then sumExpenses = Cache(sN) Exit Function End If Set Cache = Nothing End If End If CacheKey = Key Set Cache = CreateObject("Scripting.Dictionary") Dim Expenses As Worksheet Dim Row As Integer Dim Item As String Set Expenses = ThisWorkbook.Worksheets("Expenses") Row = 1 While (Not Expenses.Cells(Row, 1) = "") If Expenses.Cells(Row, 1).Value > dS And Expenses.Cells(Row, 1).Value < dE Then Item = Expenses.Cells(Row, 2).Value If Cache.Exists(Item) Then Cache(Item) = Cache(Item) + Expenses.Cells(Row, 3).Value Else Cache.Add Item, Expenses.Cells(Row, 3).Value End If End If Row = Row + 1 Wend If Cache.Exists(sN) Then sumExpenses = Cache(sN) Else sumExpenses = CVErr(xlErrNA) End If End Function Public Sub resetCache() Set Cache = Nothing CacheKey = "" End Sub 

Answer 2

I had a similar problem in which there were several array formulas about 150 lines long, and I got this error, which really puzzled me, because there really aren't many formulas to calculate. I contacted our IT guy and he explained the following, some of which I understand, most of which I do not:

Usually, when a computer tries to process large amounts of data, it uses multi-threaded calculation, where it uses all 8 processors that the computer has poked into its confusion. When multithreaded calculation is turned off, the computer does not throw the error "Excel has finished resources ...".

To disable multithreading, go to the File tab in the Excel workbook and select Options. In the right part of the window that appears, select "Advanced" and scroll down to the "Formulas" heading. Under this heading is the checkbox "Enable multithreaded calculation." Unlock it, then select “OK” and recount the formulas.

Answer 3

There can be many reasons. I just want Excel to tell us which of the (ordinary) suspects is “committing a crime in RAM mode” at the moment.

Also search

• Circular links

• Fragmented Conditional formatting (caused by cutting, pasting, sorting, deleting, and adding cells or rows.

• Errors leading to # N / A, #REF, # DIV / 0! etc.,

• Switching volatile functions TODAY (), NOW (), etc.

• Too many different formats used

... in that order

While you are there, check

6. Broken links. A formula that relies on a new value from external data may return an error.

7. Any formulas containing #REF !. If your formulas are ruined, they may also be present. They will not cause an error flag, but may cause some unacknowledged errors. If your formulas are satisfied with an earlier condition, then part of the formula contains #REF! will not be evaluated until other conditions prevail.