Average If

[Guest]

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

 

[Peo Sjoblom]

=AVERAGE(IF(B2:B366="Sun",C2:C366))

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom

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

 

[JulieD]

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

 

[Guest]

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

 

[Guest]

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.

 

[Peo Sjoblom]

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

 

[JulieD]

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

 

[Guest]

Thats the one thank you very much. Jay

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