Excel ran out of resources while attempting to calculate one or more formulas

Question

I have a workbook to do 'smart'-graphs on my expenses. It's been running for a year and there are now a lot of graphs and expenses. Excel now throws an out-of-resources error whenever I change anything or open the workbook. Thing is, I have lots of resources and its not using 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 a LOT (500 or so) of formulas that are all the same and aim to do "Sum all expenses between date X and Y where description matches -some needle-". The formula I have is this:

=
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 I give Excel more resources? (I'm happy for it to use all my ram, and chug my CPU for a few minutes).

Is there a more efficient way I can do this formula?

I understand that this formula is creating a large grid and masking my expenses list with it, and that for each formula this grid has to get created. Should I create a macro to do this more efficiently instead? If I had a macro, I would want to call it from a cell somehow like

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

Is that possible?

Thanks.

It is possible that a single object has grown too large. There are size limitations in languages (eg .NET) to how big a single object can get. — Pete Garafano, Mar 11 '13 at 00:08
I suppose. I would expect there are other users with far larger and more complicated workbooks than me. I'm surprised that I'm running into issues already with a dataset this small. — flacnut, Mar 11 '13 at 00:13
Is this a nice pretty excel popup error or is this an angry windows error? — Pete Garafano, Mar 11 '13 at 00:15
What does the `--` do before the arrays in your `sumproduct`? never really used it and cant figure out what its for. — NickSlash, Mar 11 '13 at 00:24
@NickSlash, the `--` casts the boolean result as 1 or 0, so the SUMPRODUCT will run on [1,1,123.45] if it matches the date range and description we need. @TheGreatCO, this is a nice excel error. It takes about 2 seconds of processing before the error appears. — flacnut, Mar 11 '13 at 00:29
(1) `SUMIFS` is more efficient than `SUMPRODUCT` (2)In case tghe workbook is corrupt I would try "re-birthing" the file,by right clicking the sheet tab, "Move or Copy",and pick new book (3) Try reducing the scope of the formula to actual used rows — brettdj, Mar 11 '13 at 01:39

score 8 · Answer 1 · answered Jul 22 '15 at 20:50

I had a similar problem where there were a few array formulas down about 150 rows and I got this error, which really baffled me because there really aren't that many formulas to calculate. I contacted our IT guy and he explained the following, some of which I understand, most of which I don't:

Generally when the computer tries to process large amounts of data, it uses multi-threaded calculation, where it uses all 8 processors that the computer tricks itself into thinking it has. When multi-threaded calculation is turned off, the computer doesn't throw the 'Excel ran out of resources...' error.

To turn off multi-threaded calculation, got to the 'File' tab in your Excel workbook and select 'Options'. On the right side of the box that appears select 'Advanced' and scroll down to the heading 'Formulas'. Under that heading is a check box that says 'Enable multi-threaded calculation'. Untick it, then select 'OK' and recalculate your formulas.

Solved it for me, great insight! – Alari Truuts Sep 04 '16 at 10:20 — Alari Truuts, Sep 04 '16 at 10:20

score 2 · Accepted Answer · answered Mar 11 '13 at 01:44

I had a go at creating a function that hopefully replicates what your current equation does in VBA with a few differences. Since I don't know the specifics of your second sheet the caching might not help at all.

If your second sheet uses the same date range for all calls to sumExpenses then it should be a bit quicker as it pre-sums everything on the first pass, If your date range changes throughout then its just doing a lot of work for nothing.

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

If you were going to use that as a UDF, you would have to set `Application.Volatile True` as the first line of the function. Otherwise it would never recalculate. Having lots of volatile UDFs is likely to slow the workbook to a crawl though. If you don't make it volatile, it will only recalculate if you edit the formula in a cell which calls it. If you have 500 cells calling that UDF then that will mean 500 edits. See reference [here](http://msdn.microsoft.com/en-us/library/ff195441%28v=office.14%29.aspx) — barrowc, Mar 11 '13 at 03:33
This is really useful @NickSlash! Will the Cache persist the objects for the next function to use? I was thinking of creating a UDF where first it reads all the expenses into a collection of UserDefinedTypes holding the [date,desc,amnt]. The UDF will check if the cache exists, if so, calculate based on the cache, if not, build the cache then calculate based on it. — flacnut, Mar 11 '13 at 04:29
@NickSlash, I just tested persistence, it works! I will use your answer with some tweaks for my situation, thank you for your help - I wasn't aware of UDF's. — flacnut, Mar 11 '13 at 04:43
@barrowc, in my test `=sumExpenses(D1, E1, F1)` the result changed to the correct value if I changed `D1`, `E1` or `F1`. While its true the result might be out-of-date it still changed. (this is without `Application.Volatile` or modifying the cell with the equation in) I guess this is not what you were referring to? Could you explain a little more? — NickSlash, Mar 11 '13 at 13:01
Sorry, think I get what you mean now. If the source data changes and not the arguments to the function? The function would not return a new result even with `Application.Volatile` at present due to the way the cache works. — NickSlash, Mar 11 '13 at 13:04

score 1 · Answer 3 · answered Jul 17 '16 at 05:55

There could be many causes of this. I just wish Excel would tell us which one (or more) of the 'usual suspects' is committing the offence of RAM hogging at this time.

Also look for

Circular references
Fragmented Conditional formatting (caused by cutting, pasting, sorting, deleting and adding cells or rows.
Errors resulting in #N/A, #REF, #DIV/0! etc,
Over-use of the volatile functions TODAY(), NOW(), etc.
Too many different formats used

... in that order

While you're there, check for

Broken links. A formula relying on a fresh value from external data could return an error.
Any formulas containing #REF!. If your formulas are that messed these may well be present also. They will not cause an error flag but may cause some unreported errors. If your formulas are satisfied by an earlier condition the part of the formula containing #REF! will not be evaluated until other conditions prevail.

score 1 · Answer 4 · answered Nov 22 '22 at 21:33

Fragmented conditional formatting was the case for me.

Older versions of the same workbook did not have an issue. Today, I cut/pasted many cells and the issue started occurring.

Removing the columns where I was cutting/pasting resolved the issue for me. This is difficult to diagnose since conditional formatting does not immediately standout like normal formulas.

score 0 · Answer 5 · edited May 16 '23 at 05:31

I removed the #N/A, #REF, #DIV/0! etc. Then Copied all the columns that formulas and pasted as number. But still had same error. I got to the 'File' tab in my Excel workbook and selected 'Options'. On the right side of the box that appears selected 'Advanced' and scrolled down to the heading 'Formulas'. Under that heading unticked the check box that says 'Enable multi-threaded calculation' but still had the same error. I went to Formulas tab under General and ticked "Enable iterative calculation".

Excel ran out of resources while attempting to calculate one or more formulas

5 Answers5