Ninguno

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 ofequations 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 probabilityapplications 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, f x, or directly by typing them on the formula bar.

Naming Vectors and Matrices in Excel
Excel allowsyou 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 ofthe 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 be either ranges of cells or range names. For a pair of ranges named S and T, entering SUMPRODUCT(S,T) into the Formula Bar returns sij tij ,
i,j

Excel Tutorial

1

MS&E 121 Introduction to Stochastic Modeling

January 3, 2005

i.e., the sum of the products of corresponding elements of S and T. Note that SUMPRODUCTwill return an error if the two arrays do not have the same dimensions. Entering SUMSQ(S) returns s2 , ij
i,j

i.e., the sum of the squared elements of S.

Statistical functions
The function AVERAGE(range) computes the arithmetic mean of the range of numbers, i.e., x= ¯ 1 n
n

xi .
i=1

The function VAR(range) computes the sample variance of the range of numbers, i.e., S2 = 1 n−1
n(xi − x)2 . ¯
i=1

And the function STDEV(range) the sample standard deviation, i.e., √ S = S2. To compute the correlation between two data sets, use the function CORREL(range1, range2). If X and Y are two vectors with elements xi and yi , respectively, then CORREL(X,Y) returns
n

(xi − x)(yi − y ) ¯ ¯
i=1 n n

. (yi − y )2 ¯
i=1

(xi − x)2 ¯
i=1

To close this section, it isimportant to mention that Excel counts with several cumulative distribution functions, CDFs, and inverse CDFs for some of the most commonly used probability distributions. The inverse CDFs are especially useful for computing confidence intervals, quantiles, and simulating random variables through the inverse transformation method.

Excel Tutorial

2

MS&E 121 Introduction to Stochastic ModelingJanuary 3, 2005

Prob. Distribution Beta Binomial∗ Chi Square Exponential∗ F Gamma∗ Hypergeometric Lognormal Negative Binomial Normal∗ Standard Normal Poisson∗ Student’s T Weibull∗

CDF BETADIST BINOMDIST CHIDIST EXPONDIST FDIST GAMMADIST HYPGEOMDIST LOGNORMDIST NEGBINOMDIST NORMDIST NORMSDIST POISSON TDIST WEIBULL

inverse CDF BETAINV CHIINV FINV GAMMAINV LOGINV NORMINV NORMSINV TINV...