Why is my spreadsheet slow! - Arrays?,Named Ranges?,Indirect refs??

**ACN** · 06-02-2010, 05:30 AM

Hello Everyone,

This may have been answered elsewhere before but I had a quick search and couldnt find anything that gave a concise answer.

Before anyone says the obvious, no. It's not my computer I have sufficent RAM, CPU etc.
I don't get this problem with other simmilarly complicated spreadsheets as well (although they use simpler formula), so hardware is not an issue.

A little background:

The spreadsheet I have created bassically has sheets with cost (actual vs targets) against dates as well as some other information that isnt used in calcs. Theres a sheet for each different 'thing' that has costs and targets associated with it, I have to use a sheet per 'thing' as I've been asked to do it that way.

The problem I've got is that I try to collate all the information from these sheets into one 'collection' sheet with all actuals,targets and dates.

The formulas all work, however I'm using SumProduct (Which unless I'm mistaken is a type of array formula) and it appears to slow things down, I also use named ranges specific to each of the sheets to refer to data on them.

The question:

Firstly am I right in thinking that Sumproduct/Named ranges (also Indirect refs as well) are the culprits?

If they are not to blame then any other ideas why its slow would be welcome.

If they are to blame....

i) Ideally I would prefer Anything I can do that would involve minimal rework of the spready

ii) If i) not effective - has anyone got a suggestion as to what I can use instead? I'm using SumProduct because the way the data is laid out doesn't work with a sumif (i.e. dates along a top row, items vertically and their respective values for targets and actuals under each date.

Realise I could probably explain this better, but even if the context isn't clear, should still be possible to answer the questions 'in theory'.

Appreciate any help on this!

Cheers,

ACN

**Marcol** · 06-02-2010, 05:39 AM

You would do best to post a sample workbook showing Before and After.

It should clearly illustrate your problem and not contain any sensitive data.

Cheers

**DonkeyOte** · 06-02-2010, 06:24 AM

Originally Posted by ACN

Firstly am I right in thinking that Sumproduct/Named ranges (also Indirect refs as well) are the culprits?

SUMPRODUCT used en masse or against large ranges will be slow.
If used in Volatile construct the slow down will be exacerbated.
If the Names are Volatile (ie use OFFSET) then the functions using those Names are themselves Volatile by association.

Originally Posted by ACN

has anyone got a suggestion as to what I can use instead? I'm using SumProduct because the way the data is laid out doesn't work with a sumif (i.e. dates along a top row, items vertically and their respective values for targets and actuals under each date.

The best alternative (pre XL2007) is to use concatenated fields where feasible - this will allow you to revert to SUMIF which is significantly more efficient.

This concept can be seen in action: http://www.youtube.com/watch?v=ry3vSqN1TQ0
(demo c/o Mike Girvin)

In XL2007 and beyond you also have SUMIFS function though note this is not backwards compatible with earlier versions.

**DonkeyOte** · 06-02-2010, 06:37 AM

Originally Posted by ACN

has anyone got a suggestion as to what I can use instead? I'm using SumProduct because the way the data is laid out doesn't work with a sumif (i.e. dates along a top row, items vertically and their respective values for targets and actuals under each date.

On an aside - based on your suggested layout are you sure you can't use SUMIF ?
Are the "dates" in the header days or months - ie do they match your reporting periods ?
YTD calcs would be awkward but for a single month you should find a standard calc will suffice.
Are the items on the vertical axis unique or can they be duplicated in any given listing ?

If you can - post one example SUMPRODUCT - from that we should be able to "visualise" the setup a little better without needing to see the file.
(detail the named ranges as nec.)

**ACN** · 06-03-2010, 12:49 PM

Hi Chaps,

Thanks for the feedback- I had to sort this out in a hurry so would have taken too long to strip out all the confidential data. Thanks anyway though.

Correct me if I am wrong I don't think you can use a Sumif, where you have two ranges that are different lengths rows/columns. Also Sumif won't allow you to apply complicated logical operaters that you can with sumproduct.... but correct me if Im wrong.

For the record in case anyone finds this post whilst trying to answer a simmilar question- I'd suggest making your formulas as simple as possible, and use an alternative to arrays where possible, on a smale scale they are fine but if these feed many values then it really slows things down. In my oppinion anyway... I'm not an expert.