Time if/then caclculate average formula?

[Tom T]

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

 

[JLGWhiz]

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

 

[LenB]

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