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

=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

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

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

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

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

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

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