Forum Discussion
Seeking Code/VBA for Average WaitTime Per Hour
A spreadsheet is attached that computes the average wait time for each hour of a day you select. This relies entirely on functions that are only available in newer versions of Excel, so if it doesn't work for you that would be the reason. Blank fields are not included in the calculation of averages, so if that's what you meant by "blanks in the 'OutTime' column that need to be accounted for" then there's no reason to worry. They are not reducing the averages.
It looks like this:
mathetes
Not quite done, sorry.
Can I get the average wait time for the day on the line with the wait times?
My apologies; and thanks for your patience. I just realized there was a major omission in the formulas I've given you. They were not getting average waits for the specific days, only for the hours shown. That was apparent -- if you still have the older copies -- in that the figures for hour 15 (the only hour that appeared on two different days) showed exactly the same numbers regardless of which day.
So I added a criterion to the formulas for average and total tix that now FILTERs only the averages and totals for the day specified and the hour specified.
Getting the overall average for the day then is a simple matter of removing the criterion that links to hour; same for the total count of tix
I do hope that you'll take the time to view those resources I sent you before--they should help you catch errors like the one I've fixed here. It's important for you to understand how these formulas are working; that's why I sent those links.
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.
- Its too large. The system won't let me, it exceeds the allowed upload size.
NOT the Test Data file; the ACTUAL file. The one you've been posting images of. That one!
Phishdawg
There are times when I have trouble conceptualizing some things.
I've reviewed the materials, taking from it what I could. Thank you.
I've built it out to this, though I know there has got to be an easier way; And I can't even tell if what I have is correct or accurate. The times look too similar, though it could just be happenstance, but how would I know?
Any comment you can provide is appreciated.- Sir, I appreciate the support and patience.
I will review the materials you recommend and attempt to resolve these issues on my own, as you suggest.
Thank you, How do I carry the 'Daily/Hourly Average Wait Time' down the sheet?
When I drag it just replicates the number above.Several messages back, I wrote this paragraph:
I would encourage you to avail yourself of the ExcelJet website to learn more about each of the functions in this spreadsheet, in particular FILTER, UNIQUE and SORT. There's also a great YouTube video that Microsoft used to introduce those functions to the world a couple of years ago.
Have you looked at either of those resources? They would help you answer your own question. You need to change the references in the formula so they refer to the dates and hours you want them to refer to.
I'd be happy to give you the answer using your data, but you've never sent me that actual database that has all those dates in it and hours in it.
