5.6 MB file, users having difficulties

[beetlebug]

I have a large model over 5MB that appears to have trouble upon saving. It gets hung up at around 40% calculation and is very slow when I do try to Save. The file itself has a few macros in it and the INDEX/MATCH formulas through it's 10+ worsheets. Is going to be a problem with the efficiency of the file no matter what I do?

This tool has to get out to several people to use and it works great! if only they could get it to run quickly (or quicker).

Any thoughts? Would it help to turn off calculation and then let it save (I assume it automatically starts calculating when you hit save as well?)

Thanks,
Rhonda

 

[beetlebug]

If you use the same MATCH multiple times to retrieve a few columns for a given match row, then yes because you only have to do the row lookup once.

Well, I adjusted all my worksheets and formulas to eliminate that INDEX/MATCH multiple times, separating it as suggested and it is still taking forever to save the file I'm so bummed! The spreadsheet is a great tool for my users, but I suppose it is so complex that it really belongs in an application (not Excel!).

Bummed... but thanks for your help.

 

[RoryA]

There might be other formulas that can be simplified too, but we'd need to see the workbook I think. If you are interested, send me a PM and I'll give you an email address and you can send me the file to take a look at. If not, no problem - I know it may be proprietary!

 

[Caliche]

What happens if you convert your formulas to values?.

You could have a macro that copies all formulas to corresponding cells and convert them to values.

HTH

Caliche

 

[erik.van.geit]

Hi,

This looks like a corrupt workbook...
Consider to REBUILD your workbook.
There was a lot of positive response about that thread.

kind regards,
Erik

 

[spcalan]

I have a file that is around 4mb, and it crashes 1 out of 4 times.

I have 8 sheets with each sheet having 75 sumproduct formulas ( with 4 variables ) each.

I assume that is the reason for the slowness.

Any help with my difficulties?

Is the sumproduct the most "efficient" way to get the data I need out?

 

[RoryA]

Probably not. If you can build a key column using the data from your 4 variable columns and use a SUMIF on that, it should be more efficient.

 

[Domski]

Any help with my difficulties?

Is the sumproduct the most "efficient" way to get the data I need out?

Have you looked at using a pivot table instead?

I have recetly built a spreadsheet app with about twice the number of SUMPRODUCT formula and a large data set and despite the fact that it takes about 45 secs to calculate I never have any problems with it crashing.

Dom

 

[spcalan]

Have you looked at using a pivot table instead?

I have recetly built a spreadsheet app with about twice the number of SUMPRODUCT formula and a large data set and despite the fact that it takes about 45 secs to calculate I never have any problems with it crashing.

Dom

Can I send it to you and maybe you can decide what the best way is?

 

[Domski]

Sorry bit busy, I'm only at work for another couple of hours and then off on my hols.

Dom

 

[beetlebug]

Ok.. after 2 days of pounding this spreadsheet to death, I think I have come up with the best solution. I took the advice and got rid of my INDEX/MATCH process altogether. I created separate sheets that link directly to my downloaded data (input), and concatanated the items. Then I used SUMIF in my main analysis worksheets to pull the data in (in lieu of INDEX/MATCH). It is definitely much faster now.

I guess those array formulas really bog things down.. Surprisingly though, my file is actually much larger in size than it was before (I guess because of the additional sheets I added). I went from about 5 MB to over 10MB but my average save time is 6-12 seconds, vs... 40 or so seconds (or Excel crash!)

Thanks folks!