Formula, means and non following cells

[Guest]

Hi,

I've got 2 colomns, one with dates and times, and one with temperatures. I
ve been recording temperature every hour for 3 weeks.

I want to know the mean temperature for days and the mean temperature for
nights during these 3 weeks.

Is there any formula that I could use to get a mean of data included betweem
8am-17 pm (day) and between 18pm-7am (night) ???

Thanks

 

[Franz Verga]

Hi,

I've got 2 colomns, one with dates and times, and one with
temperatures. I ve been recording temperature every hour for 3 weeks.

I want to know the mean temperature for days and the mean temperature
for nights during these 3 weeks.

Is there any formula that I could use to get a mean of data included
betweem 8am-17 pm (day) and between 18pm-7am (night) ???

Thanks

Hi Aude,

I think you could use this two formulas:

=SUMPRODUCT((HOUR(A1:A27)>=8)*(HOUR(A1:A27)<=17)*B1:B27)/SUMPRODUCT((HOUR(A1:A27)>=8)*(HOUR(A1:A27)<=17))

for the average temperature between 8am-17 pm

and

=SUMPRODUCT(((HOUR(A1:A27)>=18)+(HOUR(A1:A27)<=7))*B1:B27)/SUMPRODUCT((HOUR(A1:A27)>=18)+(HOUR(A1:A27)<=7))

for the average temperature between 18pm-7am.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Thank you so much Franz, it works perfectly and you saved me so much time !!!!
I know it was quite a basic problem but I'm a beginner with programming and
I'm really glad that you took the time to answer me ))

So thank you again !!

aude