![]() |
|
#1
|
|||
|
|||
![]() Quote:
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! |
#2
|
||||
|
||||
![]()
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
Code:
=IF(AND(G2="Booked",E2="Booking Status"),IF(NETWORKDAYS(C2,D2)<=1,NETWORKDAYS(C2,D2)-1+(D2-C2),NETWORKDAYS(C2,D2)-1+((D2-C2)-ROUND(D2-C2,0))),0) Code:
=IF(AND(G2="Booked",E2="Booking Status"), IF(NETWORKDAYS(C2,D2)<=1, NETWORKDAYS(C2,D2)-1+(D2-C2), NETWORKDAYS(C2,D2)-1+((D2-C2)-ROUND(D2-C2,0))) ,0) 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: Code:
=IF(AND(G2="Booked",E2="Booking Status"), IF(Y2<=1, Y2-1+(D2-C2), Y2-1+((D2-C2)-ROUND(D2-C2,0))) ,0) Code:
=IF(AND(G2="Booked",E2="Booking Status"), IF(Y2<=1, Y2-1+X2, Y2-1+(X2-ROUND(X2,0)) ,0) Code:
=IF(AND(G2="Booked",E2="Booking Status"), Y2-1+X2-IF(Y2>0,ROUND(X2,0),0) ,0) 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 Code:
=IF($F2="Submitted Audit Control",$AA2,0) Code:
=AC2-1+AB2-IF(AC2<=1,0,ROUND(AB2,0)) |
![]() |
Thread Tools | |
Display Modes | |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need a formula to calculate turnaround time | Rockitman31 | Excel | 9 | 09-07-2015 09:41 AM |
Calculating Profits? | MarkL | Project | 1 | 06-12-2012 07:19 AM |
![]() |
ketanco | Project | 5 | 12-15-2011 07:15 PM |
![]() |
Daria11 | Word VBA | 1 | 06-08-2011 06:54 PM |
calculating points and division in excel | edward masoya | Excel | 1 | 05-09-2011 06:31 AM |