Solved

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

Posted on 2013-11-13
4
386 Views
Last Modified: 2013-11-13
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
Comment
Question by:morinia
  • 2
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
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

by:morinia
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

by:barry houdini
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

by:morinia
ID: 39646500
Awesome.  Thanks.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Win a Jar of Candy Corn: A Scientific Approach! I love mathematics. If you love mathematics also, you may enjoy this tip on how to use math to win your own jar of candy corn and to impress your friends. As I said, I love math, but I gu…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question