MS&E 121
Introduction to Stochastic Modeling
January 3, 2005
EXCEL TUTORIAL
This tutorial will introduce you to some essential features of Excel that we will be using throughout
MS&E 121 to solve linear systems of equations, analyzing data, and simulating random variables. You
will learn how to
• use some of the functions commonly used in math/statistics,
• represent a linear system of equations in an Excel worksheet,
• use the matrix functions MMULT and MINVERSE to solve linear systems of equations, and
• generate various types of random variables.
For additional Excel assistance, try consulting the Excel Help files or numerous online resources.
Parts of this tutorial borrow from Albert Whangbo’s Excel Solver Tutorial.
1 Commonly used Excel functions
In many probability applications we will commonly need to analyze data by computing quantities such
as mean, variance, standard deviation, correlation, confidence intervals, etc., and Excel provides us with
easy to use functions to do such things. All the functions can be accessed through the insert function
button, fx, or directly by typing them on the formula bar.
Naming Vectors and Matrices in Excel
Excel allows you to name ranges of cells in the worksheet. This feature is especially convenient for doing
matrix calculations and for setting up linear systems of equations. To name a range of cells, select the
entire range with the mouse and use the Insert → Name → Define... menu. Alternatively, select the
range and enter its name in the Name Box adjacent to the Formula Bar.
General use functions
One of the most useful functions is SUM(range), which sums the numbers in an array of cells. This
array can either be a vector (vertical or horizontal) or a matrix. This function can also be used by
highlighting first the desired vector of numbers and then pressing the Σ button, in which case the
answer is automatically placed at the end of the vector.
Two other useful functions are SUMPRODUCT(range1, range2) and SUMSQ(range). The arguments of
SUM, SUMPRODUCT, and SUMSQ can