Selecting Data based on Number Range in a Column

[anoopbal]

How do I select data based on a number range in a column.

As shown in the uploaded file, I want people to type the time range(red color) in the 2 cells shaded blue. And excel will calculate the averages for the variables shown in green (Actual RMR, R, Vo2 and so forth)

for example, the Actual RMR=AVERAGE(L166:L235). L166 is time 9.31 in column H and L235 is time 13.31.

I hope it makes sense. Thank you so much!

 

[GraH - Guido]

Hi, you did upload a csv and not a workbook?

 

[Peter Bartholomew]

I suggest that the first thing to do is to convert your data range into a table with headings so that you can read what you are doing in terms of formulas.
You search the 'DataTable[t]' column for the start time
= MATCH( TimeLower-TIME(0,0,1), DataTable[t] ) + 1
which returns the startRow (the one second subtracted is to get the same value whether TimeLower is present in the table or whether there is a gap).

Then repeat the search for the end time
= MATCH( TimeUpper, DataTable[t] )
to get the row index of the final record of interest.

Everything you are interested in lies within a band across the middle of the table. If you use a defined Name 'interval' to refer to this range
= INDEX( DataTable, startRow, 0 ) : INDEX( DataTable, finalRow, 0 )
then every other quantity you are interested in is given as the intersection 'interval' and one of the columns of the table.

A typical formula might be of the form
= AVERAGE( interval DataTable[EEkc] )
where Excel treats the two ranges with a space between them as and intersection.

 

[anoopbal]

Thank you so much G and Peter!! I have uploaded the excel file.

Hey Peter, I will a few 100 of these and we will select different times based on a average from a column. so it will not always be in the middle. So the goal is to type the time range based a graph and get the results. So this time range could vary for different sheets.

Hope it makes sense. thank you again!

 

[NARAYANK991]

Hi ,

The time data in cell B23 , for example , is 09:31:00 , which 9:31 AM.

The time data in column H , ranges from an initial value of 00:00:04 to a final value of 00:20:09.

How we relate one to the other ?

Narayan

 

[Peter Bartholomew]

I won't pretend to understand the use case but the attached selects a 'temporal slice' from your data and runs stats over that. There were a couple of blank records I deleted so the numbers will have changed. I have used conditional formatting to highlight the times selected.