Spreadsheet calculates too slowly

wflabqueen

New Member
Joined
Sep 26, 2014
Messages
5
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Replace all volatile functions and your speed will increase significantly. Post the functions you are using if you want further assistance.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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)))
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top