Seeking Code/VBA for Average WaitTime Per Hour

Brass Contributor

Seeking assistance creating a text box that has the output of - Average 'Wait Time' in in a given hour.

There are blanks in the 'OutTime' column that need to be accounted for.

Is this possible using the sample test data provided, and how?

24 Replies



Is it possible for you to attach not an image but the actual file? You did it earlier in this thread, with sample data..... Just make sure there is nothing proprietary or confidential in the file

NOT the Test Data file; the ACTUAL file. The one you've been posting images of. That one!

Its too large. The system won't let me, it exceeds the allowed upload size.



OK, I did it with the test data file. 

The key is that the criteria in the FILTER portion of the formula have to refer to the column with the dates and the row with the numbers of the hour. 


This is the formula that gets average wait times:

=IFERROR(AVERAGE(FILTER(Sheet1!$E$2:$E$243,(HOUR(Sheet1!$C$2:$C$243)='Summary Data'!B$3)*(Sheet1!$B$2:$B$243='Summary Data'!$A4))),"")


The heart of that is this FILTER portion, so here's a bit of explanation of it. Again, you should refer to those sources I gave you before. Once you understand it, it's a very useful function. You also should look up some references, I suspect, to "Absolute References" and "Relative References"

FILTER(Sheet1!$E$2:$E$243,                    this specifies the range to be filtered

(HOUR(Sheet1!$C$2:$C$243)='Summary Data'!B$3).  

first criterion, where the hour = number in row 3. Referring to it as B$3 is a case of the B being a relative reference, $3 is absolute  so as you copy it to other rows and columns the B can adjust to C, D, E, etc. but the $3 stays constant,
*                                              "*" connects both criteria as AND

(Sheet1!$B$2:$B$243='Summary Data'!$A4))              

second criterion gets dates equal to date in column A, and as this gets copied, the A stays constant but the row number changes.