Worksheet calculations very slow...how to improve and/or make efficient

Hi All,

I've been working on a little project regarding Tax payments etc.

It consists of 3 worksheets, which are fundamentally:
1) putting the values in,
2) a VAT / Tax calculator and,
3) a year on year summary sheet.

I am happy with the way the projects inputs values etc but if I include the summary sheet, the dreaded " calculating 2 processors" warning rears its ugly head and the time taken to do anything is a lot lot longer.

If I remove the year on year summary, then everything works OK, and so could someone please have a look at this and see where I can make improvements / use alternative methods to get the same results.

I attach the file in two forms....one with no summary and one with.

Hope you can help as this project is very very close to finishing!!

Regards,

Skyping

Do you really expect to have 50,000 rows of data in your Input sheet? In your Summary sheet you have a lot of array formulae ( SUM(IF(... etc.) which have a range up to 49997, which is bound to slow things down a bit. But, you have two SUMPRODUCT formulae (columns H and L) which only use ranges up to 4997. (I suspect you originally had 50000 and 5000 in your formulae and have deleted 3 rows in the Input sheet).

With array formulae every element/cell in the ranges is evaluated, so it makes sense to keep those ranges as small as possible while still encompassing your data. You should also make the ranges the same length, and for consistency you might as well use SUMPRODUCT for all the formulae, which will make them easier to maintain.

Hope this helps.

Pete

Hi Pete,

Thanks for the reply.

Think you are right regarding the number of evaluated rows regarding the array formulas. I will try to get these to be as small as possible and also use your other suggestions as well.

I don't know if its possible, but what I was trying to do was have all the formula in the summary sheet triggered ONLY when the drop down in that sheet changes (and not 'interfere' with other calculations in any other sheet.....if you or anyone else can point me in the right direction regarding that, it'll be fantastic....otherwise I will shorter the number of rows used but that would limit the usability of the file, as eventually I can see the file in the 10000's of rows range!!!

Regards,

Skyping

You could have a cell which contains yes or no (e.g. cell Z1), and then you could change your formulae along the lines of:

=IF($Z$1="no","",your_existing_formula)

As for the other problem, you could think about changing the formulae to SUMIFS and COUNTIFS, where you can use full-column references without a performance penalty. To do this you might have to introduce some helper columns within your main sheet.

Hope this helps.

Pete