Excel Worksheet Calculate.pdf

About Global Documents

Global Documents provides you with documents from around the globe on a variety of topics for your enjoyment.

Global Documents utilizes edocr for all its document needs due to edocr's wonderful content features. Thousands of professionals and businesses around the globe publish marketing, sales, operations, customer service and financial documents making it easier for prospects and customers to find content.

data click box EXCEL select data sheet dialog box EXCEL Instruction Range enter confidence

EXCEL Instruction I, 9/22/01
1
Getting Descriptive Statistics with EXCEL.
If one wishes to obtain descriptive statistics for the weight variable,
1) First open or create the data sheet and then click on the Tools on the menu bar and select
Data Analysis. The following dialog box will appear on the screen.
2) Select Descriptive Statistics and click OK. The following dialog box will appear. Click on
the right end of the Input Range Box that has a red arrow mark and select a range of data to
be used for calculating descriptive statistics.
When one clicks on the right end of the Input Range Box that has a red arrow mark, the
following dialog will be shown.
3)   Go to the data sheet and select a range of data (by clicking and holding left button of the
mouse and dragging it through the data you wish to select) to be processed and click on the
right end lower corner of the dialog box that has a red arrow sign pointing down for entering
the data.  You should see things similar to the following pictures.
EXCEL Instruction I, 9/22/01
2
4)    Check Columns if the data is from a column and check the Summary statistics or other
statistics that you wish to obtain and click OK. The summary statistics will be shown on a
different worksheet. Click on that worksheet to see the statistics.
EXCEL Instruction I, 9/22/01
3
Use EXCEL to make a histogram.
1) From the descriptive statistics of the data we can define and enter the Bin values that are the
upper boundaries of classes for the histogram. The data sheet should look like the following
image. Bin values is in the right most column.
2) In EXCEL click Tools in the menu bar and select Data Analysis. You’ll see the following
dialog box. Select the Histogram and click OK.
You should then see the following dialog box.
EXCEL Instruction I, 9/22/01
4
3)    Click the square box (has blue, red and white colors in it) on the right of the Input Range
box. You should see the following box. Select the range of data to be processed and click on
the square gray box on the lower right corner of the box that has a red arrow pointing down.
You should be able to see similar thing in the following picture on your screen while
selecting a range of data.
3) After the range of data is selected, you should see the following dialog box. Select a range of
Bin values using the similar way as selecting the data range. You should then see the
following information in the dialog box for histogram.
4) Check the Chart Output box and click OK the histogram will be shown in another worksheet.
EXCEL Instruction I, 9/22/01
5
Use EXCEL to calculate the confidence interval estimate for mean with a known σ.
1) Open the Form_OneSample.XLS file. (An EXCEL worksheet that contains formula. Down
load the file from our class web page on WebCT.)
2) Prepare you data column in Data worksheet.
3) Click B4 cell and highlight the block of text in the =AVERAGE() function or simply just
enter the sample mean.
4)   Click the Data sheet and select the range of data to be used and hit Enter key.
5) Enter the sample size, confidence level and σ in column B, and the confidence limits and
margin of error will be calculated.
EXCEL Instruction I, 9/22/01
6
Use EXCEL to calculate the confidence interval estimate for mean with unknown σ.
1)    Open the Form_OneSample.XLS file. (An EXCEL worksheet that contains formula.)
2) Prepare you data column in Data worksheet.
3) Click D4 cell and highlight the block of text in the =AVERAGE() function, or simply enter
the sample mean.
4) Click the Data sheet and select the range of data to be used and hit Enter key.
5) Click D5 and select a range of data in the Data sheet or enter the sample standard deviation.
6) Enter the sample size in D3, confidence level in D6, and the confidence limits and margin of
error will be calculated.
EXCEL Instruction I, 9/22/01
7
Use EXCEL to calculate the confidence interval estimate for proportion.
Method I
1)    Open the Form_CI.XLS file.
2) Enter sample size in F3, and the total count of individual in the category of interest in F4, and
the confidence level in F6. The confidence interval will be calculated.
Method II (Data need to be 0’s and 1’s)
1) Open the Form_CI.XLS file.
2) Enter the sample size in F3 and find the sum of data in the Data sheet use a SUM function for
F4.
3) Enter the confidence level in F6, and the confidence limits and margin of error will be
calculated.