Excel - Iterations/Calculations Speed Extremely Slow - Please help

Hi All,

I have attached a small spreadsheet of the larger one which we use. The larger spreadsheet has around 100k rows and is constantly increasing, this one uploaded is very small and only has 90 rows. However, all the columns exist in the smaller sheet too. Please refer to the below link for the sheet as it is too large to upload here:

https://www.dropbox.com/s/pzov3kidwd...heet.xlsx?dl=0

Moreover, for confidential reasons, I have had to change the name of headings and data as this is for my work place. Although, all the formula etc. remains exactly how it is on the larger complete sheet. Unfortunately, when we run calculations and iterations, it takes hours and hours for it to complete. I appreciate there is a lot of formula, is there something I am missing, or is there a function I have switched on/off that could resolve this issue I am having.

The Max iterations we have got it set to is 100, and the maximum change is 0.001 - Does this all need changing.

I know the spreadsheet is fairly large and may take a while to go through, but I would be most grateful if anyone can help.

Furthermore, even opening, saving and closing takes an awful long time. We are talking 5 ish minutes for this. But it would be great if there was a fix to this too.

I look forward to help and advice. Thanks in advance.

Originally Posted by Norie

Can you attach the file to a post here rather than DropBox?

Unfortunately, I cannot because this file is 21mb and the limit for upload is 10mb. Thanks.

Originally Posted by MrShorty

For those who don't like to download from another hosting service, it seems like it should be possible to illustrate your spreadsheet with smaller lookup tables in "postage", since that seems to be where a lot of the data storage is.

A couple of things I see:

1) There appear to be a lot of lookups within the "circular reference" section. Each of these lookups is using a less efficient "linear" search algorithm, because you are forcing an exact match (4th argument of VLOOKUP() function is FALSE). Binary search (4th argument is TRUE and lookup table is sorted in ascending order) is much faster than a linear search. You might look at your lookup table and your lookups and see if you can arrange them to use the much more efficient binary search algorithm.

2) For a given row, it seems that all of the lookup functions are essentially looking for the same thing -- where is the value in column P in the lookup table. You might consider a strategy I suggested here (http://www.excelforum.com/showthread...=1#post4041181 ) where you perform the lookup once per row with a MATCH() function, then use several INDEX() functions to extract the desired information.

3) I have not thoroughly explored the iterative calculation. Clearly if you can rearrange the calculation to not require a circular logic, you can reduce the calculation time by reducing the number of calculations per calculate event. Iteration can dramatically increase the number of calculations, so eliminating the circular reference can yield significant savings.

That should be a start.

Thanks for your input.