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
|