Excel INDEX and Named Ranges

Excel allows you to refer to cells as just B6 or C17:D26 (for example), but ranges can also be given meaningful names like Market_Share. Here is a video to introduce named ranges and then using INDEX to access cells within a named range:

Named Ranges and INDEX

Creating graphs of data with the use of INDEX is quite useful. Here are some examples:

Single Variable Scatterplots

Histograms (using the FREQUENCY function)

XY Scatterplots

Excel Graphs

There are many kinds of graphs in Excel, and this page looks at a few of the most common types of graphs.

The first is just to do a scatterplot of all the data points. This allows us to “see” the variability of the data:

Scatterplots

Similar to this is to graph each point as a column. This gives us a bit more sense of how “big” each value is:

Column Charts

Similar to a column chart (one column per data point) is a histogram. Instead of plotting each point, the graph shows the number of points in various ranges (for example, 1-10, 11-20, etc.). There is a built-in tool for creating histograms in the Data Analysis tool pack:

Histograms using the Data Analysis Tool Pack

Histograms can also be constructed using the Excel function FREQUENCY():

Histograms using the FREQUENCY() function

And the last graph type we will look at is an XY scatterplot:

XYScatterplots

Excel Statistics

We can use Excel to create descriptive statistics for a data set. Here are a few videos with some help on how to do some basic tasks.

The filter command is often a good place to start with a new dataset:

Using Filter

In the Analysis Toolpack, there is a way to quickly calculate the descriptive statistics for a dataset:

Analysis Toolpack Descriptive Statistics

Sometimes, the Analysis Toolpack is missing from the Data menu tab. If it is, here is the way to install it:

Install the Analysis Toolpack

Descriptive statistics can also be computed using the built-in functions Excel provides:

Basic Statistical Functions

SAS/JMP Quick Start Guide

This is a quick start guide for SAS/JMP, the one of the statistical software packages used at Goizueta. It is Excel-friendly and requires no programming. I have created some videos to help get you started, once you have the software installed on your computer.

Here is a quick overview and some helpful preference changes that will make JMP a bit easier to use:

01-Overview

JMP will easily import data from Excel:

02-Import Data

Descriptive statistics with JMP:

03-Descriptive Statistics

JMP version of Excel pivot tables:

04-Tabulate

Scatterplots:

05-Scatterplots

Create single regression models:

06-Single Regression

Multiple regression models, with categorical data and nonlinear relationships:

07-Multiple Regression