Speed up workbook with many SUMPRODUCT formulas

[TinaP]

Hello All!

I have a massive workbook with thousands of records. For each record, I need to include three weighted averages. I am using SUMPRODUCT to calculate the weighted average. Every time a change is made to the workbook, it takes about a minute to calculate, which gets old very quickly. Is there anything I can do to speed things up?

I am using dynamic named ranges using the INDEX function instead of OFFSET to help speed things along and I've tried to eliminate all other volatile functions.

 

[Joe4]

I have a massive workbook with thousands of records.

I would hardly call thousands of records massive. Now, hundreds of thousands of records, that is getting closer to be massive.

If your workbook really is massive, note that Excel can only track so many dependency calculations (I don't know what number is off the top of my head, maybe a million or so?).
Once you exceed that number, Excel cannot figure out which calculations are affected by your data update, so it updates ALL of them, which as you might imagine, is pretty slow. And it does this with every update.
It sounds like that may be what is going on. The only thing I can think to do in this case is to shut off Automatic Calculations, and only run the Calculation step manually by hitting F9 when you want.

Note: If you have hit this dependency limit, that is usually a HUGE RED FLAG that you are probably using the wrong tool for the job (which is why it is so inefficient).
If you have that much data and that many dependencies, it usually describes a database model, and you would be far better off using a database program (Access, Oracle, SQL, MySQL, etc).

 

[offthelip]

Have you tried using a solution that doens't use sumproduct, i.e use a spare column to calculate the product for each line separately and then use a ordinary average function. I would expect this to be faster because EXCEL wouldn't need to recalcualte the whole array everytime. Just those lines that have chaged.

 

[Oaktree]

The DSUM and DCOUNT functions often require only a fraction of the recalculation time of SUMPRODUCT.

The problem with those database functions is that they would seemingly require you to insert additional rows so you can use the required header/criteria structure for every calculation....

...BUT, you can get around that by using the data table feature combined with DSUM (or DCOUNT) as explained by Aladin in this post: https://www.mrexcel.com/forum/excel-questions/58539-horizontal-criteria-null-value.html#post272495

Maybe give that a try?

 

[TinaP]

Thanks for the help from ALL of you.

I took advice from everyone who responded and here is what I came up with:
Joe 4: You are correct a database solution would be better, but that is not available right now. I believe I may have hit some sort of limit.
offthelip: I reviewed my formulas and found that I could simplify most of the formulas by replacing a SUMPRODUCT with a SUMIF. I'm not sure why I didn't use SUMIF in the first place, but there you go.
Oaktree: The solution using a data table feature is intriguing, but I'm under a time crunch. I will investigate after I get the short-term answers that I need.

I have simplified the formulas (as noted above) and the calculation time has dropped significantly. I want to research Oaktree's suggestion. Thank you all.