+ Reply to Thread
Results 1 to 5 of 5

Lowest, Highest, Average Times

  1. #1
    Registered User
    Join Date
    01-20-2017
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    199

    Lowest, Highest, Average Times

    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.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,875

    Re: Lowest, Highest, Average Times

    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.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    . Then I formatted those [h]:mm:ss. (2:03:12)That is much easier to work with.

    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.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Do the same in 'Highest Times' and 'Average Times' with these.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    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

  3. #3
    Registered User
    Join Date
    01-20-2017
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    199

    Re: Lowest, Highest, Average Times

    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.

  4. #4
    Registered User
    Join Date
    01-20-2017
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    199

    Re: Lowest, Highest, Average Times

    Dave,

    I added a bit to the code for the #DIV/0

    Please Login or Register  to view this content.
    Thanks again!

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,875

    Re: Lowest, Highest, Average Times

    You're welcome. Glad it works and that you solved the #DIV/0 errors. Thanks for the feedback, rep and marking this thread SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to change the lowest to highest into Highest to lowes ??
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-30-2015, 03:38 AM
  2. [SOLVED] how to find the lowest to highest value and display the name who is the lowest and the hig
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 09-23-2015, 09:20 PM
  3. How to: Highlight lowest and highest values multiple times in same row?
    By Luz1978 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-29-2014, 01:02 AM
  4. Replies: 3
    Last Post: 07-28-2014, 06:08 PM
  5. Replies: 9
    Last Post: 05-06-2013, 04:48 AM
  6. Average, except highest and lowest values
    By tiwas in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-06-2012, 12:27 PM
  7. Replies: 10
    Last Post: 07-17-2012, 01:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1