Average IFS with multiple conditions

[joserdsousa]

Hello, guys.

I'm facing some trouble with an excel file. Either I'm stupid or I'm not using the proper tools.


I have a spreadsheet with more than 100.000 values.

The data are organized as followed:
Day/Month/ Year (Column 1)
Time (Column 2)
Energy consumption [kWh] (Column 3)

The data are organized with a step of 15min each , i.e.:
01/01/2016 || 00:00 || 121
01/01/2016 || 00:15 || 124
01/01/2016 || 00:30 || 122
01/01/2016 || 00:45 || 128
01/01/2016 || 01:00 || 123
01/01/2016 || 01:15 || 124
01/01/2016 || 01:30 || 122
01/01/2016 || 01:45 || 128
...
31/12/2016 || 23:45 || 95

What I'm trying to do is, organize the data hour by hour, and day by day of the year, like this, in separate columns and lines:
01/01/2016 || 00:00 || 122
01/01/2016 || 01:00 || 124
01/01/2016 || 02:00 || 122
01/01/2016 || 03:00 || 124
....
31/12/2016 || 23:00 || 125

I need also to perform a simple calculation, i.e., I want the average value of the energy consumption at the day 01/01/2016 at 00:00, 01:00, 02:00, etc. Because the values measured have a interval of 15min.

Thanks in advance

Note:
If you guys want I can send you the spread sheet with the data.

 

[mrshl9898]

Try:

D2 =HOUR(B1)
E2 =AVERAGEIFS(C:C,A:A,A1,D:D,D1)

 

[joserdsousa]

Thanks! It worked!

I also found a sollution with pivot tables.

Thank you