Hello,

I have been keeping track of my blood pressure with an Excel
spreadsheet. I list "date" on column A, "time" on column B, "Systolic"
on column C, "Dia" on column D, and "Pulse" on column E (Columns A, B,
C, D, E). Currently I have a formula to give an overall average of
column C in one box, Column D in another, and Column E in another.

I want to add an Average box for each column C, D, and E based on the
time of day (column B) to include only if they are readings between a
certain time (for example, time is after 5 PM but before midnight).
Does someone here know how to do this?

Any help with this would be much appreciated.

Thanks!

Tom

--
http://www.rvlover.net
http://weather.rvlover.net

How would you do it on paper? If we have your paper formula, we can tell you
how to do it in Excel.

Tyro

"Tom T" wrote in message
...
Hello,

I have been keeping track of my blood pressure with an Excel spreadsheet.
I list "date" on column A, "time" on column B, "Systolic" on column C,
"Dia" on column D, and "Pulse" on column E (Columns A, B, C, D, E).
Currently I have a formula to give an overall average of column C in one
box, Column D in another, and Column E in another.

I want to add an Average box for each column C, D, and E based on the
time of day (column B) to include only if they are readings between a
certain time (for example, time is after 5 PM but before midnight). Does
someone here know how to do this?

Any help with this would be much appreciated.

Thanks!

Tom

--
http://www.rvlover.net
http://weather.rvlover.net

This VBA code is a little crude because I am not use to working with times,
but it tested OK. I used columns G, H and I to accumulate the readings
between 5pm and 1159pm. You will need to format you column B as time in the
h:mm AM/PM
format for this to work. Even then you might get an error message. But it
is something to play with. I also used celss K2, L2 and M2 to display the
averages,

Sub TODavg()
Dim lr, lr2 As Long
lr = Cells(Rows.Count, 2).End(xlUp).Row
Set myRange = Sheets(1).Range("B2:B" & lr)
For Each c In myRange
lr2 = Cells(Rows.Count, 7).End(xlUp).Row
If c.Value = #5:00:00 PM# And c.Value <= #11:59:00 PM# Then
Sheets(1).Range(c.Offset(0, 1), c.Offset(0, 3)).Copy _
Destination:=Sheets(1).Range("G" & lr2 + 1)
End If
Next
Application.CutCopyMode = False
lr3 = Cells(Rows.Count, 7).End(xlUp).Row
Range("K2") = Application.WorksheetFunction.Average(Range("G2:G" & lr3 - 1))
Range("L2") = Application.WorksheetFunction.Average(Range("H2:H" & lr3 - 1))
Range("M2") = Application.WorksheetFunction.Average(Range("I2:I" & lr3 - 1))
End Sub

"Tom T" wrote:

Hello,

I have been keeping track of my blood pressure with an Excel
spreadsheet. I list "date" on column A, "time" on column B, "Systolic"
on column C, "Dia" on column D, and "Pulse" on column E (Columns A, B,
C, D, E). Currently I have a formula to give an overall average of
column C in one box, Column D in another, and Column E in another.

I want to add an Average box for each column C, D, and E based on the
time of day (column B) to include only if they are readings between a
certain time (for example, time is after 5 PM but before midnight).
Does someone here know how to do this?

Any help with this would be much appreciated.

Thanks!

Tom

--
http://www.rvlover.net
http://weather.rvlover.net

Here's another way.

Use a helper column (my example used F) to have a 1 if the time is
within your bounds, otherwise a zero. I put the lower time bound
(05:00:00 PM) in I2, and the upper time bound (11:59:59 PM) in I3.
Midnight doesn't work well because excel uses 0 (zero) for 12:00:00 AM,
so it is hard to tell if a time is less than midnight. Keep that in
mind when you enter your times too.

In F2 put =IF(AND($B2$I$2,$B2<$I$3),1,0) and fill down, say to row 100
or whatever you have data in the other columns. As you adjust your time
bounds in I2 and I3, the cells in F will change to show cells matching
those bounds.
To get the average, use sumproduct to multiply your values in C (or D or
E) by the value (0 or 1) in F. I can't get sumproduct to use the entire
column without a #NUM! error, so I used up to row 100. Adjust as needed.
In your "timed average" cell for Systolic, put
=SUMPRODUCT($C1:$C100,$F1:$F100)/COUNTIF($F1:$F100,1)
For Dia and Pulse, change the $C to $D or $E.

Len

Tom T wrote:
Hello,

I have been keeping track of my blood pressure with an Excel
spreadsheet. I list "date" on column A, "time" on column B, "Systolic"
on column C, "Dia" on column D, and "Pulse" on column E (Columns A, B,
C, D, E). Currently I have a formula to give an overall average of
column C in one box, Column D in another, and Column E in another.

I want to add an Average box for each column C, D, and E based on the
time of day (column B) to include only if they are readings between a
certain time (for example, time is after 5 PM but before midnight).
Does someone here know how to do this?

Any help with this would be much appreciated.

Thanks!

Tom