Average of Values Between an Entered Time Range

DevonH

New Member
Joined
Jul 3, 2016
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,607
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Jul 3, 2016
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,607
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
you want Delete blank rows totally or don't calculate them In Average formula.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,607
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top