Relationship Math Across a Row
 

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


--
illston
------------------------------------------------------------------------
illston's Profile: http://www.excelforum.com/member.php...o&userid=23771
View this thread: http://www.excelforum.com/showthread...hreadid=374152

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.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=374152

What about other statistics like Standard Deviation? or Variance?

Thanks for the help,
Brad


--
illston
------------------------------------------------------------------------
illston's Profile: http://www.excelforum.com/member.php...o&userid=23771
View this thread: http://www.excelforum.com/showthread...hreadid=374152

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
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=374152

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?


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=374152