Average of Values Between an Entered Time Range

[DevonH]

I would like to calculate the average of all numbers between (and including) two entered times.

For example, if I entered 11:00:40 AM in A1, and 11:03:40 AM in A2, I want it to average all the values in B4:B100 that are between (and including) those two times in a table (the time values would be A4:A100). I would also like to not include any blank cells in the average.

Also I would need a formula for a 2nd calculation, that if the cells were blank, the average would assume all blank values were the same as previously entered values. As an example, using the same two entered times as above, say the table was:

11:00:20 AM 2
11:00:40 AM 5
11:01:00 AM 6
11:01:20 AM Blank
11:01:40 AM Blank
11:02:00 AM Blank
11:02:20 AM 2
11:02:40 AM 4
11:03:00 AM Blank
11:03:20 AM Blank
11:03:40 AM Blank
11:04:00 AM 7

The average would then calculate as (5+6+6+6+6+2+4+4+4+4)/10 = 4.7

Thank you for any help - it is very much appreciated!

 

[maabadi]

Please Update your Account Detail to We know what Version of Excel and OS You use to we have best option for you.
If you have excel Version of 2016 and Above Use Averageifs (average functions don't count blank cell as default)
Example:

=AVERAGEIFS($B$4:$B$100,$A$4:$A$100, ">=" & $A$1,$A$4:$A$100, "<=" & $A$2)

AND Why you replaced Blank cells at Example with above values for Taking Average?

 

[DevonH]

Thank you maabadi! I updated it as you requested - I am using 2010. Is there a way to modify the formula to remove blanks for 2010?


Also to answer your question, the reason why I want the blank values to be the same as the value above it, is because in the field where this data is being entered, if the cells are not being entered by the person inputting the data, it is assumed that the value has stayed the same as the value above it. Basically it's just not practical for the operator to enter the data on every single cell if it's not changing.

 

[maabadi]

you want Delete blank rows totally or don't calculate them In Average formula.

 

[maabadi]

Try this:

Book1
	A	B	C	D	E	F	G
1	11:00:40
2	11:03:40
3
4	11:00:20	2	2		AverageIFS	4.7
5	11:00:40	5	5
6	11:01:00	6	6
7	11:01:20		6
8	11:01:40		6
9	11:02:00		6
10	11:02:20	2	2
11	11:02:40	4	4
12	11:03:00		4
13	11:03:20		4
14	11:03:40		4
15	11:04:00	7	7
16
17
Sheet2

Cell Formulas
Range	Formula
F4	F4	=AVERAGEIFS($C$4:$C$100,$A$4:$A$100, ">=" & $A$1,$A$4:$A$100, "<=" & $A$2)
C4:C15	C4	=IF(B4="",C3,B4)