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)
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:
Similar to this is to graph each point as a column. This gives us a bit more sense of how “big” each value is:
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:
There are a number of videos for using Excel for data and decision analysis. They are organized into small pages, and this page is a table of contents. If you don’t find what you need, let me know and I’ll either add a new video or make video easier to find. Thanks.
General Introduction to Excel
Descriptive Statistics using Excel
Simple Graphs using Excel
Named Ranges, INDEX(), and Simple Graphs using the INDEX() function
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:
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
This is a quick start for people new to Excel.
Here is a quick overview of the features of Excel, focusing on those we will use in the business school:
There are short-cut keys for navigating around Excel:
Referring to cells in Excel has some conventions that are useful to understand:
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:
JMP will easily import data from Excel:
Descriptive statistics with JMP:
JMP version of Excel pivot tables:
Create single regression models:
Multiple regression models, with categorical data and nonlinear relationships:
Nice resource if you are thinking that you would like to write programs for you computer/phone.
It turns out (according to this) that if you’re a millenial, things are awful for you.
Oh wait, it is awful to be old.
What are missing from these analyses that would help prove, or undermine, the argument put forward in the articles?