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.
