Mean & St. Dev of monthly data

M

Marty Leaf

I have an Excel Spreadsheet that list monthly values. I need to exclude the
highest & lowest monthly values then compute the mean(average) & the
standard deviation for only ten of the twelve months. Is there any easy way
to do this?

Thanks,
-Marty
 
F

Frank Kabel

Hi
try for exampl the following array formula (for January->October).
formula entered with CTRL+SHIFT+ENTER:
=MEAN(IF((MONTH(A1:A100)>=1)*(MONTH(A1:A100)<=10)*(A1:A100<>""),B1:B100
))
 
M

Marty Leaf

January 85
February 75
March 110
April 98
May 86
June 68
July 74
August 97
September 59
October 67
November 85
December 90


I need to get the mean & standard deviation for the above value without
March 110 & Sept 59 included. I am looking for an array formula to do this.
I actually have 48 different columns of monthly data. Any ideas?
 
F

Frank Kabel

Hi
are these values in the first columns text values or date values just
formates to show only the month?
 
M

Marty Leaf

Sorry, lost some formatting after the post was sent.

The months are in one column A1:A12 and the values are the next column
B1:B12. I want the mean to be calculated in the 13th row B13 ant the St Dev
in B14.
 
J

JE McGimpsey

Mean's easy:

=TRIMMEAN(rng,2/COUNT(rng))

Std Dev:

=STDEV(IF(ROW(rng)=MATCH(MAX(rng),rng,FALSE), "",
IF(ROW(rng)=MATCH(MIN(rng),rng,FALSE),"",rng)))
 
J

JE McGimpsey

Should have added that this formula needs to be array-entered
(CTRL-SHIFT-ENTER or CMD-RETURN).
 
M

Marty Leaf

TRIMMEAN works great! STDEV is very close. Problem is I have a header. The
ROW function is tricked by the header. How do I compensate for the header. I
tried ROW(rng)+1 that was an error.

Any Ideas?

-Marty
 
J

JE McGimpsey

This will work if the last row of the header is Row N (e.g, 1, in your
case), array-entered:

=STDEV(IF(ROW(rng)=(MATCH(MAX(rng),rng,FALSE)+N), "",
IF(ROW(rng)=(MATCH(MIN(rng),rng,FALSE)+N),"",rng)))
 
Top