# Presentation: 437680

## Statistical Techniques Using Excel Part 2

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

## Outline

- Excel Refresher
- Introduction to Add-ins
- Introduction to  Analysis ToolPak Tool in Excel
- Introduction to Conditional Sum
- Introduction to Pivot Tables

## Add-ins

- Add-ins are features that increase the functionality of Excel
- Excel includes numerous Add-ins
- Analysis ToolPak
- Solver
- Conditional Sum
- You can create your own user-defined Add-in using Visual Basic

## Loading Add-ins 

- Select **Add-ins** from the **Tools Menu** on the Toolbar

## Available Add-ins

## Successfully Loaded Add-ins

## Analysis ToolPak

- The Analysis ToolPak contains functions and tools that increase the analytical capabilities of Excel
- 19 analytical tools
- 93 functions

## Available Analysis Tools

## Analysis ToolPak Cont.

- We will discuss:
- Descriptive Statistics
- Histograms
- Regression

## Descriptive Statistics

- The Descriptive Statistics tool will make a table displaying the Mean, Mode, Standard Deviation, Sample Variance, Kurtosis, Skewness, Range, Minimum, Maximum, Sum and Count for however many columns you want

## Descriptive Statistics Data Set

## Descriptive Statistics Demonstration

- Let’s calculate the **Electric Utilities **(diesel fuel)** **Column’s Skewness and Standard Deviation

- Select **Data Analysis**  in the **Tools Menu** on the **Menu Bar**

## Descriptive Statistics Demonstration (Cont.)

- Select **Descriptive Statistics** in the **Analysis Tools Box**

## Descriptive Statistics Dialog Box

**Input**
- Input Range- Highlight the Rows or Columns to summarize
- Click on “Labels in First Row” if you have column headings

## Descriptive Statistics Dialog Box (Cont.)

- Output Options
- Where do you want the output to be stored
- We are going to on Click New Worksheet
- What Statistics do you want
- Summary statistics

## Histograms

- Suppose we wanted to know how well the Project Capacities were distributed
- How many  100-200 Mw
- How many 500-600 Mw
- We can make a Histogram

## Histograms

- Histograms are special charts designed to show grouped frequency distribution
-  The horizontal axis of a histogram is divided into frequency intervals
- The vertical axis represents the number of data points within the intervals
- The size of resulting blocks visually shows the distribution of the data

## Histogram Demostration

- Select **Data Analysis** from the **Tools Menu **
- Select **Histogram **

## Histogram Dialog Box

- Inputs
**Input Range**-Cell Range you want the data to come from
**Bin Range**-Distribution Interval you want on the x-axis

## Histogram Demonstration

- Output Options
- Click on one of the circles to tell Excel were to output
- We are going to choose the Output Chart and Cumulative Percent Options
- The sorted histogram option sorts by frequency

## Slide 23

## Regression

- The regression tool performs regression analysis that will allow you to:
- Analyze trends
- Make forecasts
- Build predictive models
- Excel will perform simple and multiple linear regression
- no non-linear regression

## Regression Data Set

## Regression Demonstration

- Yesterday we saw that Population and Electricity Consumption were highly correlated
- Today we are going to run a regression analysis using the same data

## Regression Demonstration

- Select **Data Analysis** from the **Tools Menu **
- Select **Regression**

## Regression Dialog Box 

- Input
- Y-Range: Dependant Variable
- Electricity Consumption
- X-Range: Independent Variable
- Population

## Regression Dialog Box (Cont.) 

- Output Options
- Click on one of the circles to tell Excel were to output
- You can choose if and how you want Excel to show the residuals
- You can choose to see the Normal Probability Plot

## Regression Dialog Box (Cont.)

## Conditional Sum

- Conditional Sum gives us even more flexibility than the SUMMIF function
- With Condition Sum we can take totals using multiple criteria
- Excel has a Conditional Sum Wizard to help us

## Conditional Sum Data Set

## Conditional Sum Demonstration

- Suppose we wanted to know how much total generation capacity is available through projects larger than 400 MW and smaller than 600 MW
- Select **Wizards** from the **Tools** Menu
- Select **Conditional Sum**

## Conditional Sum Wizard Demonstration (Cont.)

- Select the data range that has both the data you want to tally from and data you want to the test the criteria with, including the labels

## Conditional Sum Dialog Box

- We want to sum **Capacity**
- We want to enter two conditions
- capacity <=400
- capacity >=600

## Conditional Sum Wizard (Cont.)

- If you choose to copy the conditional values, you can change those values in the spreadsheet without having go through the **Wizard** again

## Conditional Sum Wizard (Cont.)

- The next three Dialog Boxes will ask you where do you want to put   the two conditions and the value

## Conditional Sum Wizard (Cont.)

- Now suppose we wanted to know how much total generation capacity there is in only Gas projects larger than 400 MW and smaller than 600 MW

## Conditional Sum Wizard (Cont.)

- We can just add another condition
- Fuel=Gas

## Conditional Sum Wizard (Cont.)

- Go through selecting where you want to put the results

## Slide 43

## Slide 44

## Pivot Tables

- A Pivot table is a dynamic summary table generated from a list or database
- The user decides what information to display, how to display it, and at what level
- It can combine Sorting, Filtering, and Logic Statements in one table

## Starting PivotTable Wizard

- Select which source the data is coming from

- Choose **PivotTable Report** from the **Data **menu

## Selecting the Data Source

- Data in Excel spreadsheets or database
- Highlight the data
- External data source
- Two options
- Import the data first
- Use Wizard to import the data
- Multiple sources
- Highlight data using the Wizard
- Another Pivot Table
- Highlight the data

## Starting PivotTable Wizard (Cont.)

- Select the data you want to use or make sure the data highlighted is the correct data

## Slide 56

## Slide 71