excel guide appendix a

7/21/2019 Excel Guide Appendix A

http://slidepdf.com/reader/full/excel-guide-appendix-a 1/5

Excel User’s Guide A, Descriptive Statistics

306

APPENDIX A

MICROSOFT EXCEL USER’S GUIDE A

BASICS AND DESCRIPTIVE STATISTICS

A. Sorting the Data

Select a block of cells and then click on Data tab and click on Sort.

Example: Entering Sales Prices Data and Sorting Data

Enter the following sales prices of 25 residential properties sold and sort the data.

66 59 106 50 63

89 129 74 82 8471 95 72 57 76

109 77 68 101 65

42 36 148 94 112

Step 1. Select B1 and type Price.Step 2. Select B2.

Step 3. Enter 66 and press down-arrow.Enter 59 and press down-arrow.

....

( Note: Enter all data in one column.)Step 4. Select B1 to B26 .

Step 5. Click Edit/Copy from the main menu.

Step 6. Select C1 and click Edit/Paste.Step 7. Select C2 to C26 .

Step 8. Click on Data tab and click on Sort. A Sort dialog box appears on thescreen.

Step 9. If it appears with a Sorting Warning box, click on Continue with the

current selection. Then click Sort. You will get Sort dialog box.

Step 10. Click OK .

B. Line Graph Use Insert/Chart to construct a line graph.

Example: The following data are daily high temperatures:

70 74 72 78 75

Construct a line graph.

Step 1. Enter the data:

Select D1 and type Date.

Enter Mon, Tue, Wed, Thur, and Fri in the cells D2 to D6.Select E1 and type Temperature

Enter the data in the cells E2 to E6.




307

Step 2. Select the block from E2 to E6 .

Step 3. Click Insert tab and select Line icon in the Charts group.Step 4. Click the top-left icon for Line Trend .

Step 5. While the chart is selected, click Design tab.

Select horizontal axis label:

(i) Click Select Data in the Data group.(ii) Click Edit under Horizontal (Category) Axis Labels on the right

side.

(iii) Select cells D2 to D6 in the Axis label range box.Click OK and click OK .

Step 6. While the chart is selected, click Layout tab.

Enter title:(i) Click Chart Title in the Labels group and select Above Chart. Type

Daily Temperature in the text box.

Enter labels:

(i) Click Axis Titles in the Labels group, select Primary Horizontal

Axis Title, and select Title Below Axis. Type Date in the text box.(ii) Click Axis Titles in the Labels group, select Primary Vertical Axis

Title, and select Rotated Title. Type Degree in the text box.Remove legend:

(i) Click Legend in the Labels group.

(ii) Click None.

C. Numerical Functions

An Excel function always begins with an equal sign (=).

Example: Enter Temperature in E1 and the following data in E2 to E6.

70 74 72 78 75

We can use the following functions to get the descriptive statistics.

C.1 Count: =count(range)

The function “=count(range)” gives the number of data values in the data set.

Example: Find the number of data values in cells E2 to E6 and store in F8.

Step 1: Select E8 and type Count.

Step 2: Select F8 and type =count(e2.e6) then press [Enter] key.

C.2 Maximum: =max(range)

The function “=max(range)” gives the largest value for the data in a range of cells.




308

Example: Find the largest value for the data in cells E2 to E6 and store in F9.

Step 1. Select E9 and type Maximum.

Step 2. Select F9 and type =max(e2.e6) then press [Enter] key.

C.3 Minimum: =min(range)The function “=min(range)” gives the smallest value for the data in a range of cells.

Example: Find the smallest value for the data in cells E2 to E6 and store in F10.

Step 1. Select E10 and type Minimum.

Step 2. Select F10 and type =min(e2.e6) then press [Enter] key.

C.4 Sum: =sum(range) The function “=sum(range)” gives the sum of the data in a range of cells.

Example: Find the sum of the data in cells E2 to E6 and store in F11.

Step 1. Select E11 and type Sum.Step 2. Select F11 and type =sum(e2.e6) then press [Enter] key.

C.5 Average: =average(range) The function “=average(range)” gives the average (or the mean) of the data in a range of

cells.

Example: Find the average of the data in cells E2 to E6 and store in F12.

Step 1. Select E12 and type Mean.

Step 2. Select F12 and type =average(e2.e6) then press [Enter] key.

C.6 Standard Deviation

=stdev.s(range) for the sample standard deviation.

=stdev.p(range) for the population standard deviation.

The standard deviation of the data in a range of cells can be calculated using the function

“stdev.s(range)” or “=stdev.p(range)”. The function “=stdev.p(range)” is for the populationstandard deviation and =stdev.s(range) is for the sample standard deviation.

Example: Find the sample standard deviation of the data in cells E2 to E6 and store in F13.Step 1. Select E13 and type Standard Deviation.

Step 2. Select F13 and type =stdev.s(e2.e6) then press [Enter] key.




309

C.7 Standard Normal Distribution: =norm.s.dist( a, true)

This function gives the accumulated probability for standard normal distribution.

Example:

“=1-=norm.s.dist(1.96,true)” gives 0.025.

C.8 t-Distribution: =t.dist.rt( a, df)

This function gives the probability on the right-tailed for t-distribution.

Example:

“=t.dist.rtt(1.96,10)” gives 0.0392.

D. Using Insert Function Dialog

You can use Excel’s Insert Function dialog to get the above functions.

Step 1. Select the cell where you want to store the function result.

Step 2. Use one of the following to start Insert Function dialog.

C Click the function button f X on the toolbars.

C Or, click Formulas on the menu and then click Insert Function onthe sub-menu.

Step 3. Select a catalog and a function in Insert Function dialog box.

Example: Median

Step 1. Select E14 and type Median and then press [Enter] key.

Step 2. Select F14.

Step 3. Click the function button f X on the toolbars.Step 5. In Or select a category box, select Statistical .Step 6. In Select a function: box, select Median.

Step 7. Click OK .

Step 8. Type E2.E6 in the number 1 field. (Or select the cells using mouse). Step 9. Click OK .

Exercise:1. Select G12 and then use Insert Function dialog to find the mean for the data in E2 to E6.

2. Select G13 and then use Insert Function dialog to find the standard deviation for the data in

E2 to E6.

E. Data Analysis Tool

A summary of the descriptive statistics is available from Data Analysis Tool.

Step 1. Click Data tab;

Step 2. Click Data Analysis in the Analysis group;

Step 3. Select Descriptive Statistics in the Data Analysis window.




310

If Data Analysis item is not available under Data tab, you need to load the Analysis ToolPak .

Load the Analysis ToolPak :

Step 1. Click File tab, and then click Options.

Step 2. Click Add-ins, and then in the Manage box, select Excel Add-ins.Step 3. Click Go.

Step 4. In the Add-Ins available box, select the Analysis ToolPak check box, and

then click OK .

Example: Compute the descriptive statistics for the test scores in E2.E6.

Step 1. Click Data tab.Step 2. Click Data Analysis in the Analysis group.

Step 3. Click on Descriptive Statistics and click OK .

Step 4. In Input Range field, type E1.E6

Step 5. Check the box Labels in First Row.

Step 6. Click on Output Range field and type E18.Step 7. Check Summary Statistics and click OK .

excel guide appendix a

Documents