Define Range based on values for "average" calculation of data in another tab

[Lilium]

I have a table with temperatures from thermometers T1 to T4 taken on different time intervals 'Time" collumn in the "Data" tab. Now I want to calculate the average of all the thermometer data in the table (A2:E24) based on the range from "start time" to the "end time" given in the tab "Analysis".
So, If I fill in or change the "start time" and "end time" in cells B2 and D2 of the "Analysis" tab, the cell A5 should calculate the Average of the "T" values (T1 to T4) in the "Data" tab in the part of the range (A2:E24) from the chosen start time (B2) till the end time (D2).

I can't seem to think of a way of doing this. any help is much appreciated. Thanks!!

 

[kevin9999]

There's probably a more elegant solution to this (I couldn't get AverageIfs to work for some reason) but I think this gives the correct answer (NB random temps used)

Book1
	A	B	C	D
2	Start time:	08:33:00	End time:	08:48:00
3
4
5	23.4703
Analysis

Cell Formulas
Range	Formula
A5	A5	=SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24))/SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24<>""))

 

[Lilium]

Thanks Kevin! I don't understand the formula but it works like a charm! Much appreciated. Now I was wondering if I could alter the formula to calculate the MIN and MAX instead of the average? Difficult to say if I dont understand the formula :'). Do you think this is possible?

 

[kevin9999]

The formula is basically 2 Sumproducts - the first sums all the values in the time range, the second counts the values in the time range - then it's simply the sum/count to get the average. As far as Min/Max goes, try this:

Book1
	A	B	C	D
1
2	Start time:	08:33:00	End time:	08:48:00
3
4
5	23.68071	Average
6
7	22.04322	Min
8
9	24.95149	Max
Analysis

Cell Formulas
Range	Formula
A5	A5	=SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24))/SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24<>""))
A7	A7	=MIN(IF((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2),Data!$B$2:$E$24))
A9	A9	=MAX(IF((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2),Data!$B$2:$E$24))

 

[Lilium]

The formula is basically 2 Sumproducts - the first sums all the values in the time range, the second counts the values in the time range - then it's simply the sum/count to get the average. As far as Min/Max goes, try this:

Book1
	A	B	C	D
1
2	Start time:	08:33:00	End time:	08:48:00
3
4
5	23.68071	Average
6
7	22.04322	Min
8
9	24.95149	Max
Analysis

Cell Formulas
Range	Formula
A5	A5	=SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24))/SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24<>""))
A7	A7	=MIN(IF((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2),Data!$B$2:$E$24))
A9	A9	=MAX(IF((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2),Data!$B$2:$E$24))

Thanks Kevin!

 

[Lilium]

Kevin, one last thing if you would like to help me out. In regard of the SUMPRODUCT formula you pasted for Cell A5. Let's say T1 and T3 values are in one area and T2 and T4 values are in an other physical area, and i would like to calculate the average temperatures of each of those areas. Then I wont be able to mention the range Data!$B$2:$E$24 in the SUMPRODUCT formula but I would need a combination of B2:B24 and D2:D24 and another sumproduct formula with C2:C24 and E2:E24. However if I fill in B2:B24;D2:D24 in stead of B2:E24 i get an error.
What am I doing wrong?

 

[kevin9999]

You can do this by referencing the headers (e.g. T1 & T3 only) within the Sumproduct formula. Like this:

lilium.xlsx
	A	B	C	D
1
2	Start time:	08:33:00	End time:	08:48:00
3
4
5	22.534375	Average
6
7	21.0	Min
8
9	24.9	Max
10
11
12	22.853125	Average	T1 & T3 only
13
14	22.215625	Average	T2 & T4 only
Analysis

Cell Formulas
Range	Formula
A5	A5	=SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24))/SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*(Data!$B$2:$E$24<>""))
A7	A7	=MIN(IF((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2),Data!$B$2:$E$24))
A9	A9	=MAX(IF((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2),Data!$B$2:$E$24))
A12	A12	=SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*((Data!$B$1:$E$1="T1")+(Data!$B$1:$E$1="T3"))*(Data!$B$2:$E$24<>""),Data!$B$2:$E$24)/SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*((Data!$B$1:$E$1="T1")+(Data!$B$1:$E$1="T3"))*(Data!$B$2:$E$24<>""))
A14	A14	=SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*((Data!$B$1:$E$1="T2")+(Data!$B$1:$E$1="T4"))*(Data!$B$2:$E$24<>""),Data!$B$2:$E$24)/SUMPRODUCT((Data!$A$2:$A$24>=$B$2)*(Data!$A$2:$A$24<=$D$2)*((Data!$B$1:$E$1="T2")+(Data!$B$1:$E$1="T4"))*(Data!$B$2:$E$24<>""))