Commands in a changing interval

Hi there,

I have got the following problem - it would be great if somebody could help me with this:

I need to find the maximum values of different parts of a large excel column. Currently, the excel data is divided in columns with (simplified) the following headings:

Now, I know the starttime and endtime of a certain interval (eg. INTERVAL 1: 09:00 - 10:00, so 60 minutes, INTERVAL 2: 10:00 - 10:20, so 20 minutes, etc.), and I want to find the corresponding maximum value IN that shift (so in this case, the maximum temperature from 9:00 until 10:00, from 10:00 until 10:15, etc).

Explained in another way; I want to create a command ("give the maximum value", or "give the average" or whatever) which searches in a column that starts at a certain value (I got those values in another column) and searches until a certain value (idem).

Would be great if somebody can help me here.

Fidodido

Here more about the data:
I got two files:

1) shows the start time and end time of intervals

START TIME (hh:mm)	END TIME (hh:mm)
09:00	10:00
10:00	10:20
10:40	12:00
etc	etc

2) shows the time (hh:mm) and the corresponding value at that time (temperature)

TIME (hh:mm)	Temperature (C)
09:00	10
09:01	10,2
09:02	10,2
09:03	10,3
...	...
10:00	11
etc	etc

I know that I can correlate the starttime and endtime with the Time column with a VLOOKUP function, which will give the temperature at the start time and end time. But I hope it is clear that I do not want only these values, but I want the maximum value between those timepoints.

Assuming your table 1 is in Sheet1, starting with headers at A1 and B1, and assuming table 2 is in Sheet2, A1:B8, try in Sheet1, C2:

=MAX(IF(Sheet2!$A$2:$A$8>=A2,IF(Sheet2!$A$2:$A$8<=B2,Sheet2!$B$2:$B$8)))

adjust the sheetnames and ranges to suit, then you must confirm this formula with CTRL+SHIFT+ENTER, not just ENTER, then copy down (this is an array formula)

You can replace MAX with MIN or AVERAGE or almost any other similar function.

Note if you have EXCEL 2007 or later, you can use AVERAGEIFS or COUNTIFS, or SUMIFS instead of the array formula.

e.g.

=AVERAGEIFS(Sheet2!$B$2:$B$8,Sheet2!$A$2:$A$8,">="&A2,Sheet2!$A$2:$A$8,"<="&B2)

nbvc said:

assuming your table 1 is in sheet1, starting with headers at a1 and b1, and assuming table 2 is in sheet2, a1:b8, try in sheet1, c2:

=max(if(sheet2!$a$2:$a$8>=a2,if(sheet2!$a$2:$a$8<=b2,sheet2!$b$2:$b$8)))

adjust the sheetnames and ranges to suit, then you must confirm this formula with ctrl+shift+enter, not just enter, then copy down (this is an array formula)

you can replace max with min or average or almost any other similar function.

Note if you have excel 2007 or later, you can use averageifs or countifs, or sumifs instead of the array formula.

E.g.

=averageifs(sheet2!$b$2:$b$8,sheet2!$a$2:$a$8,">="&a2,sheet2!$a$2:$a$8,"<="&b2)

Click to expand...

great! It works! Thank you very much!

Aaiiii, this additional question is for the enthusiast:

Table 2 [time | temperature] is not complete; it does not contain a temperature for every minute.
However, the intervals are starting or ending sometimes ON these missing data points (eg. interval 1. start = 9:00 end = 10:00, but there is no temperature for 9:00, but for 9:01 only).

Several functions in Excel supply the possibility to approximate the input parameters (eg. vlookup-function) - is it possible to add this into the formula as well?

It should still work, shouldn't it? It will take the max of all the values that fall between 9:00 and 10:00 am.