Slow Calculation Time

[TSCworkhelp]

Hi everyone. I have a spreadsheet that functions as I hoped it would, but it is EXTREMELY slow because of all the calculations and conditional formatting. I don’t know how to fix it without removing anything, so I’m hoping someone here might have suggestions to speed it up (I tried the ones available in a basic Google search).

The spreadsheet already existed as a list of active patients (reduced for this sample, but appx. 200+ patients). My boss asked that I keep it as it is and make only the necessary changes for the calculations. The attached sample is a result of that request (columns H, L-Q are typically hidden).

Here is what he asked for:

• A column which calculates the 6-month date from when the patient was admitted
• If the patient has been here less than 6 months, leave it as is; If they're at or over 6 months, highlight it
• Of those that are over 6 months, some are “approved” if they are classified as “Group” or “Medication” - highlight those differently
• A summary table (that I put on a separate tab) to show how many patients a clinician has, how many are at/over 6 months, and the split of those approved and not approved

It seemed like a simple request, but when I put it all together it slowed down so significantly that I had to turn off Automatic Calculations (which is a pain).

I hope I explained this well. Any suggestions on how to simplify this?

[Richard Buttrey]

The basic problem is that you are processing whole columns with your SUMPRODUCT and Conditional formatting formula.

You should always limit your formulae to only the size of the data you're analysing. Where the data varies in size then create dynamic range names and use the range names in your formulae.

In addition SUMPRODUCT() is a bit old fashioned these days. It will speed up when you reduce the range to just 27 rows rather than the 1m+, but rather than that use the faster COUNTIFS() function.

So as an example

I've created dynamic range names for Clinician1 & Clinician2. For instance Clinician 1 is defined as

Please Login or Register  to view this content.

This evaluates to I2:I28 on the Active Sheet. This means that for instance the column C values on the Summary will be

I also created a name called 'sixmonths' which covers H2:H28

Please Login or Register  to view this content.

I've out this in I2:I10 so that you can compare. Once you've replaced all the current formulae based on the examples above the workbook will speed up.

See attached

[TSCworkhelp]

Sorry for the delay, I went on a vacation, but thank you so much! This worked!! The spreadsheet processes immediately now. Thanks for your help

[Richard Buttrey]

My pleasure and tnanks for the rep.