Compute average start time

[ibe98765]

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?

[zeddy]

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

[ibe98765]

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

[zeddy]

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

[ibe98765]

I will have to think on this over the next couple of days. Thanks very much!