How to count average in certain time range

Copper Contributor

Hello Excel wizards,

 

I'm not the best with Excel so I would like to ask for help with this problem I have.

 

I have 8 hours of measurement data and I want to know 30 minute averages in certain times.

 

Column A is for time (HH:mm) and B-F are measurement values. I have always calculated averages manually in each time frame, but now I want to make a tool where I can just import the data and set time criteria. For example, if the data is recorded from 08:00 to 16:00, and I want to know the average at 12:00-12:30. What function to use to add a value into average calculation if time is greater or equal to start time (12:00) and lower or equal than end time (12:30).

 

Sorry for confusing question and thanks for any help!

6 Replies
Suppose you have 12:00 in cell A1 and 12:30 in B1 and your data starts on row 3:
=AVERAGEIFS($B$3:$F$100,A3:A100,">="&A1,A3:A100,"<="&B1)

@Jan Karel Pieterse 

 

Hi Jan,

 

Thanks for your help!

 

I used your formula but it returns #DIV/0!. 

 

I tried to find an explanation for this but I can't get it work...

 

Any tips would be highly appreciated!

 

rutilusss_0-1582620253017.png

 

In that example I was trying to calculate it only for column B. 

 

What is in the cells in column A *precisely*? If you change their number format to general, what do they show?

@Jan Karel Pieterse 

 

I changed it to general and now A3-A5 are:

 

43871.44392
43871.4441
43871.44427

@rutilusss

 

I got it now!

 

Column A had actually YYYY/MM/DD HH:MM but only time was visible. 

 

Deleting the date fixed it. Thanks for your advices!

I figured as much, great you were able to solve the issue.