Spreadsheet calculates too slowly

[wflabqueen]

I have a mid-sized workbook ~14MB that takes far too long to calculate. One sheet contains all a data table with ~32,000 lines and 44 columns. 16 columns have formula and the rest are just text or number, four of which use INDIRECT which I know is a volatile function. One sheet contains all my pivot tables (which are not all built yet) and one sheet is the dashboard (not completed either). I have turned off automatic calculation and set the calculation to use all processors to speed things up which has helped. But I am afraid that when all the pivot tables are built this will calculate even slower. I have VBA code that copies data from another source to populate the data table and shows as linked data in Info.

What I would like to happen is:

A) Add VBA code to recalculate the formulas in the data table and refresh the pivot tables once certain cells are selected and changed.

B) figure out any other way to speed this workbook up.

I have read many posts here to get answers to other questions and can do some VBA code but this is my first post. Any help is welcome.

 

[ghrain22]

Are any of the formulas look ups or using entire rows/columns to calculate? ie. A:A or 1:1

 

[Ragnar1211]

Replace all volatile functions and your speed will increase significantly. Post the functions you are using if you want further assistance.

 

[wflabqueen]

Yes, unfortunately. Things were working well until I wanted a way to identify unique values so when I added =1/countifs(range1,criteria1, range2,criteria2) it began to churn slow. The ranges are entire columns.

 

[ghrain22]

the countifs using the entire column will slow you down. Limit that to the last row your data is in. If the length of your data is ever-changing consider using a dynamic named range.

 

[Ragnar1211]

If the ranges are entire columns then using a combination of Index and match is far superior than using Indirect.

Post a sample of your indirect formula if you like.

 

[wflabqueen]

Column AC =IF([@[Ship To]]="","",SUM(INDIRECT($AC$9&ROW(AB15)&":"&$AC$4&ROW(AB15))))
Column AD =SUM(INDIRECT($AC$11&ROW(AB15)&":"&$AC$10&ROW(AB15)))
Column AE =IF([@[Ship To]]="","",IF(AND([@[Recent Sales]]=0,[@[Previous Sales]]>0),"Lost",IF(AND([@[Recent Sales]]=0,[@[Previous Sales]]=0),"NoSales","")))
Column AF =IF([@[Lost Value]]="","",1/(COUNTIFS([Ship To],[@[Ship To]],[Lost Value],[@[Lost Value]])))

The next set of columns repeats these formulas but for different fields.

Column AQ =INDEX(Customerdata[[#Headers],[January 2013]:[Dec-14]],1,MATCH(99990000000,Customerdata[@[January 2013]:[Dec-14]],1)) - to find the last month of data in the row
Column AR = =1/(COUNTIF([Ship To],[@[Ship To]]))

I need to keep the indirect as I am asking the user to enter the number of months that they want to look at - it takes this value and translates it to the correct column.

 

[Ragnar1211]

What values do you expect in AC9 and AC4?

 

[wflabqueen]

AC9 returns a column letter. So the user enters the number of months they want to look at, 1-9, then it takes this number and subtracts it from the current months column and then the CHAR formula gives me a letter. As an example if the current month is September - which is column Y in my table - and they want to look at the previous 3 months - the sum of W,X and Y then AC9 would return W

AC4 is the current month column which would be Y

I hope I am being clear - I read posts that make my head hurt sometimes.

 

[steve the fish]

This formula is non volatile and will sum data in rows 1:10 matching the header in row 1. Maybe you could work with this?

=SUM(INDEX(1:10,,MATCH("header",1:1,0)))