Excel Worksheet calculating formulas slowly

Question

I have a worksheet (not very large currently a little over 6000 rows K columns) that is for some reason taking an extremely long time to recalculate and frequently reacalculate and save. Sometimes it takes 10-15 minutes, and others it can take up to half a day, there isn't much consistency to the calc time. The formulae are not very complex, and its fairly clean from what I can tell. The references for most of the formulas are data on a "source" sheet on within the same worksheet which is pulled from SQL and exported into excel using Cognos Impromptu. I have another sheet that doesn't have this slow calc issue, but it's data is exported directly from the system containing the data. The formulas are the same, and the data is basically the same in structure, but coming from different places. That's the only difference, but one is slow.

It isn't pulling data from SQL in real time, I am retrieving data from SQL (not using Excel) and literally pasting values into excel for calculations there. By the system I mean the program in which all of the information is manually entered (not SQL which updates from the system automatically on a nightly basis) i.e. account demographics, charges, payments, appointments, etc. The data that I am pulling into excel is Name of Provider, Type of Provider, Responsible Payor, Charges, Payments, Adjustments, and Date. It is pulled and updated on a daily basis, which is part of the problem, each time that I add a new day's data it has to reprocess to include the new rows.

Here are some of the formulas

=IF(ISERROR(SUM(IF((Source!$A$5:$A$150000='Provider Weekly-WTD'!$A8)*((Source!$K$5:$K$150000>='Provider Weekly-WTD'!B$6)*(Source!$K$5:$K$150000<('Provider Weekly-WTD'!B$6)+7)),Source!$E$5:$E$150000,""))),"-",SUM(IF((Source!$A$5:$A$150000='Provider Weekly-WTD'!$A8)*((Source!$K$5:$K$150000>='Provider Weekly-WTD'!B$6)*(Source!$K$5:$K$150000<('Provider Weekly-WTD'!B$6)+7)),Source!$E$5:$E$150000,"")))

,

=IF(ISERROR(SUM(IF(($A8=Source!$A$5:$A$150000)*(IF(Source!$K$5:$K$150000=(B$6+6),(B$6+6),MAX(IF((Source!$A$5:$A$150000=$A8)*(Source!$K$5:$K$150000>=B$6)*(Source!$K$5:$K$150000<(B$6+7)),Source!$K$5:$K$150000)))=Source!$K$5:$K$150000),Source!$I$5:$I$150000))),"-",SUM(IF(($A8=Source!$A$5:$A$150000)*(IF(Source!$K$5:$K$150000=(B$6+6),(B$6+6),MAX(IF((Source!$A$5:$A$150000=$A8)*(Source!$K$5:$K$150000>=B$6)*(Source!$K$5:$K$150000<(B$6+7)),Source!$K$5:$K$150000)))=Source!$K$5:$K$150000),Source!$I$5:$I$150000)))

,

=SUMIFS($I$5:$I$150000,$A$5:$A$150000,$A7111,$C$5:$C$150000,$C7111,$K$5:$K$150000,(MAX(IF(($A7111=$A$5:$A$150000)*($C7111=$C$5:$C$150000)*($K7111>$K$5:$K$150000),$K$5:$K$150000))))

, and

=$D7111+$E7111-$F7111+$G7111-$H7111

Since I am somewhat limited as to displaying the actual data here, and can only display the formulas I think that I am asking if the formulas I am using are ok, and/or are causing the issue. If it's not the formulas does it sound possible that the data, or the format that the data is in is causing the issue. For example I had someone tell me that because the date format (in the format settings) has the * in front of it, that it's causing the problem.

Answer 1

It won't be the formulas given the size of your rows and the calc time - unless the source sheet is actually linked to other sources, ie is your calculation trying to link to another workbook with your SQL output?

Two suggestions to try to clean your file by cleaning up ayd redundant space and possible corruption

1. Download and install the free ASAP Utilities addin. The select all sheets, run the 'Sheets' utility from ASAP and then select '25 Remove Unsed empty ending Rows/Columns'
2. Then select all sheets, right click the sheet tab, pick 'Move or Copy...' and pick 'New Book'

If you could find somewhere to upload your troubled workbook (sanitised if neccessary) then that would also help

Answer 2

Your formulas are referring to a much larger range than you're using:

Source!$A$5:$A$150000

Then, your formula is comparing every cell in that range with a single reference cell:

Source!$A$5:$A$150000 = 'Provider Weekly-WTD'!$A8

Reduce the span of cells referenced by your formulas and you should find a speed improvement.