Mean & St. Dev of monthly data

  • Thread starter Thread starter Marty Leaf
  • Start date Start date
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
 
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
))
 
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?
 
Hi
are these values in the first columns text values or date values just
formates to show only the month?
 
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.
 
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)))
 
Should have added that this formula needs to be array-entered
(CTRL-SHIFT-ENTER or CMD-RETURN).
 
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
 
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)))
 
Back
Top