Recipe5.9.Sampling Data


Recipe 5.9. Sampling Data

Problem

You need to draw random samples from a finite population of discrete values.

Solution

Use the Analysis ToolPak Sampling tool. Select Tools images/U2192.jpg border=0> Data Analysis from the main menu bar and then select Sampling from the list of available tools.

Discussion

Figure 5-22 shows a spreadsheet containing a set of discrete values from which three were selected at random. The Sampling tool discussed a moment ago was used to draw these three random samples.

Upon selecting the Sampling tool from the Data Analysis tool list, you should see the Sampling dialog box shown in Figure 5-23.

Figure 5-22. Sampling discrete values


Figure 5-23. Sampling dialog box


In the Input Range, enter or select the cell range containing the discrete values from which to draw random samples. Next, select the Sampling Method, which can be either Periodic or Random. If you choose Periodic you also have to specify a periodic interval where sampling will take place. At every kth period, a random sample will be drawn. For example, if you choose Periodic with a period of 2 for the example data shown in Figure 5-22, then six samples will be drawn, corresponding to every other value in the input range starting from the second value.

If you choose Random, then you have to specify how many random samples to draw. In this example, I chose to draw three random samples. Finally, select your output options and press OK. In this example I chose to display the results in the same worksheet, starting at cell E11. The results are shown in Figure 5-22.

See Also

See Recipe 5.8 to learn how to generate random numbers in Excel.


Chapter 6. Time Series Analysis

    Section 6.0.  Introduction

    Recipe 6.1.  Plotting Time Series Data

    Recipe 6.2.  Adding Trendlines

    Recipe 6.3.  Computing Moving Averages

    Recipe 6.4.  Smoothing Data Using Weighted Averages

    Recipe 6.5.  Centering Data

    Recipe 6.6.  Detrending a Time Series

    Recipe 6.7.  Estimating Seasonal Indices

    Recipe 6.8.  Deseasonalization of a Time Series

    Recipe 6.9.  Forecasting

    Recipe 6.10.  Applying Discrete Fourier Transforms


6.0. Introduction

Time series analysis is a broad field of study with applications ranging from engineering analysis to socioeconomic forecasting to stock market prediction, among many other applications. You can use Excel to perform many tasks required in time series analysis , such as pre- and post-processing of data, visualizing results, and making forecasts. Excel also offers a Fourier Analysis tool that allows you to transform time series data to the frequency domain (and back) for further analysis. This chapter aims to show you some specific examples of how you can effectively use Excel when analyzing time series data.

There are many time series analysis techniques available, many of which are tailored to specific kinds of time series, so I'm not going to attempt to cover them all. Instead, the examples discussed here serve to show you how you can leverage Excel's standard functionality to perform some important analysis tasks. Building on the techniques I show you here, you have the ability and power within Excel to devise all sorts of other analysis techniques.


Recipe 6.1. Plotting Time Series Data

Problem

You need to plot a set of time series data for further analysis.

Solution

Time series charts, such as the one shown in Figure 6-1, are easy to create in Excel.

Select the data series (both x and y values) you want to chart and then press the Chart Wizard button on the Standard toolbar, or select Insert images/U2192.jpg border=0> Chart... from the main menu bar to open the Chart Wizard. The Chart Wizard allows you to select the chart style and customize charts in various ways. Read Chapter 4 to learn more about creating charts in Excel.

Figure 6-1. Time series chart