HELP with SLOW Calculating Threads

[karstens]

I have been working with large excel workbooks for years doing reporting for the company I work for. Many of the spreadsheets are complex with some index and matching and several pivot tables.
It was never an issues in the past. We are now on 365 and it seems like since then I keep getting a message on the bottom bar that says calculating threads..usually 4. And it can take 10 minutes or longer to finish.
It is driving me crazy. I don't want to shut off the auto calculate because I need it to be able to do that or if changes get made and I forget to tell it to calculate, my numbers are off. Very frustrating.
I am hoping someone can help me understand why this is happening now when it hadn't in the past, yet all the youtube and research says this has been around for a long time.
I cannot function this way and need to find a fix. Anyone out there that can help this poor old woman from going insane? Thanks SO much!

[Glenn Kennedy]

Could be lots of things....

I do not understand why a change to 365 would make matters worse, though!!

Is it full of volatile formulae, using:
OFFSET, INDIRECT, RAND, RANDBETWEEN

Are you using whole column references inappropriately, e.g. in:
SUMPRODUCT

Are there a lot of array formulae in there that could be done more efficiently with O365?

None of the above can explain a sudden change in prerformance, though...

[karstens]

I have uploaded an example file. I took out thousands of rows but wanted to show my formula to see how I can improve that.

[Glenn Kennedy]

There is nothing fundamentally wrong there. One suggestion. Use these two formulae OUTSIDE of the results table:

=XLOOKUP(Table1[CODE]&Table1[DESCRIPTION]&Table1[SERVICE TYPE],Table2[CODE]&Table2[DESCRIPTION]&Table2[SERVICE TYPE],Table2[Report],,0)

and

=XLOOKUP(Table1[CODE]&Table1[DESCRIPTION]&Table1[SERVICE TYPE],Table2[CODE]&Table2[DESCRIPTION]&Table2[SERVICE TYPE],Table2[Wireless],,0)

calculations are done in ONE cell each, instead of thousands. But Excel dyanmic arrays do not work in structured Tables.

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[karstens]

There is nothing fundamentally wrong there. One suggestion. Use these two formulae OUTSIDE of the results table:

=XLOOKUP(Table1[CODE]&Table1[DESCRIPTION]&Table1[SERVICE TYPE],Table2[CODE]&Table2[DESCRIPTION]&Table2[SERVICE TYPE],Table2[Report],,0)

and

=XLOOKUP(Table1[CODE]&Table1[DESCRIPTION]&Table1[SERVICE TYPE],Table2[CODE]&Table2[DESCRIPTION]&Table2[SERVICE TYPE],Table2[Wireless],,0)

calculations are done in ONE cell each, instead of thousands. But Excel dyanmic arrays do not work in structured Tables.

Thank you for the suggestion!
When I try the code you mentioned, I get the #SPILL! error. How do I avoid that?

[JeteMc]

I don't know enough about the 365 version of Excel to suggest why you should get a #SPILL error, however...
The formulas shown in columns Q:R on the Database sheet of the file in post #3 are array entered formula which may be slowing the workbook.
Try the following regular formula in Q2 copied over and down to R10:

Please Login or Register  to view this content.

Let us know if you have any questions.