{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

ExcelFunctionReview

# ExcelFunctionReview - Basic Statistics with Microsoft Excel...

This preview shows pages 1–4. Sign up to view the full content.

Basic Statistics with Microsoft Excel 1 Excel Functions for Statistics DESCRIPTIVE STATISTICS: FREQUENCIES Using COUNTIF to Construct a Frequency Distribution The spreadsheet below shows how COUNTIF can be used to calculate how many times each country appears in the list in Column A. To calculate Frequency select D2 and enter the function: =COUNTIF(A\$2:A\$17, C2) A2:A17 is the range to be evaluated (need absolute row reference (\$) to make sure function will fill down correctly). C2 is the country to be counted. To calculate Relative Frequency , divide Frequency (D2) by Total (D7). To calculate Percent Frequency , multiply Relative Frequency (E2) by 100 . Note: Cells D2:F7 show the formulas used. Cells D11:F16 show the results.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Basic Statistics with Microsoft Excel 2 Using FREQUENCY to Construct a Frequency Distribution The FREQUENCY function involves the use of array formulas that provide multiple values (in this case the class frequencies) as output. 1. Select cells D2:D5 where the frequencies are to appear. 2. Type the formula: =FREQUENCY (A2:A16,{9,14,19,24}) 3. Press CTRL + SHIFT + ENTER and the array formula will be entered into each of the cells D2:D6. Because we entered an array formula, the formula that Excel displays in each of the cells is the same, but the values are not - they are the frequencies for each class. The class upper limits in the second argument of the FREQUENCY function tell Excel which frequency to put in each cell within the range of the array formula. Using PivotTable Report to Construct a Crosstabulation PivotTable Report provides a general tool for summarising the data for two or more variables simultaneously. 1. Select the Data menu and choose PivotTable and PivotChart Report . 2. Choose Microsoft Excel list or database . 3. Choose PivotTable and select Next . 4. Enter the data range in the Range box and select Next . 5. Select New Worksheet (if required). 6. Click on the Layout button. 7. Drag the field buttons to the ROW, COLUMN and DATA sections of the diagram as appropriate. 8. Double click the Sum of … field button in the data section. 9. Choose Count under Summarise by: and click OK . 10. Click OK and the Finish .
Basic Statistics with Microsoft Excel 3 DESCRIPTIVE STATISTICS: NUMERICAL METHODS The following spreadsheet shows the functions used to calculate the mean, median, mode, percentiles and quartiles for a cell range named hours . Displaying the Mean, Median, and Mode =AVERAGE(array), =MEDIAN(array), =MODE(array) Percentiles and Quartiles =PERCENTILE(array, percentile) where percentile is between 0 and 1 =QUARTILE(array, quart) where quart is 1, 2, 3 or 4 1 st Quartile = 25 th Percentile, 2 nd Quartile = 50 th Percentile, 3 rd Quartile = 75 th Percentile Deviation and Squared Deviation About the Mean The sum of the deviations about the mean will always equal 0.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### Page1 / 12

ExcelFunctionReview - Basic Statistics with Microsoft Excel...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online