Excel file runs slowly

**john_london** · 09-24-2010, 02:32 PM

Hi Again

I'm working on a spreadsheet which includes some array formulas, with no macros, in about 5x200 cells. The file is only about 120lk. However, when I try to filter the data, or save the worksheet, it runs incredibly slowly for about 5 minutes, and I can't do other tasks. I managed to get task manager open and the CPU was at 100% usage. Surely Excel should be able to handle this?

I've attached the file. It's still a work in progress, but just wondering if anyone had any ideas for fixing this problem?

Thanks

**MarvinP** · 09-24-2010, 04:10 PM

Hi John_london,

Your statement of "some array formulas" is understated. You have 1122 array formulas on your first sheet. Rumor is that they are calculation hogs. As a matter of fact, I've never seen my i7 quad core CPU gauge pegged to the top with all 8 processors before. It took about a minute for me to load your work.

I'd send the workbook back to the developer and ask for a faster one. You might also ask for a faster computer if they expect you to use it. If you are the developer, my apologies

You have only one bad ref name in your workbook. Look at the Names Manager and try to delete the #Ref name.

Does any of this information help? I expect the reason for it being slow is the array formulas. Perhaps there is a workaround.

**john_london** · 09-27-2010, 09:22 AM

Haha true, and thanks for the reply. Unfortunately There's no way in a million years I'm getting a faster computer, and I made this myself to try and save myself some time at work.

I guess I was just wondering if there was a simpler way to do this? All I'm doing is counting the number of profiles, by risk, per person, from the roles sheet. I don't mind adding another column or two? Surely there must be an easier way? Those array formulas are SO slow!

Thanks
John

**romperstomper** · 09-27-2010, 09:30 AM

Since you are using 2007, it would be faster if you use COUNTIFS instead:
=COUNTIFS('Roles Report'!$A:$A,$A32,'Roles Report'!$E:$E,"High")
for example. A pivot table would probably be better still.

**john_london** · 09-27-2010, 10:51 AM

Thanks for the quick reply - that's great! Loads faster.
I would like to use a Pivot but can't really see how it will work. Thanks for your advice though and I will look into it!

Again really appreciate the help!

John