D-sum and Grouping slow down problem

B

Ben

Hi everyone,

I was wondering if anyone can tell be a better way to do this. I am working
with financial information in Excel. I have roughly 50 locations and 5 years
history. The locations run done column A with the year in column B. I grouped
them so I only can see 2007 information, but and ungroup any location and see
the prior 5 years data.

What I'm finding is that when using the D-sum function to sum these
locations by year or region, that we I "expand" all of the groupings or close
them, Excel freezes up and it takes about 7 minutes for my program to respond.

I'm only working with about 3000 rows. Any suggestions, or does the D-sum
formula just bog down Excel?
 
J

John

Hi Ben,

Sounds to me like its taking time to crunch. Couple of things to look at
1 Is Calc set to Automatic. If it is set it to Manual, then use <SHIFT> F9
to recalc the tab the data is in.
2 Have you looked at Array formulae. This may prove to be a quicker method

John
 
P

pshepard

Hi Ben,

Do formulas reference just the rows that have data, or include blank rows as
well? If you were referencing all rows in the worksheet (through 65,000+) -
even if they are mostly empty - that can slow down the processing time.
 
Top