COUNTIF by days of week
 

I have three columns of data as shown below, but for the entire year:

A B C
08/01/06 Tue 2999.78
08/02/06 Wed 3283.59
08/03/06 Thu 1516.91
08/04/06 Fri 2108.34
08/05/06 Sat 2035.85
08/06/06 Sun 3972.03
08/07/06 Mon 2949.30

I need a formula that can calculate the average daily revenue by the day of
week, so for year I would know the average daily revenue by Tuesdays or
Thursdays, etc.

Thanks!

COUNTIF by days of week
 

Use SUMPRODUCT. More info he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Dave
--
Brevity is the soul of wit.


"Raza" wrote:

I have three columns of data as shown below, but for the entire year:

A B C
08/01/06 Tue 2999.78
08/02/06 Wed 3283.59
08/03/06 Thu 1516.91
08/04/06 Fri 2108.34
08/05/06 Sat 2035.85
08/06/06 Sun 3972.03
08/07/06 Mon 2949.30

I need a formula that can calculate the average daily revenue by the day of
week, so for year I would know the average daily revenue by Tuesdays or
Thursdays, etc.

Thanks!

COUNTIF by days of week
 

Hi Raza,

=sunif(B2:b366,"Tue",C2:c366)/countif(b2:b366,"Tue")

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Raza" escreveu:

I have three columns of data as shown below, but for the entire year:

A B C
08/01/06 Tue 2999.78
08/02/06 Wed 3283.59
08/03/06 Thu 1516.91
08/04/06 Fri 2108.34
08/05/06 Sat 2035.85
08/06/06 Sun 3972.03
08/07/06 Mon 2949.30

I need a formula that can calculate the average daily revenue by the day of
week, so for year I would know the average daily revenue by Tuesdays or
Thursdays, etc.

Thanks!

COUNTIF by days of week
 

The only problem is that 'Tue' is not actually Tue. The formula for B2 is
=A2, and the formatting is set up so that it shows Tue. When I pasted the
formula, it did not find any 'Tue' becuase really its a date. Any help?

"Marcelo" wrote:

Hi Raza,

=sunif(B2:b366,"Tue",C2:c366)/countif(b2:b366,"Tue")

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Raza" escreveu:

I have three columns of data as shown below, but for the entire year:

A B C
08/01/06 Tue 2999.78
08/02/06 Wed 3283.59
08/03/06 Thu 1516.91
08/04/06 Fri 2108.34
08/05/06 Sat 2035.85
08/06/06 Sun 3972.03
08/07/06 Mon 2949.30

I need a formula that can calculate the average daily revenue by the day of
week, so for year I would know the average daily revenue by Tuesdays or
Thursdays, etc.

Thanks!

COUNTIF by days of week
 

ok,

use an auxiliar column (D for eg) with =weekday(a2) copy it down excel will
return 1 for Sun, 2 for Mon .... 7 for Sat

than use

=sumif(d2:d366,2,c2:c366)/countif(d2:d366,2)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Raza" escreveu:

The only problem is that 'Tue' is not actually Tue. The formula for B2 is
=A2, and the formatting is set up so that it shows Tue. When I pasted the
formula, it did not find any 'Tue' becuase really its a date. Any help?

"Marcelo" wrote:

Hi Raza,

=sunif(B2:b366,"Tue",C2:c366)/countif(b2:b366,"Tue")

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Raza" escreveu:

I have three columns of data as shown below, but for the entire year:

A B C
08/01/06 Tue 2999.78
08/02/06 Wed 3283.59
08/03/06 Thu 1516.91
08/04/06 Fri 2108.34
08/05/06 Sat 2035.85
08/06/06 Sun 3972.03
08/07/06 Mon 2949.30

I need a formula that can calculate the average daily revenue by the day of
week, so for year I would know the average daily revenue by Tuesdays or
Thursdays, etc.

Thanks!

COUNTIF by days of week
 

just to clarify this formula will average Mon figures,

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Marcelo" escreveu:

ok,

use an auxiliar column (D for eg) with =weekday(a2) copy it down excel will
return 1 for Sun, 2 for Mon .... 7 for Sat

than use

=sumif(d2:d366,2,c2:c366)/countif(d2:d366,2)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Raza" escreveu:

The only problem is that 'Tue' is not actually Tue. The formula for B2 is
=A2, and the formatting is set up so that it shows Tue. When I pasted the
formula, it did not find any 'Tue' becuase really its a date. Any help?

"Marcelo" wrote:

Hi Raza,

=sunif(B2:b366,"Tue",C2:c366)/countif(b2:b366,"Tue")

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Raza" escreveu:

I have three columns of data as shown below, but for the entire year:

A B C
08/01/06 Tue 2999.78
08/02/06 Wed 3283.59
08/03/06 Thu 1516.91
08/04/06 Fri 2108.34
08/05/06 Sat 2035.85
08/06/06 Sun 3972.03
08/07/06 Mon 2949.30

I need a formula that can calculate the average daily revenue by the day of
week, so for year I would know the average daily revenue by Tuesdays or
Thursdays, etc.

Thanks!

COUNTIF by days of week
 

Try this *array* formula:

=AVERAGE(IF(TEXT(B1:B365,"ddd")="tue",C1:C365))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Raza" wrote in message
...
The only problem is that 'Tue' is not actually Tue. The formula for B2 is
=A2, and the formatting is set up so that it shows Tue. When I pasted the
formula, it did not find any 'Tue' becuase really its a date. Any help?

"Marcelo" wrote:

Hi Raza,

=sunif(B2:b366,"Tue",C2:c366)/countif(b2:b366,"Tue")

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Raza" escreveu:

I have three columns of data as shown below, but for the entire year:

A B C
08/01/06 Tue 2999.78
08/02/06 Wed 3283.59
08/03/06 Thu 1516.91
08/04/06 Fri 2108.34
08/05/06 Sat 2035.85
08/06/06 Sun 3972.03
08/07/06 Mon 2949.30

I need a formula that can calculate the average daily revenue by the
day of
week, so for year I would know the average daily revenue by Tuesdays
or
Thursdays, etc.

Thanks!

COUNTIF by days of week
 

A variation of RagDyer's formula:

=AVERAGE(IF(weekday(B1:B365)=2,C1:C365))

Still an array formula.

RagDyer wrote:

Try this *array* formula:

=AVERAGE(IF(TEXT(B1:B365,"ddd")="tue",C1:C365))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Raza" wrote in message
...
The only problem is that 'Tue' is not actually Tue. The formula for B2 is
=A2, and the formatting is set up so that it shows Tue. When I pasted the
formula, it did not find any 'Tue' becuase really its a date. Any help?

"Marcelo" wrote:

Hi Raza,

=sunif(B2:b366,"Tue",C2:c366)/countif(b2:b366,"Tue")

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Raza" escreveu:

I have three columns of data as shown below, but for the entire year:

A B C
08/01/06 Tue 2999.78
08/02/06 Wed 3283.59
08/03/06 Thu 1516.91
08/04/06 Fri 2108.34
08/05/06 Sat 2035.85
08/06/06 Sun 3972.03
08/07/06 Mon 2949.30

I need a formula that can calculate the average daily revenue by the
day of
week, so for year I would know the average daily revenue by Tuesdays
or
Thursdays, etc.

Thanks!


--

Dave Peterson