Seeking Code/VBA for Average WaitTime Per Hour

[Phishdawg]

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?

[#2 · Accepted solution]

@Phishdawg 

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:

[#3]

The column with the '13, 14, 15' represents the hour of the day, such as 13 is 13:00 (1:00 pm), 14:00 (2:00 pm), etc., yes?

If a specific hour doesn't appear this means no 'Ticket' was issued during the hourly period, yes?

[#4]

It was a limited database, but yes, that's what it would mean.

[#5]

What would the formula look like if I want to represent the sum of tickets issued per hour for each of the hours represented?

[#6]

@Phishdawg 

What would the formula look like if I want to represent the sum of tickets issued per hour for each of the hours represented?

Try this

=IFERROR(COUNT(FILTER(Sheet1!$B$2:$B$243,HOUR(Sheet1!$C$2:$C$243)='Summary Data'!B7)),"")

[#7]

@mathetes 

Is there a way to make the output 'vertical'. Seems unnecessarily complicated but it's what is being requested, if possible. In my ignorance it looks like a different formula would be required in each field.

Date	6	7	8	9	10	11
6/3/2019	0:21:17	0:24:46	0:22:29	0:26:11	0:30:47	0:29:52
No. Trips	5	73	62	77	85	77

[#8]

@Phishdawg 

The same formula, but with a different reference, now to the row across the top rather than the column down the left. I've left the originals in the attached so you can compare them and see the single change that had to be made.

The hours formula, using the dynamic array function UNIQUE does require the addition of the TRANSPOSE function so that it arrays itself horizontally rather than vertically.

As a separate postscript, I should add that you can achieve the same visual result by using Copy....Paste Special.... and then select the "Transpose" option. Play around with that. For a single, one time report, that might be all you need.

[#9]

@mathetes 
Can't figure out what I'm missing.

In my DailyTransactionSht Column A is 'TicketDate' and Column D is 'InTime'.

Two of the three outputs are working as desired.

Column H7 should be '7'. I can't figure out why it showing 6027.

[#10]

@Phishdawg 

It looks as if you're using the TRANSPOSE function with each of the formulas. That was not what I proposed. The TRANSPOSE function was only needed (effective) with the formula that got the unique hours from the FILTER function.

If you would be so kind, since I am seeing only the image of the end result, it would be more helpful if you posted a copy of that actual spreadsheet.

[#11]

@mathetes 
I'm confused. Below are both - Yours, with a 'Transpose' in the 'Number Tix; and my with the same 'Transpose' in the 'Number Tix'.

I've attached the test data.

Thank you.

This is mine, with a same 'Transpose' in the Number Tix -

[#12]

@Phishdawg 

I'm confused. Below are both - Yours, with a 'Transpose' in the 'Number Tix; and my with the same 'Transpose' in the 'Number Tix'.

I'm sorry. You deserve to be confused. It was my mistake; that TRANSPOSE, in that formula, slipped by me. It was utterly unnecessary, totally redundant in that place. Attached is what I thought I'd sent. Basically the same formulas as before with the exception of the formula (as noted earlier) that arrays the hours in a horizontal fashion; THAT formula incorporates the TRANSPOSE function so that the dynamic array that results will be displayed horizontally rather than the "normal" vertical.

FWIW, I was asking not for a repeat of your Test Data sheet -- I already had that -- but the full data set, if you still need more help.

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.

[#13]

@Phishdawg 

Is there a way to make the output 'vertical'. Seems unnecessarily complicated but it's what is being requested, if possible.

By the way, if it helps at all, I agree completely with your hesitation. Not that it's unnecessarily complicated, but in fact arraying the data horizontally as you've apparently been requested to do, is less clear when the number of columns gets large than a similar number of rows. The data are the same either way, but fifty or sixty columns (two months) are harder to review than the same number of rows. Not a LOT harder, but slightly. Our minds are more acquainted with data like that (think of stock listings or bank statements) in rows.

[#14]

Done.

Thank you for your patience and assistance.

[#15]

@mathetes 
Not quite done, sorry.

Can I get the average wait time for the day on the line with the wait times?

[#16]

@Phishdawg 

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.

[#17]

@mathetes 
How do I carry the 'Daily/Hourly Average Wait Time' down the sheet?

When I drag it just replicates the number above.

Output Required:
The average per hour Wait Time (based on the date) in 'D' - 'O',
and the total Average Wait Time of 'D - O' in 'P'.

[#18]

@Phishdawg 

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.

[#19]

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,

[#20]

@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.