Hi BobBridges

Your example seems to tie out with what is required alright but unfortunately my excel is not strong enough to understand what is going on there.



would you mind updating your example with some comments so that I can follow what is going on there? The thing is there is in excess of 10K rows in the report that I am using with numerous opportunity numbers so the formula would need to flexible to allow the drag down functionality to work across those. this may be a given but i just need to make sure!

Your formulae in columns G and N-U were pretty complex, so to see what was going on I broke them down into pieces, then rebuilt them using helping columns at the right. For instance, the formula in Sample!H2 is To break that down and make it easier to understand, I looked at it this way:Right away I see several things I can do to make this more comprehensible:

1) This one formula is calculating NETWORKDAYS(C2,D2) three times. For a few records that may not matter, but it's doing that not just in column G but in 8 other columns as well, and you say there are >10K rows. That's making Excel do a lot of repetition. So let's let each row make the calculation just once. I put =NETWORKDAYS($C2,$D2) in column Y, and let H2 refer to that instead:2) Likewise it's calculating D2-C2 three times. Mere subtractions aren't as time-consuming as calls to a function such as NETWORKDAYS; still, let's put =$D2-$C2 in column X, simplifying H2 further: 3) One more thing: The two formulae Y2-1+X2 and Y2-1+(X2-ROUND(X2,0)) are similar; it's only that last part that varies. You want H2 to start out as Y2-1+X2 all the time, but then you want to subtract ROUND(X2,0) only if Y2>0. Well, you can express that by moving the inner IF out to that part of the equation: (I've a notion that even Y2-1+X2-ROUND(X2,0) can be simplified somehow, but truthfully I got lazy and didn't look closely at it.)

So I put "=Y2-1+X2-IF(Y2<=1,0,ROUND(X2,0))" in column W and made H2 be simply "=IF(AND($E2="Booking Status",$G2="Booked"),$W2,0)". I did the same for the rest of column H, and all of column N.

Now, columns O-U were like this in basic form but they replaced every reference to C2 with a VLOOKUP on C2. VLOOKUPs are a bit more expensive, in terms of time required to execute, and for >10K rows you may even have noticed your worksheet having to think a bit when you made changes. That's six identical VLOOKUPs in each of seven cells on the row. I combined those 42 VLOOKUPs into one ("=VLOOKUP($M6,$J:$K,2,FALSE) in column AD. Then I made three helping columns AA through AC that are identical to those in columns W through Y. That means that the VLOOKUP could be executed just once for each row (instead of 42 times), and the same for NETWORKDAYS (instead of 6 times), and so on. So each of those long formulae in columns O through U are reduced to something like$AA2 expands to, and AC2 and AB2 expand further ... you get the idea, I hope?