Speed up workbook with many SUMPRODUCT formulas

TinaP

Well-known Member
Joined
Jan 26, 2005
Messages
523
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,754
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,032
Office Version
2010
Platform
Windows
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,941
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

Well-known Member
Joined
Jan 26, 2005
Messages
523
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.
 

Forum statistics

Threads
1,085,010
Messages
5,381,212
Members
401,719
Latest member
hadleycamp

Some videos you may like

This Week's Hot Topics

Top