Technology – Page 3

Compute the Sample Mean, Variance and Standard Deviation Using a Spreadsheet

The table below shows the dividend yields of six companies in the New York Stock Exchange energy sector.

Company	Dividend Yield July 2012 (%)
BP	4.80
Chevron	3.41
Exxon Mobil	2.66
PetroChina	3.50
Petroleo Brasiliero	1.20
Royal Dutch Shell	4.30

a. Find the sample mean.

Solution Use the AVERAGE command in a spreadsheet to compute the mean.

1. Enter the data from the table into the different cells in the spreadsheet.

2. In cell A9, type Mean to indicate that the number in B9 will be the mean.

3. Click on cell B9. This is where we will place the mean of the data. Type =AVERAGE( as shown to the right. To indicate the location of the data, type B2:B7. You can also click in cell B2, hold the left mouse button down and drag the cursor to cell B7. Type ) to complete the command.

4. Press Enter to compute the mean.

b. Find the sample variance.

Solution Use the VARA (VARPA for population variance) command to compute the sample variance of the data.

1. Type Variance in cell A10. Since we need the sample variance, type =VARA( in cell B10. Type B2:B7 or drag select these cells to identify the location of the data.

2. Press Enter to compute the sample variance.

c. Find the sample standard deviation.

Solution Use the STDEVA command to find the sample standard deviation of the data.

1. In cell A11, type Standard Deviation. In cell B11, type STDEVA( . Type B2:B7 or drag select these cells to identify the location of the data. To compute the population standard deviation, you would use the command STDEVPA. Type ) to complete the command.

2. Press Enter to compute the sample standard deviation.

How Do You Graph a Formula in WolframAlpha?

A number of you have asked me about alternatives to a TI graphing calculator. In addition to Excel, there are many online graphing tools available. If you have an Android phone or IPhone, there are a huge number of free apps that are available. I have had a hard time finding just one that does everything we need. Most will graph formulas, but may or may not graph data and fit data to lines. None of them work identically to a graphing calculator which makes them difficult to support. But luckily there is another option! Continue reading “How Do You Graph a Formula in WolframAlpha?”

How Do You Find Statistics From Frequency Data In A Spreadsheet?

Suppose we are given some frequencies corresponding to some data in intervals.

To find the mean, we need to find a representative data value from each interval.

We’ll use the midpoint of each interval. The midpoint for the first interval is

We can find the other midpoints in a similar manner. Let’s add this to the table.

Let’s put this information in a spreadsheet.

To find the mean of this frequency distribution, multiply each data value times its corresponding frequency. In the spreadsheet, put =B2*C2 in cell D2.

Press Enter to compute this value. Next we need to fill this value into the cells D3 through D7. Click on cell D2. The cell will be outlined like you see below.

Place your mouse over the box in the lower right hand corner of this outline. The cursor will change to a black cross. Hold down the left mouse button and drag the cursor to cell D7.

When you release the button, the products will be calculated for each row. Adding a label in cell D1 might help you to remember what the numbers in the cell are.

The function SUM is useful for adding up lists of numbers. For instance, we can find the sum of the frequencies N by typing =SUM(C2:C7). Put this formula in cell B9.

In this picture a label has been added in A9 to help the reader understand what is in the cell to the right. Add another label in cell A10 with the text “Mean”. Next to that cell we’ll calculate the mean of the data. This is done by adding the entries in column D and dividing by the sum of the frequencies in cell B9.

Finding the variance is a bit more complicated. In this calculation we need to subtract the mean from each data value and square the result. This needs to be done with an absolute reference to cell B10 so that the fill always refer to that cell in making the calculation. Start by clicking in cell E2 and typing =B2-$B$10.

This means that the data value 9.5 is approximately 59.3 units below the mean. Fill the rest of the column and we end up with this worksheet.

We could sum these deviation now, but the positive and negative nature of each row would mask the spread of the data. Squaring each of the entries makes each of them positive. In cell F2, type =E2^2.

Fill the rest the rows using a fill.

The entries in this column are often called the deviations from the mean squared. Each of these occur with the frequencies in column C. To find the sample variance, multiply the entries in column F by the frequencies in column C to give the worksheet below.

Let’s calculate the variance. Start by typing a label for the sample variance in cell D9. In the adjacent cell we’ll put the value. The variance is the sum of the entries divided by the sum of the frequencies minus 1. In cell E9, type =SUM(G2:G7)/(B9-1).

The sample standard deviation is the square root of the standard variance. We can take the square root using the function SQRT. Put a label in cell D10 and type =SQRT(E9) in the adjacent cell.

The standard deviation is a measure of how spread out the frequency distribution is around the mean.

How Do You Solve a Linear System with WolframAlpha?

Many of you may already be familiar with using a graphing calculator to put a matrix in reduced row echelon form. Did you know that you can do the same thing with WolframAlpha?

To see how this is done, let’s start from the system of linear equations

Convert this system into a 3 x 4 augmented matrix:

WolframAlpha understands several commands for putting an augmented matrix into reduced row echelon form. You can use the command rref { }or the command row reduce { }. The matrix goes inside the curly brackets. However, the matrix must be put in carefully. Each row needs to be typed in inside of curly brackets with the entries separated by a commas. In this case, you would type

on the command line in WolframAlpha.

After you press Enter, the reduced row echelon form is computed,

This indicates that the solution to the system is

x = 65,000, y = 45,000, z = 40,000.

How Do You Make a Table of Values in Google Sheets?

Suppose you want to generate a table of values from a formula…perhaps to help evaluate a limit. Google Sheets (or any spreadsheet) can quickly generate the values.

Make a table of values for f (x) = 7x² + 1 for x = 0, 0.9, 0.99, 0.999, 0.9999.

1. Open Google Sheets.

2. In the first row place the name of the input variable and the function’s name. We will put the values of the independent variable the first column (A) and the values of the dependent variable in the second column (B).

3. In cells A2 through A6, put the values of the independent variable for the table.

4. Click the mouse in cell B2. Enter the formula in that cell by typing =7*A2^2+1.

5. Press Enter to evaluate the formula in cell B2.

6. Click the mouse in cell B2. You will see a black outline around the cell. Use the mouse to grab the fill handle in the lower right hand corner of the black outline.

7. Click on this handle. While holding the left mouse button down, drag the mouse to cell B6.

8. When the mouse button is released, cells B2 through B6 are filled with the function values corresponding to the inputs in column A.