Relationship Math Across a Row

[illston]

Greetings,

I have a dataset of year/month/rainfall data that I am doing some simple statistics (average, std dev, etc.) upon. I have what I hope is a simple question. How can I scan column B (month), match a certain month, and do a certain stastic upon all of the rainfall for that month....for example:

1970 01 1.35
1970 02 3.45
1970 03 5.33
1971 01 0.99
1971 02 4.30
1971 03 5.12
1972 01 0.21
1972 02 2.22
1972 03 7.08

I want one cell to give the average rainfall for March, or the Std Dev for March, etc. I would like to set up a template of forumulas so that I can just drop any data (each state county) and have teh statistics at the top. So, I assume a B:B will be involved to handle varying data lengths (or at least a b10:b1000, for a buffer).

I looked at SUM_PRODUCT, but it doesn't really do what I need, plus I am doing more than just a sum.

Any ideas before I start writing a perl script to do this?

Thanks,
Brad

[Cutter]

If you used SUMPRODUCT to get the total amount for the month and then divide it by a count of the times that month occurs in your list you'd get the average. Use =COUNTIF(B10:B1000,01) to get the number of Januarys.

[illston]

What about other statistics like Standard Deviation? or Variance?

Thanks for the help,
Brad

[MrShorty]

How about using an advanced filter on the list. You could tell the filter to extract all the month 3's to a different worksheet, then perform your statistical calculations on the data on that sheet. Not quite as automatic as a single function, because you would have to call the filter command each time you wanted to look at a different month. Or do it all ahead of time and filter the list several times until you have each month on it's own spreadsheet.

[MrShorty]

If you don't like the idea of filtering, how about this solution. I'll use the data you list above in columns A, B, and C starting in Row 11 (so I can output the statistics of interest at the top of the sheet).

In A1 enter number for month of interest eg 1.
In A2 enter formula =sumif(B11:B19,$A$2,C11:C19)/countif(B11:B19,$A$2). this is the average for the month requested in A1.
In D11 enter formula =(B11-$A$2)^2 and copy down. this will be used for calculating variance.
In A3 enter formula =sumif(B11:b19,$A$1,d11:d19)/(countif(b11:b19,$A$1)-1). This is the variance s^2. standard deviation is the square root of the variance sqrt(s^2).
MAX and MIN were easily obtained by adding another column E11=IF(b11=$A$1,C11,"") then using the MAX/MIN functions on that column. Others could help in obtaining the year the MAX/MIN occurred.

Does that help?