I have attached a workbook with sample data. There are names and events and what I would like to do is calculate lowest, highest, and average times for each person participating in each event. Is it possible to use index match to perform the matches?
I am also having trouble with the elapsed times and not time of day so I separated the times in the data to hours/minutes/seconds in different columns. Is there a better way of logging the times?
I appreciate any help or suggestions.
I inserted a column (now column C 'Data Log'), removed data validation from it and put this formula in C2 and filled down to combine the hour/minute/seconds.. Then I formatted those [h]:mm:ss. (2:03:12)That is much easier to work with.Formula:
Please Login or Register to view this content.
After that try array entering this in C2 of 'Lowest Times' and filling down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Do the same in 'Highest Times' and 'Average Times' with these.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Your workbook returns only one time for each. The rest are either 0 or #DIV/0!
Dave
Thanks Dave that works fantastic. I am learning so much from everyone here. I realized that my data set could have improved with more scenarios sorry about that.
Dave,
I added a bit to the code for the #DIV/0
Thanks again!
Please Login or Register to view this content.
You're welcome. Glad it works and that you solved the #DIV/0 errors. Thanks for the feedback, rep and marking this thread SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks