How do I average every 3rd cell in a column of a spreadsheet?
 

I have a spreadsheet with headers of the days of the week. Below that is the
date, then location 1's sales then below that is location 2's sales. It is a
running year long listing and I want to calculate the Average Sales for each
day of the week.

For Example:

Monday Location
12/20/2010
$3,523.12 store #1
$4,632.12 store #2
12/27/2010
$3,322.12 store #1
$4,545.12 store #2

I just picked 2 dates that are a week apart. In the example above, I want
to average $3523.12 and $3322.12 and so forth (this would be location1) and
then separately I want to average $4632.12 and $4545.12 and so forth (this
would be location2). Columns for Tue through Sat are between the columns in
the example above.

The date being in there throws off using sumif and countif (with location
being the criteria for the count). I hope I'm making sense.

How do I average every 3rd cell in a column of a spreadsheet?
 

One way looking at the store number. ARRAY formula that must be entered
using ctrl+shift+enter

=AVERAGE(IF((RIGHT(B2:B7,1)="1"),A2:A7))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sean" wrote in message
...
I have a spreadsheet with headers of the days of the week. Below that is
the
date, then location 1's sales then below that is location 2's sales. It
is a
running year long listing and I want to calculate the Average Sales for
each
day of the week.

For Example:

Monday Location
12/20/2010
$3,523.12 store #1
$4,632.12 store #2
12/27/2010
$3,322.12 store #1
$4,545.12 store #2

I just picked 2 dates that are a week apart. In the example above, I want
to average $3523.12 and $3322.12 and so forth (this would be location1)
and
then separately I want to average $4632.12 and $4545.12 and so forth (this
would be location2). Columns for Tue through Sat are between the columns
in
the example above.

The date being in there throws off using sumif and countif (with location
being the criteria for the count). I hope I'm making sense.

How do I average every 3rd cell in a column of a spreadsheet?
 

Hi,

Assume that your data is in range D30:E36 (D31:E31 has Monday and Location).
You can try this

=SUMPRODUCT((RIGHT(E31:E36,1)="1")*(D31:D36))/COUNTIF(E31:E36,"*#1")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Sean" wrote in message
...
I have a spreadsheet with headers of the days of the week. Below that is
the
date, then location 1's sales then below that is location 2's sales. It
is a
running year long listing and I want to calculate the Average Sales for
each
day of the week.

For Example:

Monday Location
12/20/2010
$3,523.12 store #1
$4,632.12 store #2
12/27/2010
$3,322.12 store #1
$4,545.12 store #2

I just picked 2 dates that are a week apart. In the example above, I want
to average $3523.12 and $3322.12 and so forth (this would be location1)
and
then separately I want to average $4632.12 and $4545.12 and so forth (this
would be location2). Columns for Tue through Sat are between the columns
in
the example above.

The date being in there throws off using sumif and countif (with location
being the criteria for the count). I hope I'm making sense.

How do I average every 3rd cell in a column of a spreadsheet?
 

So location names are in column H, and your first date is in row 2?

=SUMIF($H$3:$H$1000,"store #1",A$3:A$1000)/COUNTIF($H$3:$H$1000,"store #1")

if the locations are not named in column H then you need something like:

=SUMPRODUCT($A$3:$A$1000*
(MOD(ROW($A$3:$A$1000)-CELL("row",$A$3:$A$1000),NrOfStores+1)=0))*
(NrOfStores+1)/COUNT($A$2:$A$1000)

which will average every NrOfStores+1 cell in column A as your subject line
requests.

HTH

Steve D.


"Sean" wrote in message
...
I have a spreadsheet with headers of the days of the week. Below that is
the
date, then location 1's sales then below that is location 2's sales. It
is a
running year long listing and I want to calculate the Average Sales for
each
day of the week.

For Example:

Monday Location
12/20/2010
$3,523.12 store #1
$4,632.12 store #2
12/27/2010
$3,322.12 store #1
$4,545.12 store #2

I just picked 2 dates that are a week apart. In the example above, I want
to average $3523.12 and $3322.12 and so forth (this would be location1)
and
then separately I want to average $4632.12 and $4545.12 and so forth (this
would be location2). Columns for Tue through Sat are between the columns
in
the example above.

The date being in there throws off using sumif and countif (with location
being the criteria for the count). I hope I'm making sense.