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
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 |
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.
- Done.
Thank you for your patience and assistance. 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.
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.
