13Yexcel

= Statistics in Excel - Univariate Data =


 * Note: **
 * everything on this page can be done in any spreadsheet
 * the comments and screenshots are specific to Microsoft Excel
 * you may have to search for the equivalent functions in your spreadsheet software


 * Arithmetic in Excel **


 * Excel can perform basic arithmetic
 * Type "=" followed by the sum you want to evaluate
 * For multiply, use *
 * For divide, use {forward slash}
 * To raise to a power, use ^ .. ( .. = 5^2 .. would give you 25)
 * Don't forget that BODMAS applies .. (use brackets)


 * Example 1 **

math \text{Evaluate } \dfrac{3 \times 4 + 7}{4} math


 * Solution:**

... ... Type: ... =(3*4+7)/4

In arithmetic, any value can be replaced with a cell location.
 * Note: **
 * a cell location is specified by the letter of the column followed by the number of the row
 * In the screen shot to the right
 * the formula is shown in cell A3
 * the result is shown in cell B3
 * Any change to the contents of the target cell results in an immediate recalculation of the result


 * Example 2 **


 * Enter any two values into cells A3 and B3
 * Now into another cell, type the equation: ... = A3*B3
 * (you should see the product of the two numbers you entered)
 * Now try changing the contents of A3 or B3
 * (you should see the product immediately change)


 * Ranges in Excel **


 * Many formulas in Excel rely on specifying a range of values
 * A range looks like ... ** B2:C4 ** ... where
 * the first entry (** B2 **) refers to the top left corner of the set of cells
 * the second entry (** C4 **) refers to the bottom right corner of the set of cells
 * there is a colon (** : **) between the two entries
 * A range of cells can cover
 * part of a single row ... (** A3:D3 **)
 * part of a single column ... (** B4:B7 **)
 * a rectangular area ... (** B4:D7 **)
 * To enter a cell range, you can
 * type in the appropriate entries ... (or)
 * click on the top left cell and drag down to the bottom right cell to highlight the desired range


 * Example 3 **

In the screenshot shown to the right, list the values in the range
 * 1) .. A3:D3
 * 2) .. B3:B6
 * 3) .. C3:D4


 * Solution:**
 * 1) . A3:D3 ... 3, 9, 2, 12
 * 2) . B3:B6 ... 9, 7, 0, 2
 * 3) . C3:D4 ... 2, 12, 10, 8


 * Statistics in Excel **

In Microsoft Excel, enter a formula by typing "=" followed by the name of the formula
 * Excel will guess at the formula you are typing and put a list -- you can always select the right one from the list
 * All of these formulas (and many more) are available from the FORMULAS menu

To find the __**sum**__ of a set of values ... ... =SUM(range)

To find the __**number of values**__ in a range (counts only those cells containing numbers) ... ... =COUNT(range)

To find the __**mean**__ of a set of values ... ... =AVERAGE(range)

To find the __**median**__ of a set of values ... ... =MEDIAN(range)

To find the __**mode**__ of a set of values ... ... =MODE(range)

To find the **__smallest value__** (minimum) ... ... =MIN(range)

To find the __**largest value**__ (maximum) ... ... =MAX(range)

To find Q 1 - the __**first quartile**__ ... ... =QUARTILE(range, 1)

To find Q3 - the **__third quartile__** ... ... =QUARTILE(range, 3)

To find IQR - the __**Interquartile Range**__ ... ... =QUARTILE(range, 3) - QUARTILE(range, 1)

To find the __**Standard Deviation**__ ... ... =STDEV.P(range)

To find an estimate of the standard deviation of a population based on a sample ... ... =STDEV.S(range)


 * Example 4 **

Here is a screen shot demonstrating each of these formulas

... ... ... = G11 – G10
 * The IQR (Interquartile range) could also have been calculated using:


 * Note **
 * The number of decimal places displayed can be controlled by clicking on the buttons highlighted here:
 * when you copy (CTRL-C) and paste (CTRL-V) a formula from one cell to another,
 * the range will automatically be changed to be same area relative to the new position


 * The Fill Down (CTRL-D) and Fill Right (CTRL-R) functions also do this
 * To make use of these functions, type the equation once
 * Then highlight it and the cells below or to the right of the original cell
 * Then type either CTRL-D or CTRL-R
 * The formula will be copied into all the highlighted cells with the range changed in each formula


 * Example 5 **

In a spreadsheet like that shown below, In cell G3 ... type ... = AVERAGE(A3:D3)
 * this will give you the mean of the 4 values in the first row of the table

Then highlight from G3 down to G11 and press ... CTRL-D .. (fill down)
 * Notice how the range in each formula has been adjusted automatically

.