Tracker
HOME    TRACKER    Excel

# Formula For Three Sigma Rule Of Standard Deviation

## I want to find the correct formula to find answers using excel for the folliowing: 1. >3Sds 2. 3SDs<>2SDs 3. 2SDs<>1SD 4. <1SDs This question uses 3 sigma rule (see attached worksheet) and the formula needs to be re-arranged to meet new criteria. So, we need to consider both sides of nornmal distributiion (ND) curve. eg. when we say >3SD it means it should count all valuee falling in the rage "Mean(Average)+3SD AND Mean-3SD" i.e two extreme ends of the ND cureve. I have inserted a normal distribution diagram and colour coded the ranges so that it will be easy to identify. Please refer comments inserted in K7to N7, cells that contain formula that need to be reset.

Related Forum Messages:
Standard Deviation Formula That Ignores 0 Values
I am trying to come up with a STDEVA formula that will only count values greater than 0 in a data set. Is this possible?

Graphing Standard Deviation
I was wondering what the best way to create a standard deviation graph was? I have a single row of numbers and need to make a stadard deviation graph.

I read somewhere that the analysis toolpak would be useful for this but having installed it i'm not really sure what to do next.

Standard Deviation Of Database
i'd like to calculate the standard deviation of different items all in the same database

If this would be the database:
a 1
a 2
b 9
a 6
b 5
b 3
c 2
c 5
a 7

Than this would be the result:
item a 2.55
item b 2.49
item c 1.50

I succeeded in doing this via a pivot table but i want to create a calculation as the result should be part of other data in another sheet.

Moving Standard Deviation
I am trying to learn VBA and started reading Wallenbach's book and I thought the best way to get up to speed fast was to work on some real problems.

So this is what I am trying to do.

I have a list of about 1000 stock price returns in the following format

jan 1 2008 .033
jan 2 2008 .01
jan 3 2008 -.002
jan 4 2008 -.0033
jan 5 2008 .04

What I want to do is calculate in VBA a moving 3 day standard deviation of returns. So I want the first calculation to include jan 1 - jan 3 and place the answer in the cell to the right of jan 3 return. Then move down 1 row and calculate the standard deviation using jan 2 - jan4 and place the answer in the cell to the right of jan 4 return...and so on until the last cell.

Standard Deviation W/O Max+Min
Is there anyway to do a standard dev w/o the max + min in a range of numbers?

Find 1 Standard Deviation From The Largest Value
I don't know too much about standard deviation, but I was wondering if somebody could help me with a function to find 1 standard deviation (68%??) from the largest value (not the mean value) within a column of values. What i'm really after is the 'standard deviation boundaries' either side (i.e. 34% of values above the largest value and 34% below the lowest value) as a highlight.

im using excel mac 2008 so no vba im afraid.

here is an example of real data: ...

Create Standard Deviation From Array
I am fairly new to VBA, i require some help creating a Standard Deviation UDF using arrays, i have made a userform that allows a user to select anywhere between 1 to 30 companies (A1: AD1) and its corresponding stockprices (A1:A60) in a sheet called companydata, using the following sub, my problem is that i am unsure how to create a function using the info the user selects. I have also created a returns function (below) but this is vba is saying that there is a out of range error, Will anyone be able to help please.

Below is the code i am using to allow for my sub: ....

Standard Deviation :: Way To Do A Stddevif Of A Sort?
Is there a way to do a stddevif of a sort? I have a flat file with thousands of records and based on criteria I group all records into specific buckets. I need to, without sorting, find the stddev of each group. Can this be done and if so does it require an array function (hopefully not). I dont mind having 3-4 additional columns of arguments to help me compute this (i.e countif, sumif, average, (Variance-average)^2, etc.).

Calculate The Mean, Median, And Standard Deviation
a column represents quantities and their measurement frequency in another column.

Using excel functions, how can I calculate the mean, median, and standard deviation

Calculate The Standard Deviation Of A Whole Population
I want to calculate the standard deviation of a whole population in my database (total population: 36,458)

I used the STDEV equation however, I went into help and it says that the equation calculates STDEV based on a sample of 30.

I don't want to calculate a standard deviation based on a sample. I want a standard deviation based on the whole population. Is it still okay to use the equation?

The reason I want to calculate the standard deviation is so that I can use that number to calculate the sample size needed for my population so I can create a histogram.

Standard Deviation Based On Criteria
I want to get the standard deviation based on criteria. It looks like this.
Criteria is 40 consecutive values ignoring 0 in Column A.
A B
1 50
2 20
3 10
0 20
4 10
5 20
....
....
nth

I use manual selection (e.g. =STDEV(B1:B3, B5:B6, etc...) that covers the 40
consecutive rows. B4 here is zero so I did not select it.

My rows is getting bigger and bigger, how could I formulate this?

Data With Fixed Mean And Standard Deviation
i need simulate data in 10 cells with a fixed standard deviation and a fixed mean

Example: Simulating the laptime for a runner where the mean should be 50 and the standard deviation 5,

I tried typing
=50+5*RAND()*(-1^(RANDBETWEEN(1;2)))

That gives me a mean close to 50 which is good but th standard deviation is no where near 5.

Standard Deviation And Nested If Statement
I have a sheet of data in which Column A contains a list of sales agents names and Column B contains their results against their KPIs. All the data is terribly skewed due to new metrics so I have been tasked with attributing 'ranking band' to their performance, i.e. very low, low, medium, high, very high.

I have calculated the average and standard deviation for the list of data, but now I need to calculate which ranges will fall under which ranking band, e.g. very low is less -18%, low is greater than 18% but less than 80%, etc.

I also need to come up with a nested if statement next to each of the sales agents so it returns what ranking brand they are on based on their performance. Sample sheet attached. EDIT - attachment on post #4.

Standard Deviation Of Dynamic Range..
I have a dynamic range that I need to be able to calculate the standard deviation for. The dynamic range sometimes has blank cells and #N/A cells which I do not want included in the calculation. However, there are sometimes cells with the number 0 in it (instead of blank) which I do want to include. I have come up with an array formula, but the standard deviation it calculates is not correct. The formula is:

=STDEV(IF(ISNUMBER(A3:A20000)*ISNUMBER(A3:A20000),1,0)). *This is an array formula calculated by pressing CTRL+SHIFT+ ENTER. The number it calculates is 0.43, however, this is incorrect. If you just calculate the standard deviation of the actual data points in excel (cells A138:A5055 in this case) : STDEV(A138:A5055), it returns 0.17. This is the correct standard deviation.

Macro/UDF To Calculate Standard Deviation Of % Changes
I have a seris/column of numerical data for which I want to work out percentage differences and then calculate the standard deviation of the percentage differences for all values

For example in column A, I have

row 1 10
row 2 15
row 3 17
row 4 10

I want to work out % difference between row 1 and row 2 (which is 50%), row 2 and row 3 etc and the get the standard deviation of thoose % changes.

I know how to do this on the spreadsheet using formulas but I want to do it using a macro so that the macro returns the standard deviation of the percentage changes because I do not want to have any data showing on the spreadsheet other than the raw data.

kkknie kindly provided the following UDF to work out the the average of the percentage changes but is there code that will work out the standard deviation instead?

I thought maybe excel's built-in standard deviation function (stdev)could be incorporated somewhere into the code but I'm not sure how!

Function AvgPct(rngIn As Range)
Dim dblErrAccum As Double
Dim intErrCount As Long
Dim dblLastVal As Double
Dim r as range
dblErrAccum = 0
dblLastVal = 0
intErrCount = 0

For Each r In rngIn
intErrCount = intErrCount + 1
If intErrCount > 1 Then
'dblErrAccum = dblErrAccum + (r.Value - dblLastVal) / dblLastVal
dblErrAccum = dblErrAccum + Abs((r.Value - dblLastVal)) / dblLastVal
End If
dblLastVal = r.Value
Next

AvgPct = dblErrAccum / (intErrCount -1)

End Function

Average / Standard Deviation On Range Containing #N/A Errors
I'm currently trying to perform some statistical functions on a range of data but with little success.

I want to "AVERAGE" the range and also do a "STDEV" on the range G8:G107 but some of the cells contain #N/A formula errors. I must have the formula returning #N/A to the cells else my chart on the same range plots the empty cell as 0.

What formula can I use that will let me do the standard average and stdev functions on that range, but ignoring the #N/A errors as if they were simply empty cells?

Standard Deviation Based On Multiple Criteria
I have a sheet of over 28,000 rows. I'd like to figure standard deviation of certain cells in a column that meet a specific criteria.

Do I need an If statement, is there a function that will allow me to do this, or anything else?

MS Excel: Standard Deviation Conditional Calculation

I have a large database and would like to calculate standard deviation based on a condition "Yes" or "No".

Shown below is a small layout from my database: ....

Standard Deviation Of Range Based On Cell Value
What I have:

If A1 = "5" (#of wins)
B1 = "2" (#of losses)
Column C = "1" all the way down (represents a success)
Column D = "0' all the way down (represents a failure)

What I want:

A formula that will calculate the standard deviation of the range based on what is input in A1 and B1. So in this example:

=STDEV(C1:C5,D1:D2)

I'm just tired of manually specifying these ranges when the values in the cells hold the length of the range.

Average Standard Deviation Of Array Within Matrix
I have tried to find information on this subject, but it seems diifficult to find exactly what I am looking for. I have provided a test example of the operation that I would like to acheive. The first example (Method 1) works well enough but would require defining more detailed functions beyond the scope of average. This would probably be ok, but more importantly, I would like to source particular arrays in this manner for other tasks beyond averages and even standard deviation and am not quite sure how this can be done. In an attempt to describe the situation, I have provided a second example (Method 2). In this example, I am trying to take the average of the jth column array to create an array of averages. I am not sure if I am incorrectly setting up the matrix, if the functions don't perform this way in the matrix, or if this is simply not possible.

Sub test()
a = 5
b = 5
Redim X(1 To a, 1 To b) As Double
'Assumes there is a matrix of values [a x b] drawn from worksheet
For j = 1 To b
For i = 1 To a
X(i, j) = Cells(i, j)
Next
Next

'----METHOD 1---------------------------------------------
'I know this way works using average as an example, but...
Redim AverageX(1 To a, 1 To b) As Double.......................

Calculate Standard Deviation By Cell Color
ColorFunction is a great tool, but I would like to use it to calculate the Standard Deviation of color filled cells. Can the following code be modified to do this?

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell,vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

Standard Deviation Ignoring N/A With Max+Min Removed
So this forum has helped me in the past develop a standard deviation that removes max and min from the data set.

=STDEV(LARGE(E5:AB5,ROW(INDIRECT("2:"&COUNT(E5:AB5)-1))))

Would if be possible to adjust this formula to ignore N/A's?

Array Formulas For Geometric Standard Deviation And Sharpe
Since I got such fantastic help with the last quesiton let me try one that
I've been beating my head against the wall on for two weeks now.

I'm calculating CAGR (Compound Annual Growth Rate) of an investment using
the following formula:

=((GEOMEAN(IF(Work!\$A9:\$A236<B2,Work!\$DS\$9:\$DS\$236))^12-1)*100)

A Column: Dates
D Column: Monthly returns in multiplier format

I want to do the same thing as this with GSD using this basic formula --
that is, only do the returns based on the IF statement:

=(EXP(STDEV(LN(Work!B9:B236)))^SQRT(12)-1)*100

And Sharpe with this formula:

=(AVERAGE(B49:B276)/STDEV(B49:B276))*SQRT(12)

beginning to hurt!

Calculate The Standard Deviation Of The Difference Of Two Arrays In Vba
I'm trying to create a VBA piece to calculate the standard deviation of the difference of two arrays. I have two columns of data and i want to create a third column that is the first minus the second and them calculate the standard deviation of that. My problem is that i wanted to do all of this behind the scenes in VBA (didn't want to output the third column).

VBA Code Mean And Standard Deviation Of Values In Specified Cells
I m trying to compute the mean and standard deviation for the following output:

Private Sub RunMCSimButton_Click()
'Sub created so that once the macro button is pushed, it outputs a specified number
'of FTSE 100 values and the equivalent binary payoffs.

Dim Kt As Double
Dim q As Double
Dim r As Double
Dim IndexBetValue As Double
Dim SampleSz As Long

Dim IndexSim() As Double
Dim Cnt As Long

Dim Payoff() As Double

'All the relevant variables have been defined.

'Read the parameter values from the required cells.
With Me.Range("d4")
Kt = .Cells(1, 1).Value
q = .Cells(2, 1).Value
r = .Cells(7, 1).Value...........................................

As you can see from the code, by pressing the macro button a list of FTSE 100 values and binary payoffs are produced and outputted into the spreadsheet. What I need help on is writing a piece of code that, also on the click of the macro, will output the mean and standard deviation of these values in specified cells, e.g. C10 mean and C11 SD.

Calculate Monthly Standard Deviation From Daily Data
I've daily data of a stock indices returns and I would like to calculate the monthly standard deviation. Currently, I'm using the following worksheet functions: =STDEVP(C2:C20)*SQRT(COUNT(C2:C20))

However, the range changes from month to month, which makes the process of calculating the monthly standard deviation to be quite tedious if I've about 10 years worth of data. I assume I could somehow substitute the range with a dynamic range, but I'm struggling to come up with the correct formulation that would do that.

Array Formula - St Deviation
I have a list of 187 countries and I would like to find a way to do this with an array and not by hand. Attached here is an excel with a sample; I would like to compute the standard deviation of the variable for the period 1980-2008 for each country separately. Is there a way to do this without me having to select by hand the range corresponding to each country in the st dev formula?

Cumulative Sum (convert Into Terms Is SIGMA)
I have, for my formula, 2 constant values a and x, I then also have a variable, n. The formula I'm trying to convert into excel terms is
SIGMA (with respect from 1 upto n) ((1-x)^(1-n) * x)*a. Unfortunatly thats not drastically inteligable so i've attached a picture file that should explain it in more understandable terms. Now the problem im having is, sigma is a cummulative funtion, which in this example is relative to the variable n, firstly I don't know what formulas I would have to use in order to get a sigma/summination function, and secondly I am uncertain of how to link it to a variable range.

“Five-even” Rounding Rule

6.6 must round to this ==> 7
6.5 must round to this ==> 6
5.5 must round to this ==> 6
8.5 must round to this ==> 8
9.5 must round to this ==> 10
66 must round to this ==> 70
65 must round to this ==> 60
55 must round to this ==> 60
85 must round to this ==> 80
95 must round to this ==> 100

Cross-Validation Rule
Is there any way to create a cross-validation rule between 2 cells?

For example,

Cell A5 will have one of the following values (AA, BB, or AB) ....

Duplicate Rule Without Pasting
I am using excell 2007 and am having a few problems with conditional formatting.
I am trying to do the following.
i have 6 cells in 6 columns ( c2, d2, e2, f2, g2, h2 ) each with figures 17.14+
Now i am trying to add a formula so when ever i insert a number in the cell below e.g c3 i want the cell c3 to turn green or red depending on the value in the above cell (c2).
so in all:

c2 has the value 17.14
if i insert the value 17.14 or less in cell c3 i want the c3 cell to turn green.
if i insert the value 17.15 or more i want the cell to turn red.
now i know how to do this in the conditional formatting feature but i want to do this will all the cells refering the the cell above.
i also do not want to create 6 diffrent conditional rules.

Is there a formula i can use that can be pasted into each cell and the cell number edited?

IF Rule: Another Cell To State N/a
Depending on the text entered into one cell, I'd like another cell to state n/a. For example, if cell a2 shows the text of cash, then cell b2 would show n/a.

Create If Rule Of % Into Text Mark A B C
Want to make an if rule where a student gets overall 75% in a column, then in another column next to that that would automatically give him a B or a B+ you know what I mean?

Hear are the ratios

A+ - 90-100
A - 80-89
B+ - 75-79
B - 70-74
C+ - 65-69
C - 60-64
D+ - 55-59
D - 50-54
E+ - 45-49
E - 40-44

and another one

Excellent - 80-100
Very Good - 70-79
Good - 60-69
Moderate - 50-59
Borderline - 40-49

Multiple Criteria Per Conditional Formatting Rule
Is there a way color a cell red if the the text is "Z" or a number between 0.5 and 10

Or if the cell contains a "Z" or a "C" ....

Multiple Criteria Per Conditional Formatting Rule..
how to adapt the solution so it would work in multiple cells. Since I was told to start a new thread, here it is. I want to format a cell based on its contents, If it has one of three entries, it should be greyed. I then want to use the conditional format across the whole page, but the solution given in the other thread refers to a single cell, how can I do this without that reference?

Rule To Delete Data From Adjacent Cells
For the first grouping of data (Ex 1) I need to do the following; if the value in column J is less than 49 then delete the data in the corresponding row from column E to K. For the second grouping (Flx 1) if the value in column R is less than 49 then delete the data in the corresponding row from column M to S. I would like to be able to do this for all groups all the way to group Flx 5. All my sheets are set out in this way but I am not sure if I can do this using a formula or a macro (which I am not very experienced at writing).

Before Close Event (delete An Auto Correct Rule)
I'm working on a time sheet, and to make it easier to enter time i created a macro that sets the auto correct to correct a "." to a ":", but when the workbook closes i want to run a macro that deletes the auto correct rule.

Apply Custom Validation Rule To A Drop-down Field
Is there a way to apply a custom validation rule to a drop down field in Excel (which itself is already using a validation rule)? Here is what I wish to do: I have a range of cells that can only pick up values from a list. I want to further apply a custom validation rule to these cells - display an error message when one chooses an element from the list that has already been used by another cell in the range.

If Cell Is Empty Create Rule To Display The Current Date
Im trying to find a formula that when a cell is empty ie this cell is linked to another cell on another worksheet and info has not been entered or is 0 then i want the cell to display the the current date ie =NOW()

Check 10 Different Text Values And Return False For All Of Those In The Conditional Formatting Rule
Allows me to check like 10 different text values and return false for all of those in the conditional formatting rule. When those are not present, the conditional format is true thus applying the format.

I tried
=NOT(FIND("text1",\$A\$1,1))
It did not work because I believe this statement doesnt give the rule its TRUE value its looking for.

I am having trouble with this, to clarify I need the below...
Cell has conditional formatting checking for text1 text2 text3...text 10ish and if they are present nothing will happen and if they arent present then format the cell.

EDIT: to clarify, the code should look for either text1 or text2 or text3 so on...

Deviation From 0?
I'm looking for the proper way of determining the success of a forecast over time.

Forecast ranges from -25% to +25%.

I don't want an average, as it would return a 0% deviation over 2 periods.
I don't want a standard deviation as it would be deviation from an eventual mean greater than 0.

I want the result of the above to say that my average deviation is 25 basis points. Meaning I'm either high or low by 25 points or that I'm "usually" off by +/- X%

Calculating AVERAGE DEVIATION From A SPECIFIED POINT?
I have a set of % score values (e.g. 88%, 94%, 82%, 67%)

I would like to know if there is a relatively simple formula that can be used to calculate the AVERAGE DEVIATION from 100%, as opposed to the AVERAGE DEVIATION from the mean, which is what the excel function =AVEDEV will return.

E.g. The Average Deviation from the mean for the the above set of values is 8.25%, whereas the Average Deviation from 100% is 17.25%

I can create a complex manual formula which sums absolute values of the variation from 100% of each score, and then divides by the count total of the number of scores, however this is flawed for several reasons, including the fact it is very time consuming when used across a large set of scores, and it is corrupted when a non score exists in a cell (some of the 'scores' may be '-', in which case they should be ignored and not included in the calculation of the average deviation from 100% by treating as a 0% score (and a deviation of 100% from 100%).

Another Sheet To Show Each Class Down The Rows And The Columns To Show Each School Rule
I have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.

I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).

Multiple 'if-thens' (Chain Rule) With Two Sheets
I have attached a sample file with two sheets.

Sheet 1 has two columns, 'A' represents a parcel number, and 'B' represents an indicator for the plat name in which the parcel number is located.

Sheet 2 also has two columns. 'A' represents the same indicator as Sheet 1 Column 'B' and Column 'B' is the formal name for the plat.

What I hope to achieve is Sheet 1 Column 'A' to be the parcel number (as it currently is)and Column 'B' to be the formal name for the plat (instead of the number).

As you will notice, the plat numbers on Sheet 1 Column 'B' can repeat many times. A plat number of '0' would mean it is not associated with a plat and therefore should return a blank.

The 'real' file has over 275,000 parcel numbers. All total, there are approximately 8800 differnent plat names.

Due to my 'lacking' skill level, I need it to be a macro as I have yet to learn other methods.

Standard Error Of Mean
how can i calculate standart error of mean of a sample?

Hiding Standard Toolbar
I use Excel 2000.

I want to hide all toolbars when I work.

I know how to hide them during a session, but when I exit and reboot Excel, the "standard" toolbar always returns.

Is there some way to hide the "standard" toolbar permanently?

Converting Non Standard Dates
I'm a moderate user of excel with some experience but need help creating a specific if statment.

I have a column "A" that shows dates formated like this: 060115 (January 15, 2006). Because excel does not recognize this as a valid numerical date I need column B to show what year the date falls under. To complicate it I need it to reflect a seasonal year from April 1 YYYY, to March 31, YYYY.

So here's an example. Say I have a date in column "A" of 050612 (June 12 2005). I need column "B" to look at that date and determine if it's >040105 and <033106 and return a result of "2005". I then need the formula to calculate an answer for up to 5 years worth of returns.

how to set up this type of =IF statement?

Create Standard Workbook
I am fairly new in Macros, I have a workbook wich will have some data for each year month, the new month ( sheet) data will be added by a macro, copying the current sheet (month) as a base. When all months are created I need to create new workbook with only Jan (January) sheet. I created the following macro, but I think it could be reduced, in code lines, and need to how to create the new workbook based in last (December) sheet.

Dim wSheet As Worksheet
SheetName = ActiveSheet. Name
Select Case SheetName
Case "Jan"
On Error Resume Next
Set wSheet = Worksheets("Feb")
If wSheet Is Nothing Then