• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delays in processing time using WORKDAY formula

dronka

dronka

Member
I have a spreadsheet that uses the WORKDAY formula, referencing a named range HOLIDAYS on a separate sheet. When I added that formula to my spreadsheet suddenly everything slowed down (the formula is in about 20 cells). There is a half-second delay after I do anything in the spreadsheet (which gets quite annoying). I decreased the HOLIDAYS named range as much as possible (it's only about 15 cells).

Is this processing delay normal with the WORKDAY formula? Is there a way to optimize?

Thanks!
 
Not typically, there are probably other cells that are slowing you down.
First, you could post the formula here so we can actually see what's included. Next, you could search this site for similar threads, or perhaps upload the workbook (if possible) and we'll take a crack and finding where the resource hogs are at.
 
I've uploaded the workbook. The WORKDAY formula is in column Q. I also have a whole load of conditional formatting formulas to get the bars and formatting in the GANTT chart to work. Normally columns M through Q would be hidden, but I made them visible for this sample. The primary function of those columns is to try to calculate the correct end date given weekends and holidays (and depending on whether the user selects a 5-day workweek or 7-day workweek in cell E1.

Thanks for being willing to take a look!
 

Attachments

  • Sample Workplan.xlsm
    256.6 KB · Views: 6

Attachments

  • Sample Workplan.xlsm
    252.4 KB · Views: 11
Thank you for taking the time to do this, Luke! I see that you got rid of the OFFSETS, the nested IF formulas, and the SUMPRODUCT. I'll study your updated formulas to learn how to use them (especially the INDEX).

I pulled up my original sample and compared the speed. I'm still getting that half-second pause after I make any change. I tried systematically deleting groups of formulas, and the pause persisted, even after there were hardly any formulas left in the spreadsheet. Then I went to the conditional formatting rules and deleted those. Boom, the speed was back.

Could it be that my conditional formulas are taking up so much processing time?
 
Could be. I removed the one volatile function I saw, the TODAY, and replaced it with a macro/static cell combo. If you could make continuous ranges that the CF rule(s) apply to, rather than multiple small bits (e.g., A:B,D:F,G:H) that would help a little bit.
 
You must log in or register to reply here.
Back
Top