стр. 64 |

(c) Estimate the residual, by using the formula:

Residual= Y вЂ” Predicted_Y

(d) Standardize the series of residuals using the function INSERT/

FUNCTION/ STATISTICAL/ STANDARDIZE

(e) make the charts using the standardized series. See book two in

this series вЂ” Charting in Excel вЂ” for more on making charts.

224

Chapter 12: Regression

BREAKDOWN OF CLASSICAL ASSUMPTIONS:

12.4

VALIDATION AND CORRECTION

Basic validation can be conducted using procedures mentioned in the

previous table. Excel does not have procedures for more advanced testing.

The corrective procedures are not available in Excel.

The validation and corrective procedures are available in Add-Ins for

statistics. Links to such Add-Ins can be accessed at the URL

http://www.vjbooks.net/products/publications/Excel/Excel.htm.

For more on this topic, please refer to our book вЂњInterpreting

regression OutputвЂќ available at http://www.vjbooks.net.

225

Statistical Analysis with Excel

226

Page for Notes

Statistical Analysis with Excel

CHAPTER 13

OTHER TOOLS FOR STATISTICS

This chapter briefly touches on the following topics:

вЂ” SAMPLING ANALYSIS

вЂ” RANDOM NUMBER GENERATION

вЂ” TIME SERIES

вЂ” EXPONENTIAL SMOOTHING, MOVING AVERAGE ANALYSIS

This chapter requires the Analysis ToolPak Add-Ins; chapter 9

shows how to learn how to launch the Add-Ins.

SAMPLING ANALYSIS

13.1

This tool creates a sample from a population by treating the input range

as a population. You can use a representative sample when the

population is too large to process or chart. You can also create a sample

that contains only values fro a particular part of a cycle if you believe that

the input data is periodic Excel draws samples from the first column, then

the second column, and so on.

Access the feature through the menu path TOOLS/DATA ANALYSIS and

choose the procedure вЂњSampling.вЂќ

228

Chapter 13: Other Tools for Statistics

Figure 157: Sampling

Sampling Method: choose Periodic or Random to indicate the sampling

interval you want.

Period: Enter the periodic interval at which you want sampling to take

place. The interval value in the input range and every periodвЂ™s value

thereafter are copied to the output column.

Random & Number of Samples: Number of random values you desire in

the output column. Excel draws each value from a random position in the

input range. (Consequently, a value may be drawn more than once.)

Output Range: Data is written in a single column below the cell.

Note:

If you selected Periodic, the number of values in the output table is

equal to the number of values in the input range, divided by the

sampling rate. If you selected Random, the number of values in the

output table is equal to the number of samples.

229

Statistical Analysis with Excel

RANDOM NUMBER GENERATION

13.2

This tool fills a range with independent random numbers drawn from one

of several Density Functions.

You can characterize a population with a Probability Density Function.

стр. 64 |