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!
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!