Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Average If
I have sheet containing 365 days column B contains the day of the week column
c contains a number. I want to average the numbers for each day of the week. i.e. average number for "Sun" This is a sheet modified daily and the cells for the dates that have not occured yet are blank. Any suggestions. Thank you |
#2
|
|||
|
|||
=AVERAGE(IF(B2:B366="Sun",C2:C366))
entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Jason" wrote in message ... I have sheet containing 365 days column B contains the day of the week column c contains a number. I want to average the numbers for each day of the week. i.e. average number for "Sun" This is a sheet modified daily and the cells for the dates that have not occured yet are blank. Any suggestions. Thank you |
#3
|
|||
|
|||
Hi Jason
here's two approaches depending on how the data is entered in column B if it's entered as text - Mon, Tue, Wed etc then use =SUMIF(B2:B366,"Sun",C2:C366)/COUNTIF(B2:B366,"Sun") if it's entered as a date and formatted to show the day of the week then you can use =SUMPRODUCT(--(WEEKDAY(B2:B366)=1)*C2:C366)/SUMPRODUCT(--(WEEKDAY(B2:B366)=1)) Cheers JulieD "Jason" wrote in message ... I have sheet containing 365 days column B contains the day of the week column c contains a number. I want to average the numbers for each day of the week. i.e. average number for "Sun" This is a sheet modified daily and the cells for the dates that have not occured yet are blank. Any suggestions. Thank you |
#4
|
|||
|
|||
Thank you Peo. How ever the result is dividing all the Sundays and giving my
a wrong answer. So far we have had 9 Sundays. The remaining cells are blank. Is thier a way to get this to do the math based only on the cells that have data in them? Thank you for the assistance "Peo Sjoblom" wrote: =AVERAGE(IF(B2:B366="Sun",C2:C366)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Jason" wrote in message ... I have sheet containing 365 days column B contains the day of the week column c contains a number. I want to average the numbers for each day of the week. i.e. average number for "Sun" This is a sheet modified daily and the cells for the dates that have not occured yet are blank. Any suggestions. Thank you |
#5
|
|||
|
|||
Nope that one isn't working either. It is still dividing by 52. I nelected to
say the columns A and B already have the Date and Day respectively for the whole year. "JulieD" wrote: Hi Jason here's two approaches depending on how the data is entered in column B if it's entered as text - Mon, Tue, Wed etc then use =SUMIF(B2:B366,"Sun",C2:C366)/COUNTIF(B2:B366,"Sun") if it's entered as a date and formatted to show the day of the week then you can use =SUMPRODUCT(--(WEEKDAY(B2:B366)=1)*C2:C366)/SUMPRODUCT(--(WEEKDAY(B2:B366)=1)) Cheers JulieD "Jason" wrote in message ... I have sheet containing 365 days column B contains the day of the week column c contains a number. I want to average the numbers for each day of the week. i.e. average number for "Sun" This is a sheet modified daily and the cells for the dates that have not occured yet are blank. Any suggestions. Thank you |
#6
|
|||
|
|||
I assumed that there was as many Sundays as there are values in C, are you
saying that you have all Sundays for a year while the values in C are not, if so use =AVERAGE(IF((B2:B366="Sun")*(C2:C366<""),C2:C366) ) entered the same way -- Regards, Peo Sjoblom "Jason" wrote in message ... Thank you Peo. How ever the result is dividing all the Sundays and giving my a wrong answer. So far we have had 9 Sundays. The remaining cells are blank. Is thier a way to get this to do the math based only on the cells that have data in them? Thank you for the assistance "Peo Sjoblom" wrote: =AVERAGE(IF(B2:B366="Sun",C2:C366)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Jason" wrote in message ... I have sheet containing 365 days column B contains the day of the week column c contains a number. I want to average the numbers for each day of the week. i.e. average number for "Sun" This is a sheet modified daily and the cells for the dates that have not occured yet are blank. Any suggestions. Thank you |
#7
|
|||
|
|||
Hi Jason
if you edit my second formula to =SUMPRODUCT(--(WEEKDAY(B2:B366)=1)*C2:C366)/SUMPRODUCT(--(WEEKDAY(B2:B366)=1)*(C2:C366<"")) (if working with dates) or use (if text) =SUMPRODUCT(--(B2:B366="Sun")*C2:C366)/SUMPRODUCT(--(B2:B366)="Sun")*(C2:C366<"")) Does this give you what you need Cheers julieD "Jason" wrote in message ... Nope that one isn't working either. It is still dividing by 52. I nelected to say the columns A and B already have the Date and Day respectively for the whole year. "JulieD" wrote: Hi Jason here's two approaches depending on how the data is entered in column B if it's entered as text - Mon, Tue, Wed etc then use =SUMIF(B2:B366,"Sun",C2:C366)/COUNTIF(B2:B366,"Sun") if it's entered as a date and formatted to show the day of the week then you can use =SUMPRODUCT(--(WEEKDAY(B2:B366)=1)*C2:C366)/SUMPRODUCT(--(WEEKDAY(B2:B366)=1)) Cheers JulieD "Jason" wrote in message ... I have sheet containing 365 days column B contains the day of the week column c contains a number. I want to average the numbers for each day of the week. i.e. average number for "Sun" This is a sheet modified daily and the cells for the dates that have not occured yet are blank. Any suggestions. Thank you |
#8
|
|||
|
|||
Thats the one thank you very much. Jay
"Peo Sjoblom" wrote: I assumed that there was as many Sundays as there are values in C, are you saying that you have all Sundays for a year while the values in C are not, if so use =AVERAGE(IF((B2:B366="Sun")*(C2:C366<""),C2:C366) ) entered the same way -- Regards, Peo Sjoblom "Jason" wrote in message ... Thank you Peo. How ever the result is dividing all the Sundays and giving my a wrong answer. So far we have had 9 Sundays. The remaining cells are blank. Is thier a way to get this to do the math based only on the cells that have data in them? Thank you for the assistance "Peo Sjoblom" wrote: =AVERAGE(IF(B2:B366="Sun",C2:C366)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Jason" wrote in message ... I have sheet containing 365 days column B contains the day of the week column c contains a number. I want to average the numbers for each day of the week. i.e. average number for "Sun" This is a sheet modified daily and the cells for the dates that have not occured yet are blank. Any suggestions. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGE problems | Excel Worksheet Functions | |||
average on 2 conditions | Excel Worksheet Functions | |||
Average | Excel Worksheet Functions | |||
calculate average hours worked | Excel Worksheet Functions | |||
average, array and offsets | Excel Worksheet Functions |