Formula, means and non following cells
 

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

Formula, means and non following cells
 

aude wrote:
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)*B 1: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

Formula, means and non following cells
 

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

"Franz Verga" wrote:

aude wrote:
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)*B 1: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