Monday, May 6, 2013

yogi_Count Instances Of 5+ Minutes Wait Time By The Hour Starting From 0 Hour


                                          Google Spreadsheet   Post  #1172
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 06, 2013
user Dan Wysocki  (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Ko3ReBi4lYs)
Need help with a formula...

Hi all, I've been having a hard time coming up with a formula to do what I need and I'm hoping someone can help.  Here's an example sheet:

https://docs.google.com/spreadsheet/ccc?key=0AtYlBcwVOmyHdHRkemVXR1JTWTAxbDNZX0kxajRGQkE#gid=0

Basically, I need a breakdown by hour of how many 5+ minute wait times on tickets we're seeing.  I have a column of wait times (Column 'I') and a column of timestamps (Column 'B').  The closest I've been able to get is this formula, but it is counting up ALL the 5+ minute waits and then adding the difference of hourly numbers on top of it:

=COUNTIF($I5:$I385,">="&L19)+COUNTIF($B5:$B385,">="&A1)-COUNTIF($B5:$B385,">="&B1)

What needs to happen is to find all messages (in column 'b') that came in within an hour period, then count how many of those had 5+ minute waits (column I).  Is this possible?  Any help would be greatly appreciated.
------------------------------------------------------------------------------------------------------------------
following is a solution to the problem


No comments:

Post a Comment