Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-21-2013, 08:02 AM
Swooshy Swooshy is offline Calculating turnaround times in excel Windows 7 64bit Calculating turnaround times in excel Office 2010 64bit
Novice
Calculating turnaround times in excel
 
Join Date: Nov 2013
Posts: 7
Swooshy is on a distinguished road
Default

Quote:
Originally Posted by BobBridges View Post
Swooshy, you may or may not thank me for this, but before I could understand what you were doing, I had to break it down into pieces and then simplify it. The attached workbook is not the answer to your question; I just added a sheet "Simple" which shows how "Sample" might look, doing the same thing yet being easier to follow. All your logic is in there, it's just that by adding some helping columns and using a few absolute references, the formulae are a lot shorter.
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!
Reply With Quote
  #2  
Old 11-21-2013, 01:04 PM
BobBridges's Avatar
BobBridges BobBridges is offline Calculating turnaround times in excel Windows 7 64bit Calculating turnaround times in excel Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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)
To break that down and make it easier to understand, I looked at it this way:
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)
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:
Code:
=IF(AND(G2="Booked",E2="Booking Status"),
  IF(Y2<=1,
    Y2-1+(D2-C2),
    Y2-1+((D2-C2)-ROUND(D2-C2,0)))
  ,0)
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:
Code:
=IF(AND(G2="Booked",E2="Booking Status"),
  IF(Y2<=1,
    Y2-1+X2,
    Y2-1+(X2-ROUND(X2,0))
  ,0)
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:
Code:
=IF(AND(G2="Booked",E2="Booking Status"),
  Y2-1+X2-IF(Y2>0,ROUND(X2,0),0)
  ,0)
(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
Code:
=IF($F2="Submitted Audit Control",$AA2,0)
$AA2 expands to
Code:
=AC2-1+AB2-IF(AC2<=1,0,ROUND(AB2,0))
, and AC2 and AB2 expand further ... you get the idea, I hope?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
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
Calculating turnaround times in excel calculating duration ketanco Project 5 12-15-2011 07:15 PM
Calculating turnaround times in excel Calculating dates 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:29 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft