Calculate average for multiple ranges for multiple criteria

Hello,

I would like to calculate average values from multiple different ranges with varying number of sizes for multiple criteria. I have:


A B C D E
1 Day Time Hour Temperature Average Temp.
2 15.02.13 09:02:15 10 20,1 20,3
3 15.02.13 09:02:45 10 20,5
4 15.02.13 10:01:15 11 21,5
5 15.02.13 10:02:15 11 21,7
6 15.02.13 11:02:15 12 22
7 15.02.13 11:02:15 12 22,5
8 16.02.13 09:02:15 10 20,1
9 16.02.13 09:02:45 10 20,5
10 16.02.13 10:01:15 11 21,5
11 16.02.13 10:02:15 11 21,7
12 16.02.13 11:02:15 12 22
14 16.02.13 11:02:15 12 22,5
15 16.02.13 13:02:45 14 23,5

I want to get average temperature for a same day (in column A) and same hour (in column C) automatically calculated and written in the first row (in column E) for the corresponding day and hour. Example: 20,1 and 20,5 for day 15.02.2013 hour 10 -> average 20,3 in E2. Can anyone please help me?

Thank you!

Did you looked at AVERAGEIFS function?

thank you for your quick reply.

I have found solutions to similar problems but could not understand howto use AVERAGEIFS function for my problem. Better to say, in the examples I found, the ranges are given manually into the function. However, the table is much larger than the simplified form here, there are 1000s of entrys... and the number for entries correspond to same day and hour are very different...

I think it would be better to upload a small sample workbook with some data to work on it...

I mean in E2 and copy down this formula.

=AVERAGEIFS($D$2:$D$13,$A$2:$A$13,A2,$C$2:$C$13,C2)

AVERAGEIFS works in Excel >=2007.

i get: "the formula you typed contains an error highlighting "$D$13,$A$2" ...
besides this syntax problem, this formula must be placed in the corresponding cells in column E automatically, since there are thousands of rows in the original table..

Please help me !

Thank you..

See the example,pls

In Germany you must use semi colons as separators as we do in Greece. That's why the error....