# Presentation: 071709

## Statistical Techniques Using Excel

- Preston McDowney
- Bangladesh Ministry of Energy and Mineral Resources & Petrobangla
- June 2001

## Outline

- Introduction to Microsoft Excel
- Demonstration of Statistical functions in Excel
- Demonstration of Data Summarization and Manipulation Tools
- Review and Conclusions

## What is Excel?

- Microsoft Excel is an electronic *spreadsheet* program
- Spreadsheets are computer programs that contain a series of *rows and columns*
- The intersection of a row and a column is a *cell*
- A cell can contain a number, text, or *formula*
- A formula is a user defined mathematical expression

## From the Beginning

- When you first open Excel you are in a blank *Workbook*
- A Workbook consists of a series of *worksheets*
- A Worksheet is simply a collection of rows and columns
- Excel allows a maximum of 256 Worksheets, 16384 rows, and 256 columns

## Active Cell Indicator

- Sheet Tabs

- Vertical Scrollbar

- Formula Bar

- Name Box

- Horizontal Scrollbar

- Menu Bar

- Standard Toolbar

## The Blank Workbook

- At the top of the Workbook is the **Title Bar, **it identifies the active Workbook
- Bellow the Title Bar is the **Menu Bar**, it is the main menu in Excel.  Clicking on any of the words in the **Menu Bar** will open a drop-down menu of commands

## The Blank Workbook (Cont.)

**Standard Toolbar**-contains shortcut keys to commonly used Excel functions
**Name Box-** indicates what cell is the active cell
**Formula Bar**
- allows data entry and editing (text or formula)
- displays any data that is entered (text or formula)

## The Blank Workbook (Cont.)

**Sheet Tabs**-indicates what worksheet is the active worksheet
**Vertical Scrollbar**- allows you to scroll up and down within a worksheet
**Horizontal Scrollbar**-allows you to scroll left and right within a worksheet

## Getting Started

- Text and Constants
- Click on the cell where you want text to appear
- Text will appear left justified
- Constants will appear right justified

-  Entering Data

## Getting Started (Cont.)

- Entering Equations
- All equations must begin with an equals sign “=“
- You can type an equation using operators (+,-,*,/, etc)
- Press the Enter or Return key, or the Green Check to enter the equation

## Getting Started

- What if we wanted to make either a pie chart or bar graph of this data?
- Excel has a built in Chart Wizard to help guide you through the graph making process

## Using Chart Wizard

- To use the **Chart Wizard** you can choose **Chart** from the **Insert** menu **or **
- You can click on the **Chart  Wizard** button (      ) located on the **Standard toolbar**

## Using Chart Wizard

- Select what Chart type you want to use
- Let’s try a Column Chart
- Click Next

## Using Chart Wizard

- Select what data you want to graph and **Highlight** it
- Push the (   )
- Click on the cells you want to select while either holding the **shift key** or **left mouse** buttons down
- Click Next

## Using Chart Wizard

- The Highlighted data will have a special box around it to show what is selected

- The selected cells will appear in the Source Data-Data Range Box
- Click the (     ) button or push the Enter Key to enter the selected cells

## Using Chart Wizard

## Using Chart Wizard (Cont.)

**Chart Options** allow you to add titles, format axes, gridlines, and legend, and add either Data Label or a Data table

## Using Chart Wizard

- Chart locations allows you to either make the chart in a new Sheet, or make it in the current sheet

## Getting Started (Cont.)

- That first example had only three data points
- If there were hundreds or thousands of data points in a column, typing or picking each point just to get a sum would be long and tedious
- Lucky for us, Excel has built in functions

## Getting Started (Cont.)

- What are Functions?
- Functions are built in equations and formulas in Excel that you can use
- Excel is programmed to accept parameters for each function in a certain format
- To use the function you have to either know how to enter the parameters or you can use the **Function Wizard**

## Getting Started (Cont)

- Like the **Chart Wizard,** Excel has a **Function Wizard** to assist you with using the functions
- Each function was a **Dialog Box** that will prompt you for the information Excel needs to calculate that function
- If you look in the **Formula Bar**, it will show you how to enter the equation without using the **Wizard**

## Using Function Wizard

- To use the **Function Wizard** you can choose **Function** from the **Insert** menu **or **
- You can click on the **Function Wizard** button (      ) located on the **Standard toolbar**

## Paste Function Dialog Box

- Either method will take you to the **Paste Function** Dialog Box
- The Left Column lists the categories of functions available
- The Right Column lists the functions in the various categories

## Statistical Function Category

- Excel has over 50 built in statistical functions
- Today we will demonstrate some of the more widely used functions

## Commonly Used Statistical Function Types

- Mean Functions
- Deviation Functions
- Correlation Functions

## Mean Functions

- Mean functions are measures of central tendency in a data set
- Excel has 6 built in Mean Functions
- Most of these functions are calculated in the exact same ways

## Mean Functions (Cont.)

- Average (arithmetic mean = total sum/n)
- Median (middle value)

## Data Set For Mean Functions

## Mean Demonstration

- Start with the Function Wizard
- Select **Statistical **from the **Function Category**
- Select **Average** from the **Function Name**

- Suppose we wanted to know the average growth rates for natural gas production and consumption

## Average Function Dialog Box

- Number 1- Cell range you want the average of
- We want to select the **Gross Production Growth Rate **column
- Number 2- Can be used if you want to take the average of 2 or more ranges _together_
- We want the average of each column separately

## The formula being used

- The value of the formula

## Mean Demonstration (Cont.)

- Let’s look at the equation in the Formula Bar “=AVERAGE(D3:D14)”
- All functions must start with the “=“
- After the “=“ is the function’s name
- After the name are the ()’s with the  function’s parameters

## Mean Demonstration (Cont.)

- Average Function Parameters
- Recall the AVERAGE Function Dialog Box asked us for a cell range
- To enter a cell range either highlight the cells or type them
- Cell ranges have the form (top left cell:bottom right cell)

## Mean Function (Cont.)

- Now we want to find the average of the next column
- We can use AutoFill
- We can use Function Wizard
- We can type in the equation

## Mean Demonstration (Cont.)

- Use **AutoFill** to copy the Average Function form the **Production Growth Rate** column
- Click on small black square in the lower right corner of the cell
- Drag the box into the cell you want to to find the average

## Selecting the Median Function

- Select **Statistical **from the **Function Category**
- Select **Average** from the **Function Name**

- Let’s find the Median Power Generation Growth Rate

## Median Dialog Box

- The Median Dialog Box works exactly the same as the Average Dialog Box
- Lets try to type the formula in by hand without using the Function Wizard

## Median Demonstration (Cont.)

- Start by entering the equal sign “=“
**=**
- Type in the function name
- =median
- Either highlight or type in the cell range
- =median(E3:E14)

## Median Demonstration (Cont.)

## Deviation and Variance Functions (D&V)

- Deviation and Variance Functions measure how well the data is distributed around the mean
- Excel has 5 built in functions
- We are going to focus on:
**VAR**-Variance
**STDEV**-Standard Deviation

## Deviation and Variance Functions (Cont.)

** ****Variance **- summation of the squared difference between each observation (xi) and the average of all the data points (x), divided by the total number of observations
**Standard Deviation -** square root of **Variance**

## Data Set  for D&V Functions

## Standard Deviation Demonstration 

- Suppose we wanted to know the standard deviation of the annual increase in generation

- Start with the Function Wizard
- Select **Statistical **from the **Function Category**
- Select **STDEV** from the **Function Name**

## Standard Deviation Dialog Box

- Number 1- Range of cells you want to find the standard deviation of
**Annual Increase Total Generation** in our example
- Number 2- If you wanted to combine two or more ranges together and then take the standard deviation

## Correlation Functions

- Correlation measures the strength of the relationship between two variables
- The larger the absolute value of the correlation coefficient, the stronger the correlation
- The correlation coefficient is a number that ranges between –1 and 1
- Positive coefficients signify that the two variables move in the same direction, negative signify that the variables move in opposite directions

## Correlation Data Set

## Correlation Demonstration

- Suppose we wanted to know how well population and consumption vary together

- Start with the Function Wizard
- Select **Statistical **from the **Function Category**
- Select **CORREL** from the **Function Name**

## Correlation Dialog Box

- Array1-The first cell range we want to use in the comparison
- Array 2-The second second cell range
- It is does not matter what order we use

## Slide 55

## Data Summarization and Manipulation Tool

- Sorting and Filtering
- Count and Logic Functions

## Sorting and Filtering

- Simple Sorting
- Arrange the data in the column either in ascending or descending order
- Filtering
- Highlights data you want to focus on while temporarily hiding the rest of the data you do not want displayed
- Count, Sum, Logic functions
- Manipulates data according to user defined conditions

## Sorting and Filtering Functions 

- Sorting and Filtering functions can be used for:
- Checking for outliers
- Checking for blank spaces
- Isolating data
- Filtering functions have the additional benefit of allowing you to edit, format, chart, and print your list subset without rearranging or moving it

## Sort Demonstration Data Set

## Simple Sort Demonstration	

- Let’s  rearrange the table so that we can easily compare the different Projects by **Fuel Type**

- Suppose we wanted to be able to identify the highest **Capacity** project for each **Fuel Type** and see if there are differences across **States  **

## Simple Sort Demonstration

- Select **Fuel** in the **Sort by** Box
- Select **Capacity** in the **Then by** Box
- Descending order
- Select **State **in the last **Then by** Box

- Select **Sort**  in the **Data** Menu on the Menu Bar

## Simple Filter Demonstration 

- Installing a Filter
- Select **Filter** from the **Data** Menu
- Select **AutoFilter** from the **Filter** Menu
- Removing a Filter
- Re-select **Filter** from the **Data** Menu

## Simple Filter Demonstration (Cont.)

- Click the **Black Triangle** in the **Fuel** Column Heading
- Select Gas
- Temporally hide everything except gas

## Simple Filter Demonstration (Cont.)

## Counting, Summing and Logic Functions

- Counting functions will return the number of entries that match a user defined specification
- Summing functions will total numerical entries within any column that match a user defined specification
- Logic functions allow the user to specify data to be counted or totaled
- Logic Commands:=,<,>,<=,>+

## COUNTIF Data Set

## COUNTIF Demonstration

- Suppose we wanted to know how many Projects were 1000 MW or larger

- Start with the Function Wizard
- Select **Statistical **from the **Function Category**
- Select **COUNTIF **from the **Function Name**

## COUNTIF Function Dialog Box

- Range - Range of cells you want to count the entries from
- Capacity Column in our example
- Criteria - Logic statement that defines which entries will be counted
- “>= 1000”

## SUMIF Demonstration

- Create a small table with two columns
- First column just text entry the fuel types
- Second column will use the SUMIF function to calculate the generation capacity for each fuel type

- Suppose we wanted to know the total generation **Capacity** by **Fuel Type**

## SUMIF Demonstration 

- Select **SUMIF** from the _***Math & Trig***__* *__***Category***_ of the **Paste Function** Dialog Box

## SUMIF Function Dialog Box

- Range -Columns you want the criteria to evaluate
**Fuel Column** for our example
- Criteria - Logic statement
**“****Coal”** for the first column
- Sum_range - What cells to add if the criteria is met
**Capacity Column **for our example

## SUMIF Demonstration (Cont)

- Repeat the process for each fuel type in the table
- Change the Fuel type in the **Criteria Box**

- Absolute Reference

## Review 

- Today we have looked at
- What is Excel
- How to enter data
- How to make graphs
- How to enter functions
- How to summarize and manipulate data