ExcelFunctionReview

ExcelFunctionReview - Basic Statistics with Microsoft Excel...

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

View Full Document Right Arrow Icon
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.
Background image of page 1

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

View Full DocumentRight Arrow Icon
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 .
Background image of page 2
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. To calculate the square of a value enter =A1^2.
Background image of page 3

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

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 08/30/2011 for the course ECON 101 taught by Professor Rm during the Spring '11 term at Rochester.

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 Right Arrow Icon
Ask a homework question - tutors are online