How to match date criteria then calculate an average of matches
 

I have a spreadsheet currently containing 2 sheets, the first sheet contains
raw data and several lines of information are tied to the same date which is
listed in column A, the data I want to sumarize is listed in columnar style
across the page. In the second sheet I want a summary by date, averages and
standard deviations. The second sheet also contains the date I'm looking to
match calculate. I know how to use the sumif and countif functions, but I
think this needs a combination of MATCH, AVERAGE, STDEV and OFFSET. I had
this working in another spreadsheet, but lost access to it in a move.

How to match date criteria then calculate an average of matches
 

You need to be more specific. Where is the data you want to average?

--
Biff
Microsoft Excel MVP


"Buffy M. Warren" <Buffy M. wrote in
message ...
I have a spreadsheet currently containing 2 sheets, the first sheet
contains
raw data and several lines of information are tied to the same date which
is
listed in column A, the data I want to sumarize is listed in columnar
style
across the page. In the second sheet I want a summary by date, averages
and
standard deviations. The second sheet also contains the date I'm looking
to
match calculate. I know how to use the sumif and countif functions, but I
think this needs a combination of MATCH, AVERAGE, STDEV and OFFSET. I had
this working in another spreadsheet, but lost access to it in a move.

How to match date criteria then calculate an average of matche
 

Sheet 1 column A contains dates that reoccur, column B contains data of
interest to calculate averages and standard deviations, also Row 1 contains
header or column title information. Sheet 2, or what I call the Summary
sheet, repeats the dates of interest (only once) in Column A, also has EXACT
same header list in Row 1. In cell B2 of Sheet 2 I want to average the
values from Sheet 1, with the same header (title) that match the date in cell
A2.

"T. Valko" wrote:

You need to be more specific. Where is the data you want to average?

--
Biff
Microsoft Excel MVP


"Buffy M. Warren" <Buffy M. wrote in
message ...
I have a spreadsheet currently containing 2 sheets, the first sheet
contains
raw data and several lines of information are tied to the same date which
is
listed in column A, the data I want to sumarize is listed in columnar
style
across the page. In the second sheet I want a summary by date, averages
and
standard deviations. The second sheet also contains the date I'm looking
to
match calculate. I know how to use the sumif and countif functions, but I
think this needs a combination of MATCH, AVERAGE, STDEV and OFFSET. I had
this working in another spreadsheet, but lost access to it in a move.

How to match date criteria then calculate an average of matche
 

See this screencap:

http://img103.imageshack.us/img103/369/avgat5.jpg

That shows the array formula** needed for the average. The array formula**
for the stdev uses the same structu

=AVERAGE(IF($A$3:$A$8=$A12,B$3:B$8))
=STDEV(IF($A$3:$A$8=$A12,B$3:B$8))

I put everything on the same sheet to make it easier to follow. All you need
to do is plug in the actual sheet name. Here's a tip that you might find
useful. Just like the screencap shows, I put everything on the same sheet.
After I've got all the formulas entered I'll then cut and paste the entire
block of formula data to the other sheet. Excel will automatically add the
sheet name for you. I find this to be easier than starting on the other
sheet and having to switch back and forth between sheets when writing the
formulas.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Buffy M. Warren" wrote in message
...
Sheet 1 column A contains dates that reoccur, column B contains data of
interest to calculate averages and standard deviations, also Row 1
contains
header or column title information. Sheet 2, or what I call the Summary
sheet, repeats the dates of interest (only once) in Column A, also has
EXACT
same header list in Row 1. In cell B2 of Sheet 2 I want to average the
values from Sheet 1, with the same header (title) that match the date in
cell
A2.

"T. Valko" wrote:

You need to be more specific. Where is the data you want to average?

--
Biff
Microsoft Excel MVP


"Buffy M. Warren" <Buffy M. wrote in
message ...
I have a spreadsheet currently containing 2 sheets, the first sheet
contains
raw data and several lines of information are tied to the same date
which
is
listed in column A, the data I want to sumarize is listed in columnar
style
across the page. In the second sheet I want a summary by date,
averages
and
standard deviations. The second sheet also contains the date I'm
looking
to
match calculate. I know how to use the sumif and countif functions,
but I
think this needs a combination of MATCH, AVERAGE, STDEV and OFFSET. I
had
this working in another spreadsheet, but lost access to it in a move.

How to match date criteria then calculate an average of matche
 

Thanks T. Valko - Absolutely fantastic, much more straight-forward than my
previous path, obviously it was too cumbersome for me to recreate. I haven't
had much practice using arrays but this gets me off on the right foot!

"T. Valko" wrote:

See this screencap:

http://img103.imageshack.us/img103/369/avgat5.jpg

That shows the array formula** needed for the average. The array formula**
for the stdev uses the same structu

=AVERAGE(IF($A$3:$A$8=$A12,B$3:B$8))
=STDEV(IF($A$3:$A$8=$A12,B$3:B$8))

I put everything on the same sheet to make it easier to follow. All you need
to do is plug in the actual sheet name. Here's a tip that you might find
useful. Just like the screencap shows, I put everything on the same sheet.
After I've got all the formulas entered I'll then cut and paste the entire
block of formula data to the other sheet. Excel will automatically add the
sheet name for you. I find this to be easier than starting on the other
sheet and having to switch back and forth between sheets when writing the
formulas.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Buffy M. Warren" wrote in message
...
Sheet 1 column A contains dates that reoccur, column B contains data of
interest to calculate averages and standard deviations, also Row 1
contains
header or column title information. Sheet 2, or what I call the Summary
sheet, repeats the dates of interest (only once) in Column A, also has
EXACT
same header list in Row 1. In cell B2 of Sheet 2 I want to average the
values from Sheet 1, with the same header (title) that match the date in
cell
A2.

"T. Valko" wrote:

You need to be more specific. Where is the data you want to average?

--
Biff
Microsoft Excel MVP


"Buffy M. Warren" <Buffy M. wrote in
message ...
I have a spreadsheet currently containing 2 sheets, the first sheet
contains
raw data and several lines of information are tied to the same date
which
is
listed in column A, the data I want to sumarize is listed in columnar
style
across the page. In the second sheet I want a summary by date,
averages
and
standard deviations. The second sheet also contains the date I'm
looking
to
match calculate. I know how to use the sumif and countif functions,
but I
think this needs a combination of MATCH, AVERAGE, STDEV and OFFSET. I
had
this working in another spreadsheet, but lost access to it in a move.

How to match date criteria then calculate an average of matche
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Buffy M. Warren" wrote in message
...
Thanks T. Valko - Absolutely fantastic, much more straight-forward than my
previous path, obviously it was too cumbersome for me to recreate. I
haven't
had much practice using arrays but this gets me off on the right foot!

"T. Valko" wrote:

See this screencap:

http://img103.imageshack.us/img103/369/avgat5.jpg

That shows the array formula** needed for the average. The array
formula**
for the stdev uses the same structu

=AVERAGE(IF($A$3:$A$8=$A12,B$3:B$8))
=STDEV(IF($A$3:$A$8=$A12,B$3:B$8))

I put everything on the same sheet to make it easier to follow. All you
need
to do is plug in the actual sheet name. Here's a tip that you might find
useful. Just like the screencap shows, I put everything on the same
sheet.
After I've got all the formulas entered I'll then cut and paste the
entire
block of formula data to the other sheet. Excel will automatically add
the
sheet name for you. I find this to be easier than starting on the other
sheet and having to switch back and forth between sheets when writing the
formulas.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Buffy M. Warren" wrote in
message
...
Sheet 1 column A contains dates that reoccur, column B contains data of
interest to calculate averages and standard deviations, also Row 1
contains
header or column title information. Sheet 2, or what I call the
Summary
sheet, repeats the dates of interest (only once) in Column A, also has
EXACT
same header list in Row 1. In cell B2 of Sheet 2 I want to average the
values from Sheet 1, with the same header (title) that match the date
in
cell
A2.

"T. Valko" wrote:

You need to be more specific. Where is the data you want to average?

--
Biff
Microsoft Excel MVP


"Buffy M. Warren" <Buffy M. wrote in
message ...
I have a spreadsheet currently containing 2 sheets, the first sheet
contains
raw data and several lines of information are tied to the same date
which
is
listed in column A, the data I want to sumarize is listed in
columnar
style
across the page. In the second sheet I want a summary by date,
averages
and
standard deviations. The second sheet also contains the date I'm
looking
to
match calculate. I know how to use the sumif and countif functions,
but I
think this needs a combination of MATCH, AVERAGE, STDEV and OFFSET.
I
had
this working in another spreadsheet, but lost access to it in a
move.