Results 1 to 5 of 5
  1. #1
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts

    Compute average start time

    I have a spreadsheet where I track many things that occur during my day. One of them is time I go to sleep, time I wake up and total time sleeping over x days (last 5, 10 & 30 days).

    Here is a sample array formula I use for this (Column A is the date and column J is the total sleep time):

    =AVERAGE(IF(ISNUMBER(J4:J370)*(A4:A370>((TODAY()-1)-5))*(A4:A370<=(TODAY()-1)),J4:J370))

    Now, the total time in column J is computed from the start time (column H) and the end time (column I).

    I would like to have a formula that works similar to the above but on the START TIME in column H instead of the TOTAL TIME in column J).

    So if I went to bed at 10:00PM one day and 10:30PM the next day, I would expect to see 10:15PM as the average time I went to bed. I thought I could modify the above formula to do this but it does not seem to work out like I expect.

    Can anyone help me with this problem?

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    If your first date starts in column [A] row4 (as per your formula), and your bed time is column [H], your wake up time is column [i] and your sleep time is column [K], then..
    This formula will give you the average time you went to bed over the previous 5 days:
    =AVERAGE(OFFSET($A$1,MATCH(TODAY()-1,$A:$A,FALSE)-5,7,5,1))

    This formula will give you the average time you went to bed over the previous 10 days:
    =AVERAGE(OFFSET($A$1,MATCH(TODAY()-1,$A:$A,FALSE)-10,7,10,1))

    This formula gives you the average sleep time over the previous 5 days:
    =AVERAGE(OFFSET($A$1,MATCH(TODAY()-1,$A:$A,FALSE)-5,9,5,1))

    This formula gives you the average sleep time over the previous 10 days:
    =AVERAGE(OFFSET($A$1,MATCH(TODAY()-1,$A:$A,FALSE)-10,9,10,1))

    see my attached file as an example.

    Note: These are 'ordinary' formulas so you don't need to enter with Ctrl-Shift-Enter etc as you would for arrays.

    zeddy
    Attached Files Attached Files

  3. #3
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by zeddy View Post
    Hi

    If your first date starts in column [A] row4 (as per your formula), and your bed time is column [H], your wake up time is column [i] and your sleep time is column [K], then..
    This formula will give you the average time you went to bed over the previous 5 days:
    =AVERAGE(OFFSET($A$1,MATCH(TODAY()-1,$A:$A,FALSE)-5,7,5,1))

    This formula will give you the average time you went to bed over the previous 10 days:
    =AVERAGE(OFFSET($A$1,MATCH(TODAY()-1,$A:$A,FALSE)-10,7,10,1))

    This formula gives you the average sleep time over the previous 5 days:
    =AVERAGE(OFFSET($A$1,MATCH(TODAY()-1,$A:$A,FALSE)-5,9,5,1))

    This formula gives you the average sleep time over the previous 10 days:
    =AVERAGE(OFFSET($A$1,MATCH(TODAY()-1,$A:$A,FALSE)-10,9,10,1))

    see my attached file as an example.

    Note: These are 'ordinary' formulas so you don't need to enter with Ctrl-Shift-Enter etc as you would for arrays.

    zeddy
    Thanks, that's a start for me to ponder.

    But what I need is a 5, 10, 30 day formula for ALL of column H, displayed in cells at the bottom of column H. This will be a moving target as the days march forward in the calendar year. I think that requires an array formula like all the others I use in this spreadsheet. See the example below. The new calculation has to fit in this format.

    Excel sleep time example.jpg

    I

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    NO, you only need to use an Array formula if your dates are NOT in sorted order.

    OK, lets talk about the OFFSET formula in Excel.
    The OFFSET formula allows you to specify a starting point, then you tell it to move so many rows down and so many rows across.
    When you get to that new point, you can tell it to 'return' a block measuring x rows by y columns.

    It is this returned block that you want for calculating your Average.
    The returned block can be 5 rows, 10 rows, 30 rows etc and, for your purposes, just one column wide.

    So, for your Average calc over the last say, 5 days, in say, column H:
    We tell Excel to start in cell $H$1 as our start point
    We tell Excel to first find what row today's date is, in column [A]
    For this, we can just use
    =MATCH(TODAY(),$A:$A,FALSE)
    ..this says look for an EXACT match in column [A].
    So, if today was 21-MAY-2012, in the example file attached, it will be row 145.

    We then subtract 5 to 'go back' 5 days (i.e. to row 140, 16-May-2012 ).
    As our start point is already in column [H], we tell Excel to 'go across 0 columns to the right'.
    We then tell Excel to return a block of 5 rows.
    We tell Excel we need 1 column wide.
    So, the 'block' is:
    OFFSET(H$1,MATCH(TODAY(),$A:$A,FALSE)-5,0,5,1)

    So the formula to Average this block, in cell [H373] is :
    =AVERAGE(OFFSET(H$1,MATCH(TODAY(),$A:$A,FALSE)-5,0,5,1))

    For the last 30 days, in cell [H375], the formula is:
    =AVERAGE(OFFSET(H$1,MATCH(TODAY(),$A:$A,FALSE)-30,0,30,1))

    see attached file

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    ibe98765 (2012-05-21)

  6. #5
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    I will have to think on this over the next couple of days. Thanks very much!

Posting Permissions

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