Average of Values Between an Entered Time Range

DevonH

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

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Well-known Member
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:
Excel Formula:
``=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

New Member
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.

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

Well-known Member
Try this:
Book1
ABCDEFG
111:00:40
211:03:40
3
411:00:2022AverageIFS4.7
511:00:4055
611:01:0066
711:01:206
811:01:406
911:02:006
1011:02:2022
1111:02:4044
1211:03:004
1311:03:204
1411:03:404
1511:04:0077
16
17
Sheet2
Cell Formulas
RangeFormula
F4F4=AVERAGEIFS(\$C\$4:\$C\$100,\$A\$4:\$A\$100, ">=" & \$A\$1,\$A\$4:\$A\$100, "<=" & \$A\$2)
C4:C15C4=IF(B4="",C3,B4)

Replies
4
Views
87
Replies
5
Views
73
Replies
4
Views
86
Replies
4
Views
205
Replies
5
Views
142

1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back