Solved

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

Posted on 2013-11-13
4
384 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article seeks to propel the full implementation of geothermal power plants in Mexico as a renewable energy source.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now