Welcome to the Numplexis standard deviation guide. The following contents will present how to conduct a basic statistic assessment of a data set by calculating it's standard deviation. For background the guide will detail the meaning of the calculation and how it can be used to predict future values of the data set under study. For simplicity the guide will assume you have foundational knowledge on how to use excel including opening a blank workbook, clicking and editing cells, and accessing the formula bar. If you are unfamiliar with these actions it is recommended to learn the basics at the Goodwill Community Foundation's Excel 2016 Basics videos.

The text box below contains a comma delimited population sample set this guild will use to demonstrate how to calculate it's standard deviation and corresponding normal distribution. Please **edit the text box** as you wish to observe how it effects the output results below.

Data Set

◢ | A | B | C | D |
---|---|---|---|---|

1 | Variable Name | Value | Units | Description |

2 | Adr_{start} |
=ADDRESS( ROW(G2), COLUMN(G2)) | text | Cell address of the first value in the input data set. |

3 | Adr_{end} |
=ADDRESS( ROW(G7),COLUMN(G7)) | text | Cell address of the last value in the input data set. |

4 | µ | =AVERAGE( INDIRECT( B2&":"&B3)) | number | Arithmetic mean of the input data set. |

5 | σ | =STDEV.S( INDIRECT( B2&":"&B3)) | number | Total population standard deviation. |

The button below is the **download link** to the excel version of the standard deviation and normal distribution calculator on this page. For a guide on how to create elements of the spreadsheet from a blank sheet, consult the sections below.

Download Statistics Excel Spreadsheet

**Adjust the input boxes** below to observe how changing the mean and standard deviation effects the position and shape of two normal distributions.

Distr_{1}
Mean, µ

Distr_{2}
Mean, µ

Distr_{1}
Std. Deviation, σ

Distr_{2}
Std. Deviation, σ

Let's cover some terms we use to create the statistics calculator.

Term | Definition |
---|---|

Mean | Also known as the arithmetic mean or average, the mean is the sum of a collection of values divided by the number of the values in the collection. |

Standard Deviation | The standard deviation is a measure that is used to quantify the extent of variation in a collection or population of data values. A low standard deviation indicates the data points tend to be close in value to the population mean. |

Probability Distribution | A mathematical function that links each outcome of a statistical experiment with it's probability of occurrence. You can think of a probability distribution as a tool for predicting future events given past observations. Probability distributions come in many different shapes and can be more or less suited to predicting a specific type of phenomenon partly based on how well it can conform to a given set of past observations. |

Normal Distribution | Also known as the Gaussian distribution, the normal distribution is a widely used continuous probability distribution that resembles that of a bell curve centered on the population mean it aims to statistically represent. It consists of a family of curves whereby an individual curve is defined by two parameters. The first parameter is the population's mean value and the second is the population's standard deviation. |

Listed below are the formulas this guide will use to calculate the mean, standard deviation, and normal distribution for a generalized data set or population.

Variable | Description |
---|---|

x | An individual value or outcome of the data set under study. |

n | Number of samples of the data set under study . |

µ | The arithmetic mean of the data set under study. |

σ | The standard deviation of the data set under study. |

e | Called Euler's number, it is a mathematical constant approximately equal to 2.71828. |

f_{norm} |
The normal distribution probability density function. |

All you will need to get started is an installation of Microsoft Excel. For reference Microsoft Excel 2016 was used for this guide, but any recent version of Excel will do.

If you are creating a spreadsheet to solve a problem, you may wish to share it with coworker or need it years later. To address these possibilities it is best to add descriptions and figures to the sheet so anyone using it will understand what the spreadsheet is accomplishing and how. The below steps follow documentation best practices that you may find beneficial with any spreadsheet application.

Follow the below instructions to create the portion of the excel sheet that solves for the data set standard deviation and best fit normal distribution.

- Click
**File -> New -> Blank Workbook**. - Adjust the width of column B to 10, column D to 30, column F to 15, and column G to 15 to better display input values.
- To adjust column width right click on the letter corresponding to the column you wish to change.
- Select
**Column Width**from the drop down menu. - Type the desired number into the input cell and click
**Ok**. **Copy table 4**just below the table tag and paste into the active spreadsheet with cell**F1**(1st row in the 6th column of the spreadsheet) selected.**Caution**: The formulas below may not function properly if the table is pasted into a cell other than F1.**Copy table 5**just below the table tag and paste into the active spreadsheet with cell**A1**(1st row in the 1st column of the spreadsheet) selected.**Caution**: The formulas below may not function properly if the table is pasted into a cell other than A1.- Adjust the format of the value cells as follows:
- Change format of arithmetic mean value (B4) to a number format
**Home Ribbon -> Number -> Drop Down Menu -> Number**. - Change format of standard deviation value (B5) to a number format
**Home Ribbon -> Number -> Drop Down Menu -> Number**. - Confirm resulting table resembles Table 6

Data Value Index | Data Set Values |
---|

Variable Name | Value | Units | Description |
---|---|---|---|

Adr_{start} |
=ADDRESS( ROW(G2), COLUMN(G2)) | text | Cell address of the first value in the input data set. |

Adr_{end} |
=ADDRESS( ROW(G7), COLUMN(G7)) | text | Cell address of the last value in the input data set. |

µ | =AVERAGE( INDIRECT( B2&":"&B3)) | number | Arithmetic mean of the input data set. |

σ | =STDEV.S( INDIRECT( B2&":"&B3)) | number | Sample standard deviation. |

To create the above figure within excel download this sample spreadsheet and paste your **Tables 4 and 5** in cells **F1** and **A1** respectively. Be sure to **adjust the axes** of the bar and scatter plot to ensure the resulting histogram and normal distribution are correctly positioned relative to one another.