Solved

# test to see if an number is greater than one standard deviation from mean in Excel

Posted on 2013-11-13
384 Views
Experts,

Does anyone know how  code a formaul in Excel test to see if a number is greater than one standard deviation from the mean?
0
Question by:morinia
• 2
• 2

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 39646274
Well, if you have a range of numbers like A1:A10 then you can get standard deviation of those numbers with

=STDEV(A\$1:A\$10)

......so if you want to test whether any specific number, like A1 is greater than one standard deviation from the mean use

=A1>AVERAGE(A\$1:A\$10)+STDEV(A\$1:A\$10)

that will give you TRUE or FALSE

See attached example - press F9 to generate new numbers

regards, barry

PS this version only gives you numbers at the top end - if you want to find any numbers that are more than 1 standard deviation away from the mean (either higher or lower) you can use this version

=ABS(A1-AVERAGE(A\$1:A\$10))>STDEV(A\$1:A\$10)
STDEV.xlsx
0

Author Comment

ID: 39646299
Barry,

This is great.  Is there any way to eliminate the high end and low end numbers when calculating the standard deviation?

I am able to do it when calculating the average by using this formula.

=(SUM(C29:G29)-MAX(C29:G29)-MIN(C29:G29))/(COUNT(C29:G29)-2)
0

LVL 50

Assisted Solution

barry houdini earned 500 total points
ID: 39646332
For a small range of numbers like that you could use this formula

=STDEV(SMALL(C29:G29,{2,3,4}))

or for any range

=STDEV(SMALL(range,ROW(INDIRECT("2:"&COUNT(range)-1))))

confirmed with CTRL+SHIFT+ENTER

Note: for the average without highest and lowest values you can simplify your version by using TRIMMEAN function, i.e.

=TRIMMEAN(C29:G29,2/COUNT(C29:G29))

regards, barry
0

Author Closing Comment

ID: 39646500
Awesome.  Thanks.
0