vendredi 30 septembre 2016

Average column by specific datetime associated values

I have one column with the time in format "dd/mm/yyyy hh:mm" and another with the temperature for that time point. I am looking to calculate the average temperature of the day and night of each month separately. I.e. average all temperatures between 06:00 and 18:00 in May and all temperature between 18:00 and 06:00 in May and then the same for March and so on.

    Time    Celsius(C)
06/05/2016 10:49    28
06/05/2016 11:49    29
06/05/2016 12:49    31
06/05/2016 13:49    27.5
06/05/2016 14:49    24
06/05/2016 15:49    25
06/05/2016 16:49    24.5
06/05/2016 17:49    23.5
06/05/2016 18:49    23
06/05/2016 19:49    22.5
06/05/2016 20:49    22.5

I am currently using the following formula:

=AVERAGEIFS(C2:C3643,B2:B3643,">=01/05/2016",B2:B3643,"<=31/05/2016",B2:B3643,">=01/05/2016 06:00",B2:B3643,"<=31/05/2016 18:00")

To try and calculate an average if the date is within May and during the day - however it doesn't appear to be working and when I change the hour periods it still spits out the same number (which is the average for the month).

Aucun commentaire:

Enregistrer un commentaire