1

Statistics using Excel

2010

Tsagris Michail

mtsagris@yahoo.gr

Athens, Nottingham and Abu Halifa 2014

2

Table of Contents

1.1 Introduction ...................................................................................................................................... 3

2.1 Data Analysis toolpack ....................................................................................................................... 4

2.2 Descriptive Statistics .......................................................................................................................... 6

2.3 Z-test for two samples ....................................................................................................................... 8

2.4 t-test for two samples assuming unequal variances ........................................................................... 9

2.5 t-test for two samples assuming equal variances ............................................................................. 10

2.6 F-test for the equality of variances ................................................................................................... 11

2.7 Paired t-test for two samples ........................................................................................................... 12

2.8 Ranks, Percentiles, Sampling, Random Numbers Generation ........................................................... 13

2.9 Covariance, Correlation, Linear Regression ...................................................................................... 15

2.10 One-way Analysis of Variance ........................................................................................................ 19

2.11 Two-way Analysis of Variance with replication ............................................................................... 20

2.12 Two-way Analysis of Variance without replication ......................................................................... 23

2.13 Statistical functions ........................................................................................................................ 24

3.1 The Solver add-in ............................................................................................................................. 27

3

1.1 Introduction

One of the reasons for which these notes were written was to help students and not only to

perform some statistical analyses without having to use statistical software such as R, SPSS, and

Minitab etc. It is reasonable not to expect that excel offers much of the options for analyses

offered by statistical packages but it is in a good level nonetheless.

The areas covered by these notes are: descriptive statistics, z-test for two samples, t-

test for two samples assuming (un)equal variances, paired t-test for two samples, F-test for

the equality of variances of two samples, ranks and percentiles, sampling (random and

periodic, or systematic), random numbers generation, Pearson’s correlation coefficient,

covariance, linear regression, one-way ANOA, two-way ANOVA with and without

replication and the moving average.

We will also demonstrate the use of non-parametric statistics in Excel for some of the

previously mentioned techniques. Furthermore, informal comparisons with the results provided

by the Excel and the ones provided by SPSS and some other packages will be carried out to see

for any discrepancies between Excel and SPSS. One thing that is worthy to mention before

somebody goes through these notes is that they do not contain the theory underlying the

techniques used. These notes show how to cope with statistics using Excel.

The first edition was in May 2008. In the second edition (July 2012) we added the solver

library. This allows us to perform linear numerical optimization (maximization/minimization)

with or without linear constraints. It also offers the possibility to solve a system of equations

again with or without linear constraints. I am grateful to Vassilis Vrysagotis (teaching fellow at

the Technological Educational Institute of Chalkis,) for his contribution. This third edition

(November 2014) uses Excel 2010 (upgrading, even in 2014).

Any mistakes you find, or disagree with something stated here or anything else you want

to ask, please send me an e-mail. For more statistical resources the reader is addressed to

statlink.tripod.com.

4

2.1 Data Analysis toolpack

If the Excel does not offer you options for statistical analyses you can add this option very easily.

Just click on the File on the top left and a list will appear. From the list menu you select Options

and picture 1 will appear on the screen.

Picture 1

Select add-Ins from the list on the left and the window of Picture 2 will appear. In this window

(Picture 2) press Go… to move on to the window of Picture 3 where you select the two options

as I did in Picture 3. If you go to the tab Data in Excel you will see the Data analysis and Solver

libraries added (Picture 4). The solver we will need it later. The good thing is that we only have

to do this once, not every time we open the computer.

5

Picture 2

Picture 3

6

Picture 4

By pressing Data analysis (see picture 4) the window of Picture 5 will appear.

Picture 5

2.2 Descriptive Statistics

The data used in most of the examples the cars data taken from R. This data set contains

information about the speed and distance covered until the automobile is stopped, of 50 cars. In

the two previous versions of this document I was using the cars data (cars.sav) from SPSS.

Unfortunately, I do not have these data anymore.

The road will always be the same, click on Data in the tools bar and from there choose

Data Analysis. The dialogue box of picture 5 appears on the screen. We Select Descriptive

Statistics and click OK and we are lead to the dialogue box of picture 6. In the Input Range

white box we specified the data, ranging from cell 2 to cell 51 all in one column. If the first row

contained label we could just define it by clicking that option. We also clicked two of the last

four options (Summary statistics, Confidence Level for Mean). As you can see the default

7

value for the confidence level is 95%. In other words the confidence level is set to the usual

95%. The results produced by Excel are provided in table 1.

Picture 6

Column1

Mean 15.4

Standard Error 0.74778585

Median 15

Mode 20

Standard Deviation 5.28764444

Sample Variance 27.9591837

Kurtosis -

0.50899442

Skewness -

0.11750986

Range 21

Minimum 4

Maximum 25

Sum 770

Count 50 Confidence Level(95.0%) 1.50273192

Table 1: Descriptive statistics for the speed of cars.

The results are pretty much the same as should be. There are only some really slight differences

with regard to the rounding in the results of SPSS but of no importance. The number of

observations is 406 as we expected. If there are missing values, the value in count will be less

8

than the number of rows we selected. The sample variances differ slightly but it is really not a

problem. SPSS calculates a 95% confidence interval for the true mean whereas Excel provides

only the quantity used to calculate the 95% confidence interval. The construction of this interval

is really straightforward. Subtract this quantity from the mean to get the lower limit and add it to

the mean to get the upper limit of the 95% confidence interval. So it is (mean-conf.level,

mean+conf.level)=(15.4-1.50273192, 15.4+1.50273192)=(13.89727, 16.90273).

2.3 Z-test for two samples

The statistical packages known to the writer do not offer the z-test for two independent samples.

The results are pretty much the same with the case of the t test for two independent samples. The

difference between the two tests is that apart from the normality assumption the z test assumes

that we know the true variances of the two samples. We used data generated from two normal

distributions with mean equal to zero for both population but different variances. Due to the

limited options offered by Excel we cannot test the normality hypothesis of the data (this is also

a problem met in the latter cases). Following the previously mentioned path and selecting the Z

test for two samples from the dialogue box of picture 4 the dialogue box of picture 7 appears on

the screen. The first column contains the data of the first sample of size 20 while the second

column is of size 30. I split the speed data in two groups, the first 20 observations and the other

30.

Picture 7

We selected the hypothesized mean difference to be zero and filled the white boxes of the

variances with the variances. In order to perform the z-test we must know the variance of each

population from which the sample came from. Since we do not have this information, we put the

sample variances for illustration purposes. The value of the z-statistic, the critical values and the

p-values for the one-sided and two-sided tests are provided. The results, provided in Table 2, are

9

the same with the ones generated by R. Both of the p-values are equal to zero, indicating that the

mean difference of the two populations from which the data were drawn, is statistically

significant at an alpha equal to 0.05.

z-Test: Two Sample for Means

Variable 1 Variable 2

Mean 10.25 18.83333333

Known Variance 8.618421 11.1092

Observations 20 30

Hypothesized Mean Difference 0

z -

9.589103286 P(Z<=z) one-tail 0 z Critical one-tail 1.644853627 P(Z<=z) two-tail 0 z Critical two-tail 1.959963985

Table 2: Z-test.

2.4 t-test for two samples assuming unequal variances

The theory states that when the variances of the two independent populations are not known

(which is usually the case) we have to estimated them. The use of t-test is suggested in this case

(but still the normality hypothesis has to be met unless the sample size is large). There are two

approaches in this case; the one when we assume the variance to be equal and the one we cannot

assume that. We will deal with the latter case now.

We used the same data set as before and we suppose that the variances cannot be

assumed to be equal. We will see the test for the equality of two variances later. Selecting the t-

test assuming unequal variances from the dialogue box of picture 4 the dialogue box of picture 8

appears on the screen. The results generated from SPSS are the same except for some rounding

differences. In case you forget to set the hypothesized mean difference equal to 0, excel will use

by default this number.

10

Picture 8

t-Test: Two-Sample Assuming Unequal Variances

Variable 1 Variable 2

Mean 10.25 18.83333333

Variance 8.618421053 11.1091954

Observations 20 30

Hypothesized Mean Difference 0 df 44

t Stat -

9.589104187 P(T<=t) one-tail 1.19886E-12 t Critical one-tail 1.680229977 P(T<=t) two-tail 2.39773E-12 t Critical two-tail 2.015367574

Table 3: t-test assuming unequal variances.

2.5 t-test for two samples assuming equal variances

We will perform the same test assuming that the equality of variances holds true. The dialogue

box for this test following the famous path is that of picture 9.

The results are the same with the ones provided by SPSS. What is worthy to mention and

to pay attention is that the degrees of freedom (df) for this case are equal to 178, whereas in the

previous case were equal to 96. Also the t-statistics is slightly different. The reason it that

different kind of formulae are used in these two cases.

11

Picture 9

t-Test: Two-Sample Assuming Equal Variances

Variable 1 Variable 2

Mean 10.25 18.83333333

Variance 8.618421053 11.1091954

Observations 20 30

Pooled Variance 10.12326389 Hypothesized Mean Difference 0 df 48 t Stat -9.34515099 P(T<=t) one-tail 1.10835E-12 t Critical one-tail 1.677224196 P(T<=t) two-tail 2.2167E-12 t Critical two-tail 2.010634758

Table 4: t-test assuming equal variances.

2.6 F-test for the equality of variances

We will now see how to test the hypothesis of the equality of variances. The dialogue box of

picture 10 appears in the usual way by selecting the F-test from the dialogue box of picture 4.

The results are the same with the ones provided by R. The p-value is equal to zero indicating that

there is evidence to reject the assumption of equality of the variance of the two samples at an

alpha equal with 0.05.

12

Picture 10

F-Test Two-Sample for Variances

Variable 1 Variable 2

Mean 10.25 18.83333333

Variance 8.618421053 11.1091954

Observations 20 30

df 19 29

F 0.775791652

P(F<=f) one-tail 0.285474981

F Critical one-tail 0.481414106

Table 5: F-test for the equality of variances.

2.7 Paired t-test for two samples

Suppose that you are interested in testing the equality of two means, but the two samples (or the

two populations) are not independent. For instance, when the data refer to the same people

before and after a diet program. The dialogue box of picture 11 refers to this test. The results

provided at table 6 are the same with the ones generated from SPSS. We can also see that the

Pearson’s correlation coefficient is calculated.

13

Picture 11

t-Test: Paired Two Sample for Means

Variable 1 Variable 2

Mean 10.25 16.95

Variance 8.618421053 3.944736842

Observations 20 20

Pearson Correlation 0.941021597 Hypothesized Mean Difference 0 df 19 t Stat -23.76638508 P(T<=t) one-tail 6.77131E-16 t Critical one-tail 1.729132812 P(T<=t) two-tail 1.35426E-15 t Critical two-tail 2.093024054

Table 6: Paired t-test.

2.8 Ranks, Percentiles, Sampling, Random Numbers Generation

The dialogue box for the Ranks and Percentiles is the one of picture 12. The use of this option

is to assign a rank and a percentage at each number. The rank refers to the relative order of the

number, i.e. rank 1 is assigned to the highest value; rank 2 to the second highest and so on. The

percentages are of the same use.

14

Picture 12

The dialogue box of the sampling option is the one in the picture 13. Two sampling

schemes are available, of the systematic (periodic) and of the random sampling. In the first

case you insert a number (period), let’s say 5, means that the first value of the sample will be the

number in that row (5th row) and all the rest values of the sample will be the ones of the 10th,

the 15th, the 20th rows and so on. With the random sampling method, you state the sample size

and Excel does the rest. If you specify a number in the second option of the sampling method,

say 30, then a sample of size 30 will be selected from the column specified in the first box.

Picture 13

If you are interested in a random sample from a known distribution then the random

numbers generation is the option you want to use. Unfortunately not many distributions are

offered. The dialogue box of this option is at picture 14. In the number of variables you can

select how many samples you want to be drawn from the specific distribution. The white box

below is used to define the sample size. The distributions offered are Uniform, Normal,

Bernoulli, Binomial, and Poisson. Two more options are also allowed. Different distributions

require different parameters to be defined.

15

Picture 14

The random seed is an option used to give the sampling algorithm a starting value but can

be left blank as well. If we specify a number, say 1234, then the next time we want to generate

another sample, if we put the same random seed again we will get the same sample. The number

of variables allows to generate more than one samples.

2.9 Covariance, Correlation, Linear Regression

The covariance and correlation of two variables or two columns containing data is very easy to

calculate. The dialogue box of correlation and covariance are the same. For the correlation

matrix from the dialogue box of picture 4 we select correlation.

Picture 15

16

Column 1 Column 2

Column 1 1 Column 2 0.941022 1

Table 7: Correlation matrix.

The above table is called the correlation matrix. The dialogue box of the linear regression

option is presented at picture 16. We fill the white boxes with the columns that represent Y and

X values. The X values can contain more than one column (i.e. variable). We have to note that if

one value is missing in any column the function will not be calculated. This function requires

that all columns have the same number of values. Thus, if one or more columns have missing

values we have to delete these rows from all columns before running the regression.

We select the confidence interval option. We also select the Residual Plots, Line Fit

Plots and Normal Probability Plots. The option Constant is Zero is left un-clicked. We want

the constant to be in the regression line regardless of its statistical significance. By pressing OK,

the result appears in table 8.

Picture 16

17

SUMMARY OUTPUT

Regression Statistics Multiple R 0.941021597 R Square 0.885521645 Adjusted R

Square 0.879161737 Standard Error 0.690416648 Observations 20

ANOVA

df SS MS F Significance

F Regression 1 66.36984733 66.36984733 139.2349644 6.6133E-10 Residual 18 8.580152672 0.476675148

Total 19 74.95

Coefficients Standard Error t Stat P-value Lower 95% Upper 95%

Intercept 10.42442748 0.574168954 18.15567944 5.08222E-13 9.21814327 11.6307117

X Variable 1 0.636641221 0.053953621 11.79978663 6.6133E-10 0.523288869 0.74999357

Table 8: Analysis of variance table for the regression.

The multiple R is the Pearson correlation coefficient, whereas the R Square is called

coefficient of determination and it is a quantity that measures the fitting of the model. It shows

the proportion of variability of the data explained by the linear model. The model is

Y=10.4144+0.6366*X. The adjusted R Square is the coefficient of determination adjusted for

the degrees of freedom of the model; this is a penalty of the coefficient. The p-value of the

constant provides evidence to claim that the constant is not statistical significant and therefore it

should be removed from the model. So, if we run the regression again we will just click on

Constant is Zero, right? No, even if the constant is not significant, we still keep it in the model.

Why? Because, the residuals will not sum to zero. So, do not look at the significance of the

constant. Does it really matter if it’s zero or not? Does it affect the outcome? The point is to see

the significance of the coefficients of the independent variables, not of the constant. Try fitting

the regression line with a zero constant and check the plot of Figure 2 then and compare.

The results are the same generated by SPSS except for some slight differences due to

roundings. The disadvantage of Excel is that it offers no normality test. The two plots also

constructed by Excel are presented.

18

Figure 1: Scatter plot of X and Y & predicted Y.

Figure 2: Residuals versus predicted values

The first figure is a scatter plot of the data, the X values versus the Y values and the

predicted Y values. The linear relation between the two variables is obvious through the graph.

Do not forget that the correlation coefficient exhibited a high value.

Excel produced also the residuals and the predicted values in the same sheet. We shall

construct a scatter plot of these two values, in order to check (graphically) the assumption of

homoscedasticity (i.e. constant variance through the residuals). If the assumption of

heteroscedasticity of the residuals holds true, then we should see all the values within a

bandwidth. We see that almost all values fall within -1.5 and 1.5. It seems like the variance is not

constant since there is seems to be evidence of a pattern. This means that the residuals do not

exhibit constant variance. But then again, we only have 30 points, so our eyes could be wrong. If

we are not sure about the validity of the assumption we can transform the Y values using a log

transformation and run the regression using the transformed Y values.

0

5

10

15

20

0 5 10 15

Y

X Variable 1

X Variable 1 Line Fit Plot

Y

Predicted Y

0

0.2

0.4

0.6

0.8

1

1.2

0 1 2 3 4 5

Res

idu

als

X Variable 1

X Variable 1 Residual Plot

19

The Normal Probability Plot is used to check the normality of the residuals graphically.

Should the residuals follow the normal distribution, then the graph should be a straight line.

Unfortunately many times the eye is not the best judge of things. The Kolmogorov Smirnov test

conducted in SPSS provided evidence to support the normality hypothesis of the residuals.

Figure 2: Normal probability plot of the residuals.

2.10 One-way Analysis of Variance

The one-way analysis of variance is just the generalization of the two independent samples t-test.

The assumptions the must be met in order for the results to be valid are more or less the same as

in the linear regression case. It is a fact that analysis of variance and linear regression are two

equivalent techniques. The Excel produces the analysis of variance table but offers no options to

check the assumptions of the model. The dialogue box of the one way analysis of variance is

shown at picture 17. As in the t-test cases the values of the independent variable are entered in

Excel in different columns according to the factor. In our example we have three levels of the

factor, therefore we have three columns. After defining the range of data in the dialogue box of

picture 17, we click OK and the results follow.

0

0.2

0.4

0.6

0.8

1

1.2

0 0.2 0.4 0.6 0.8 1 1.2

Y

Sample Percentile

Normal Probability Plot

20

Picture 17

Anova: Single Factor

SUMMARY Groups Count Sum Average Variance

Column 1 15 139 9.266667 7.495238 Column 2 12 188 15.66667 1.878788 Column 3 23 443 19.26087 15.29249

ANOVA Source of Variation SS df MS F P-value F crit

Between Groups 907.9652 2 453.9826 46.1809 8.07E-

12 3.195056

Within Groups 462.0348 47 9.830527

Total 1370 49

Table 9: The one-way analysis of variance

The results generated by SPSS are very close with the results shown above. There is

some difference in the sums of squares, but rather of small importance. The mean square values

(MS) are very close with one another. Yet, by no means can we assume that the above results

hold true since Excel does not offer options for assumptions checking.

2.11 Two-way Analysis of Variance with replication

In the previous paragraph, we saw the case when we have one factor affecting the dependent

variable. Now, we will see what happens when we have two factors affecting the dependent

variable. This is called the factorial design with two factors or two-way analysis of variance. At

21

first, we must enter the data in the correct way. The proper way of data entry follows (the data

refer to the cars measurements). As you can see, we have three columns of data representing the

three levels of the one factor and the first columns contains only three words, C1, C2 and C3.

This first column states the two levels of the second factor. We used the R1, and R2 to define the

number of the rows representing the sample sizes of each combination of the two factors. In

other words the first combination the two factors are the cells from B2 to B6. This means that

each combination of factors has 5 measurements.

Picture 18

From the dialogue box of picture 4, we select Anova: Two-Factor with replication and the

dialogue box to appear is shown at picture 19.

Picture 19

22

We filled the two blank white boxes with the input range and Rows per sample. The alpha is at

its usual value, equal to 0.05. By pressing OK the results are presented overleaf. The results

generated by SPSS are the same. At the bottom of the table 10 there are three p-values; two p-

values for the two factors and one p-value for the interaction. The row factor is denoted as

sample in Excel.

A limitation of this analysis when performed in Excel is that the sample sizes in each

combination of column and rows (the two factors) must be equal. In other words, the design has

to be balanced, the same number of values everywhere.

Anova: Two-Factor With Replication

SUMMARY C1 C2 C3 Total S1

Count 5 5 5 15 Sum 31 48 58 137 Average 6.2 9.6 11.6 9.133333 Variance 4.7 60.8 0.3 24.12381

S2

Count 5 5 5 15 Sum 75 130 73 278 Average 15 26 14.6 18.53333 Variance 62 34 9.3 59.98095

Total

Count 10 10 10 Sum 106 178 131 Average 10.6 17.8 13.1 Variance 51.15556 116.8444 6.766667

ANOVA Source of Variation SS df MS F P-value F crit

Sample 662.7 1 662.7 23.23904 6.55E-05 4.259677

Columns 267.2667 2 133.6333 4.686148 0.019138 3.402826

Interaction 225.8 2 112.9 3.959088 0.032665 3.402826

Within 684.4 24 28.51667 Total 1840.167 29

Table 10: The table of the two-way analysis of variance with replication.

23

2.12 Two-way Analysis of Variance without replication

We will now see another case of the two-way ANOVA when each combination of factors has

only one measurement. In this case we need not enter the data as in the previous case in which

the labels were necessary. We will use only the three first three rows of the data.

We still have two factors except for the fact that each combination contains one

measurement. From the dialogue box of picture 4, we select Anova: Two-Factor without

replication and the dialogue box to appear is shown at picture 20. The only thing we did was to

define the Input Range and pressed OK. The results are presented in table 11. What is necessary

for this analysis is that there no interaction is present. The results are the same with the ones

provided by SPSS, so we conclude once again that Excel works fine with statistical analysis. The

disadvantage of Excel is once again that it provides no formulas for examining the residuals in

the case of analysis of variance.

Picture 20

Anova: Two-Factor Without Replication SUMMARY Count Sum Average Variance

Row 1 3 17 5.666667 22.33333 Row 2 3 25 8.333333 14.33333 Column 1 2 8 4 0 Column 2 2 12 6 32 Column 3 2 22 11 0

ANOVA Source of Variation SS df MS F P-value F crit

Rows 10.66667 1 10.66667 1 0.42265 18.51282

Columns 52 2 26 2.4375 0.290909 19

Error 21.33333 2 10.66667 Total 84 5

Table 11: The table of the two-way analysis of variance without replication.

24

2.13 Statistical functions

Before showing how to find statistical measures using the statistical functions available

from Excel under the Insert Function option let us see which are these.

AVEDEV calculates the average of the absolute deviations of the data from their

mean.

AVERAGE is the mean value of all data points.

AVERAGEA calculates the mean allowing for text values of FALSE (evaluated

as 0) and TRUE (evaluated as 1).

BETADIST calculates the cumulative beta probability density function.

BETAINV calculates the inverse of the cumulative beta probability density

function.

BINOMDIST determines the probability that a set number of true/false trials,

where each trial has a consistent chance of generating a true or false result, will

result in exactly a specified number of successes (for example, the probability that

exactly four out of eight coin flips will end up heads).

CHIDIST calculates the one-tailed probability of the chi-squared distribution.

CHIINV calculates the inverse of the one-tailed probability of the chi-squared.

Distribution.

CHITEST calculates the result of the test for independence: the value from the

chi-square distribution for the statistics and the appropriate degrees of freedom.

CONFIDENCE returns a value you can use to construct a confidence interval for

a population mean.

CORREL returns the correlation coefficient between two data sets.

COVAR calculates the covariance of two data sets. Mathematically, it is the

multiplication of the correlation coefficient with the standard deviations of the

two data sets.

CRITBINOM determines when the number of failures in a series of true/false

trials exceeds a criterion (for example, more than 5 percent of light bulbs in a

production run fail to light).

DEVSQ calculates the sum of squares of deviations of data points from their

sample mean. The derivation of standard deviation is very straightforward, simply

dividing by the sample size or by the sample size decreased by one to get the

unbiased estimator of the true standard deviation.

EXPODIST returns the exponential distribution

FDIST calculates the F probability distribution (degree of diversity) for two data

sets.

FINV returns the inverse of the F probability distribution.

FISHER calculates the Fisher transformation.

FISHERINV returns the inverse of the Fisher transformation.

FORECAST calculates a future value along a linear trend based on an existing

time series of values.

FREQUENCY calculates how often values occur within a range of values and

then returns a vertical array of numbers having one or more elements than Bins_array.

25

FTEST returns the result of the one-tailed test that the variances of two data sets

are not significantly different.

GAMMADIST calculates the gamma distribution.

GAMMAINV returns the inverse of the gamma distribution.

GAMMALN calculates the natural logarithm of the gamma distribution.

GEOMEAN calculates the geometric mean.

GROWTH predicts the exponential growth of a data series.

HARMEAN calculates the harmonic mean.

HYPGEOMDIST returns the probability of selecting an exact number of a single

type of item from a mixed set of objects. For example, a jar holds 20 marbles, 6 of

which are red. If you choose three marbles, what is the probability you will pick

exactly one red marble?

INTERCEPT calculates the point at which a line will intersect the y-axis.

KURT calculates the kurtosis of a data set.

LARGE returns the k-th largest value in a data set.

LINEST generates a line that best fits a data set by generating a two dimensional

array of values to describe the line.

LOGEST generates a curve that best fits a data set by generating a two

dimensional array of values to describe the curve.

LOGINV returns the inverse logarithm of a value in a distribution.

LOGNORMDIST Returns the number of standard deviations a value is away

from the mean in a lognormal distribution.

MAX returns the largest value in a data set (ignore logical values and text).

MAXA returns the largest value in a set of data (does not ignore logical values

and text).

MEDIAN returns the median of a data set.

MIN returns the largest value in a data set (ignore logical values and text).

MINA returns the largest value in a data set (does not ignore logical values and

text).

MODE returns the most frequently occurring values in an array or range of data.

NEGBINOMDIST returns the probability that there will be a given number of

failures before a given number of successes in a binomial distribution.

NORMDIST returns the number of standard deviations a value is away from the

mean in a normal distribution.

NORMINV returns a value that reflects the probability a random value selected

from a distribution will be above it in the distribution.

NORMSDIST returns a standard normal distribution, with a mean of 0 and a

standard deviation of 1.

NORMSINV returns a value that reflects the probability a random value selected

from the standard normal distribution will be above it in the distribution.

PEARSON returns a value that reflects the strength of the linear relationship

between two data sets.

PERCENTILE returns the k-th percentile of values in a range.

PERCENTRANK returns the rank of a value in a data set as a percentage of the

data set.

26

PERMUT calculates the number of permutations for a given number of objects

that can be selected from the total objects.

POISSON returns the probability of a number of events happening, given the

Poisson distribution of events.

PROB calculates the probability that values in a range are between two limits or

equal to a lower limit.

QUARTILE returns the quartile of a data set.

RANK calculates the rank of a number in a list of numbers: its size relative to

other values in the list.

RSQ calculates the square of the Pearson correlation coefficient (also met as

coefficient of determination in the case of linear regression).

SKEW returns the skewness of a data set (the degree of asymmetry of a

distribution around its mean).

SLOPE returns the slope of a line.

SMALL returns the k-th smallest values in a data set.

STANDARDIZE calculates the normalized values of a data set (each value minus

the mean and then divided by the standard deviation).

STDEV estimates the standard deviation of a numerical data set based on a

sample of the data.

STDEVA estimates the standard deviation of a data set (which can include text

and true/false values) based on a sample of the data.

STDEVP calculates the standard deviation of a numerical data set.

STDEVPA calculates the standard deviation of a data set (which can include text

and true/false values).

STEYX returns the predicted standard error for the y value for each x value in

regression.

TDIST returns the Student’s t distribution

TINV returns a t value based on a stated probability and degrees of freedom.

TREND Returns values along a trend line.

TRIMMEAN calculates the mean of a data set having excluded a percentage of

the upper and lower values.

TTEST returns the probability associated with a Student’s t distribution.

VAR estimates the variance of a data sample.

VARA estimates the variance of a data set (which can include text and true/ false

values) based on a sample of the data.

VARP calculates the variance of a data population.

VARPA calculates the variance of a data population, which can include text and

true/false values.

WEIBULL calculates the cumulative Weibull distribution.

ZTEST returns the two-tailed p-value of a z-test.

27

3.1 The Solver add-in

Let us suppose we want to maximize the following linear bivariate function

f(X,Y)=400X+300Y,

under some linear constraints

I. 4X+2Y ≤300

II. X≤70

III.2X+4Y≤240

The way to do it in Excel 2007 is simple. At first we will go to picture 3 and select the option

Solver add-in (we already did this). Then, similarly to the data analysis path we click on Data in

the tools bar and from there choose Solver. The dialogue box of picture 21 will appear.

But before doing these we have to put the functions in Excel. Suppose Table 12 is the

Excel.

Column A Column B Column C

Row 1

Row 2 =A1*400+300*B1

Row 3 =4*A1+2*B1 300

Row 4 =a1 70

Row 5 =2*A1+4*B1 240

Table 12. Imitation of Excel.

The green is the function we want to maximize. The red are the constraints. In the cells

A1 and B1 we will put the X and Y answers respectively.

As we can see Excel offers the possibility for maximization, minimization and search for

the values of X and Y which satisfy a condition, such as that the function is equal to some

specific value, not only 0. Furthermore we have the option to perform the three mentioned tasks

with the inclusion of constraints, either in the form of equalities or inequalities. We will use the

form of inequalities in this example.

28

Picture 21

By pressing the button Add in the dialogue box of picture 21, the dialogue box of picture

22 will appear. We put the cell which describes the first constraint and the cell whose maximum

value is. We repeat this task until all constraints are entered. In case we have no constraints, we

do not have to come here. After the last constraint is entered we press Add. When we put the

final constraint we can either press OK or press Add first and then OK. In the second case a

message will appear (picture 23) preventing us from continuing. We will press Cancel and we

will go to picture 24, which is the same as picture 21, but with the constraints now added.

Picture 22

29

Picture 23

Picture 24

Then we select the Simplex LP as the solving method and the message of picture 25 will appear.

We press OK and the message disappears. The solution will also appear in Excel.

30

Picture 25

Column A Column B Column C

Row 1 60 30

Row 2 33000

Row 3 300 300

Row 4 60 70

Row 5 240 240

Table 15: Result of the constrained optimization.