Time Incremented Data Dynamtic Fields

[zunebuggy]

I have a column of numeric data and next to it a numeric column that represents time increments in .005 seconds. Both columns are the same length every time. I have two additional fields that correlate to the time increments values. One is labeled Start Time and one is End Time. When I update these fields (for example .01 start and .110 end time) I would like it to only look at the numeric data next to the selected time increment range and get the MIN, MAX and AVERAGE of those data items only. So it will change every time I update the Start Time and End Time increment fields.

 

[Mackers]

Hi zunebuggy

If I understand you correctly, you want to aggregate some data based on the time in one of the columns, let's say column A, being after the start time and before the end time you provide. If that is the case, what you need to use is an array formula containing something like the following:

IF(TimeColumn<= EndTime, IF(TimeColumn>= StartTime, ValuesColumn))

This should return an array that says something like {FALSE, FALSE, FALSE, value, value, value, FALSE, FALSE}, for example, where the values are only returned if the time is in your selected range, and FALSE is returned otherwise. You can then stick that into AVERAGE(), MIN() and MAX(), using control+shift+enter, to get the results you need, so if your time is in column A, your start date is in D1 and your end date in E1 and your values are in column B:

=AVERAGE(IF(A:A<=E1, IF(A:A>=D1, B:B)))

The problem I see is that your TimeColumn is the start time and not the end time, so you may need to change the formula slightly, as follows: (0.05 is an example figure)

IF(TimeColumn+0.05<= EndTime, IF(TimeColumn>= StartTime, ValuesColumn))

Let me know if that helps or if I've got the wrong end of the stick.

Thanks

Mackers