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:
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)),"")
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 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.
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.