HELP! My caculation is running SLOW!

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thank you! I didn't scroll down far enough to see the attachment feature.

I have placed a sample file in my message.

Jim

Thanks for the response,

The [FIN] is the unique key for the "customer" visit, there could be multiple rows (events) for each visit. So...

For the current row go look for all the records that have the same [FIN] AND the [Base_Time] is LATER than the current row's [Base_Time]

Now count the number of records that have a [Base_Time] LESS THAN EQUAL TO the current row's [Target_Time1] if 1 or more, then "Excellent"

.... then it goes on to check if there are records in the next time range.....etc... if it does find a "Next EVENT/RECORD" then return "Poor"


Basically,

If I talked to a customer... then I need to go back and check on the customer within 60 minutes to be marked as Excellent"..... within 120 minutes for "Good"... greater than 120 minutes "Fair"... if I don't check on them at all then "Poor"

Does that make sense? My logic may be WAY OFF....lol

Thanks

Jim

A quick answer to a simple question will give me what I need to help you make this formula more efficient. In your data, does each record have a valid "Base Time"?

Answering this question will let me know how I should direct you. It appears that a lot of unnecessary calculations are happening in the middle of this formula.

There are also some efficiencies that could be applied to the "Required" field formula if you're interested.

Thanks for the response,

The [FIN] is the unique key for the "customer" visit, there could be multiple rows (events) for each visit. So...

For the current row go look for all the records that have the same [FIN] AND the [Base_Time] is LATER than the current row's [Base_Time]

Now count the number of records that have a [Base_Time] LESS THAN EQUAL TO the current row's [Target_Time1] if 1 or more, then "Excellent"

.... then it goes on to check if there are records in the next time range.....etc... if it does find a "Next EVENT/RECORD" then return "Poor"


Basically,

If I talked to a customer... then I need to go back and check on the customer within 60 minutes to be marked as Excellent"..... within 120 minutes for "Good"... greater than 120 minutes "Fair"... if I don't check on them at all then "Poor"

Does that make sense? My logic may be WAY OFF....lol

Thanks

Jim

Yes, the Base_Time field is mandatory, there will be "No" blanks

I would guess the MAX times the [Fin] could be in the dataset would be 500, but that would be rare, probably on average 150

Ok. Based on the information you gave me, here is my recommendation:

The idea behind this method is to considerably reduce the calculations that are happening within each instance of the formula in the "Rating" field.

Step 1: Use a 2-level sort to sort the table by "FIN" (any order) and then by "Base_Time" (oldest to newest). It appears you were already doing this.
Step 2: Insert the following formula in the first data row in the "Required" column. (This is just another small efficiency thing I noticed. It should have minimal time impact)

Formula:

Please Login or Register  to view this content.

Step 3: Insert the following formula in the first data row in the "Rating" column.

Formula:

Please Login or Register  to view this content.

Explanation... There are 2 basic changes to the formula in the "Rating" column:
1) I removed the unnecessary conditions from the COUNTIFS for meeting the "Good" and "Fair" ratings. Since "Excellent" is checked first, there is no need to have the criteria for "Base_Time" being greater than "Target_Time1" when identifying a rating of "Good". Similarly, you do not need to check if "Base_Time" is greater than "Target_Time2" when identifying "Fair" ratings.
2) I limited the number of records that are being checked in the COUNTIFS formula to 500. For a data set of 100,000 records, this means the COUNTIFS criterion in each cell of the "Rating" field will only run 500 times instead of 100,000 times. This should reduce calculation time significantly and allow the table updates to happen in a fraction of the time it was taking. If you need to adjust the maximum number of times the FIN could happen, you can adjust the formula accordingly. Alternately, if you want it to be more dynamic, you could have a column that counts the number of times a FIN occurs, and use an offset formula (that references this count) in lieu of the direct cell references I used here. If you are interested in that, I can change the formula to reflect this.

Thanks!

One last thing I forgot to mention. Do you really need "Target_Time3". Seems like a waste of space and an additional calculation that doesn't need to happen.

Brant,

THANKS!!! your solution reduced my calculation times to under a minute!

I used the advanced sort so I could validate my calculations, but didn't think to take advantage of it for calculations. Thanks again it really helped!

Jim

jrjeffcoat,

It just dawned on me that I complicated the solution, because I got a little too close to the problem. Please use this solution instead. In the first row of the "Rating" column, paste the following formula:

Formula:

Please Login or Register  to view this content.

Let me explain why. Since the data is sorted by "FIN" and then by "Base_Time", there is no need to look at the next 500 records and do some COUNTIFS. You simply need to look at the next record ONLY, because we know it will have a "Base_Time" that is closest to the record above it. This formula looks only at the record below (which is all that is needed) and determines the "Rating" based on that. This should further reduce the calculation time and will probably take the time down to less than a second. Sorry I got a little too much in the weeds and didn't provide this solution the first time around.

Thanks!