Standard Deviation removing Max & Min

Looking for a formula to pull the standard deviation of a series of numbers after removing the highest and lowest value from the series.

Thanks!

Array (committed Ctrl+Shift+Enter) formula:

=STDEV.S(IF((A1:A5=MAX(A1:A5))+(A1:A5=MIN(A1:A5))=0,A1:A5,""))

see attachement

Thank you very much! I do, however, receive a different answer when I manually remove the high and low numbers from the array using STDEV.S alone without Min and Max. Could you help explain this for me?

16 19 39 35 55 29 29 66(high) 24 35 57 21 14 62 7 (low) =STDEV.S(IF((A1:O1=MAX(A1:O1))+(A1:O1=MIN(A1:O1))=0,A1:O1,"")) = 17.7264

16 19 39 35 55 29 29 24 35 57 21 14 62 = STDEV.S(A1:M1) = 15.9301

Hi, you simply not used all of my answer (this part bold below IS important).

Originally Posted by Kaper

Array (committed Ctrl+Shift+Enter) formula

see again new attached file and note that if you position cursor in A7 in the formula line you will see {=...}

PS. Check if this number (here 15.93...) is what you expect - in excel you have several STD DEV functions like Sample std dev used above, but also STDEV.P for Population std dev, etc.

Thank you both. Both formulas work fine.

What I failed to include in my criteria is that each row of data has a minimum of one single cell left blank appearing randomly throughout which should be interpreted as a non-number/text ("") rather than zero. In other words there are sixteen columns in each row of data but only a maximum of fifteen instances of actual data to count in each row. There can be as many as 14 blank cells in each row which should not be counted.


I should tell you what I am using this for in my very important research project!.... This is to analyze fantasy football data on the 2013 NFL regular season to pull the standard deviations of things like number of receptions per game played, number of rush attempts per game, etc etc. The idea being with each relevant stat, I want to locate the top performers of the season who had the most consistent output from game to game rather than the players who had a few monster games mixed in with several duds who posted high season totals but had high volatility/std deviations.

I need to pull out the high and low games of the year to smooth the number and also treat games they didn't play in due to injury or the bye week as non-qualifiers for the formula.

Thanks so much!
John

Try this

=STDEV.S(IF((A1:O1<>MAX(A1:O1))*(A1:O1<>MIN(A1:O1)),A1:O1,""))