How to match date criteria then calculate an average of matches

[Guest]

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.

 

[T. Valko]

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

 

[Guest]

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]

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 structure:

=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)

 

[Guest]

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]

You're welcome. Thanks for the feedback!