+ Reply to Thread
Results 1 to 7 of 7

Average Elapsed Time

  1. #1
    Registered User
    Join Date
    03-18-2023
    Location
    Brentwood, TN
    MS-Off Ver
    Office 2013
    Posts
    4

    Average Elapsed Time

    Excel 2013. All cells in this spreadsheet are formatted as Time.

    Column E is the Start Time.
    Column F includes minutes to deduct from the Elapsed time.
    Column G contains no data.
    Column H includes minutes to deduct from the Elapsed time.
    Column I is the End Time.

    Column J contains cells with this formula to calculate the Elapsed Time of the values in the row:
    For example: =IF(I16=E16,I16-E16-F16-H16,I16-F16-H16+1-E16)

    E F G H I J
    16 6:00 AM 0:15 0:15 9:00 AM 2:30
    17 11:45 PM 0:30 1:30 7:00 AM 5:15
    18 11:00 PM 0:15 0:15 9:00 AM 9:30

    How can I calculate the average of the elapsed times in Column J?

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,706

    Re: Average Elapsed Time

    so, first, I'm assuming your J calc should really read

    =IF(I16>=E16,...)

    else it doesn't really make sense - i.e. your first result might be format as Time and thus show 2:30 but, in reality, it's actually storing 26:30 (1 day + 2:30) -- thereby distorting your Average.

    personally, I would change / simplify your approach in J to below:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to average, simply use =AVERAGE(Jx:Jy) where x & y represent your first / law row of times, again, format as time

  3. #3
    Registered User
    Join Date
    03-18-2023
    Location
    Brentwood, TN
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: Average Elapsed Time

    Thanks very much XLent. Your calculation works like a charm.

    One question though:

    The MOD function is used to find a remainder after a number is divided by another number.

    How are you using the MOD function and what is the ",1" in your use of the MOD function?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,387

    Re: Average Elapsed Time

    Spreadsheets store and manipulate date/time/calendar information in the form of double precision serial numbers. The integer portion of the numbers represents days/date, and the fractional portion represents fraction of a day or time of day. When you have days.fraction information, the MOD(day.fraction,1) function is a convenient way of extracting just the fraction/time of day information.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    03-18-2023
    Location
    Brentwood, TN
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: Average Elapsed Time

    Thanks MrShorty!

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,706

    Re: Average Elapsed Time

    more specifically, here, the MOD approach is useful for handling scenarios where you have only Time values (as opposed to DateTime) and where said times may cross midnight

    the main advantage is that the MOD(end-start,1) will always return the correct answer without need for manipulation of source values, e.g:

    where end > start

    =MOD("12:00"-"06:00",1) --> MOD( 0.25 , 1) --> 0.25

    where end < start

    =MOD("22:00"-"04:00",1) --> MOD( -0.75 , 1) --> 0.25

    so, this approach is the more succinct alternative to the traditional approach of:

    =if(end<start,end+1,end)-start

    HTH

  7. #7
    Registered User
    Join Date
    03-18-2023
    Location
    Brentwood, TN
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: Average Elapsed Time

    Thank you XLent.

    I have been using spreadsheets since Lotus 1-2-3. I actually programmed (macros) a large budget once and later ported it to Excel. How have you learned spreadsheets? Are you hired for spreadsheet work?

    Thanks again,
    Wayne

+ 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. Average Time Elapsed
    By Cannon26 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-11-2020, 09:26 AM
  2. Calculating Average Length of Treatment (Time Elapsed)
    By seancamw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-11-2019, 07:34 PM
  3. [SOLVED] Average and Standard deviation for elapsed time
    By Buzz1126 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-29-2016, 10:47 AM
  4. Calculating the average elapsed time in Excel 2010
    By lvirden in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-03-2014, 11:58 AM
  5. Replies: 1
    Last Post: 03-12-2012, 12:09 PM
  6. Converting and calculating average elapsed time
    By jamminalley in forum Excel General
    Replies: 7
    Last Post: 03-12-2012, 10:31 AM
  7. elapsed time average calculcations
    By relux in forum Excel General
    Replies: 6
    Last Post: 08-26-2005, 03:05 PM

Tags for this Thread

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