Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying filtered four cells by means of macro | Excel Discussion (Misc queries) | |||
what is this formula means =+$R$16 | Excel Worksheet Functions | |||
Can someone tell me what this formula means? | Excel Worksheet Functions | |||
Can anyone tell me what this formula means? | Excel Discussion (Misc queries) | |||
hi i would like to know what this means } | New Users to Excel |