Follow-Up: Speeding up calculation

[Rodney POWELL]

If it is satisfactory to recalc on demand, I'll suggest a VBA procedure to handle the work of your SUMPRODUCT function.

This way you can leave autocalc = True and Excel won't be trying to churn through this big spreadsheet.

If you are only adding sales data once a week -- you can probably eliminate your worksheet formulas

-- maybe edit the table as reqd then execute a subroutine that uses VBA for the calc and only returns the values to your table.

... It's a common problem -- Hope It Helps,

- Rodney POWELL
Microsoft MVP - Excel

Beyond Technology
Spring, Texas USA
www.BeyondTechnology.com



I have a large spreadsheet of sales data. 2 months of data is about 10k
lines. I need to build a summary page that shows sales for each region, by
item and month. On the data page I have columns for these fields. On my
summary page I have a matrix for each region, with items down the left and
months across the top. I then use sumproduct to populate the matrix, eg:

=SUMPRODUCT(--(Data!$A$2:$A$10593=1),--(Data!$D$2:$D$10593=$A44),--(Data!$F$2:$F$10593=$A$42),Data!$I$2:$I$10593)

Col A is month #
Col D is Item
Col F is Region
Col I is Sales

The problem I am having is that Excel takes at least 2-3 minutes to make
this calculation (and I only have one of 5 regions set up.) Each week I will
be adding sales info to the Data page because I have to also provide a
rolling 4 week summary, and it is very frustrating to sit and wait anytime
something changes. Is there a more efficient way (other than sumproduct with
the '--' syntax) to summarize the data? At this point, I have calculation
set to manual, but whenever I test my macros or formulas I have to get up and
take a walk.

I'm using Excel 2003 on Win XP (Home Edition)
I have 256MB of memory and a 1.7G Intel Celeron chip

 

[Rodney POWELL]

Can you maybe eliminate the SUMPRODUCT altogether ?

... having a VBA proc to recalc that part only on demand (like click a 'Re-Calc Now' button)

-- then you can leave autocalc on but it won't be getting bogged down with that big function.

- Rodney POWELL



Thanks Rodney. I don't mind waiting for it to calculate when I add the data
once a week. I actually have a macro that takes care of adding the data to
the existing spreadsheet, so I have to wait a few minutes anyway. The main
problem is I'm still in the development stage, so I'm still testing various
things out, and these calculations are slowing me down quite a bit. I was
wondering if there was a more effiecient way to do this type of calculation.
I guess my best bet is to do all my testing with a minimal data set instead
of a full month or two of data.

Thanks again.