vui
Uploaded by
13 SLIDES
233 VUES
130LIKES

Excel Pivot Tables for Data Analysis

DESCRIPTION

Learn to use Pivot Tables in Excel for analyzing data, including creating crosstabs and conditional expectations. Explore examples and techniques for statistical analysis.

1 / 13

Télécharger la présentation

Excel Pivot Tables for Data Analysis

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. EC339: Lecture 4 Chapter 3: Pivot Tables or… Conditional Expectations? (a.k.a. Crosstabs)

  2. First, get Excel up to speed • Install Necessary Add-ins: • Execute Tools: Add-ins (to get the Add-Ins Manager dialog box, as shown to the right) and check the Analysis ToolPak and Analysis ToolPak VBA options. • Now is also a good time to install the Histogram add-in. Once again, execute Tools: Add-ins and click Browse. Navigate to the Histogram.xla file and click OK.

  3. Open IndianaFTWorkers.xls • Look in Basic Tools\Internet\CPS folder • Try to find out if wage discrimination is prevalent in the state of Indiana • Start by summarizing data • Descriptive Statistics: see DescStat • Use “Decriptive Statistics” for numeric variables • Use “PivotTable” for categorical variables

  4. Univariate Statistics • Histogram • Try using built-in Excel Histogram function • Also use BH Histogram function • See my website or BH for Excel Macros • You must reinstall these each time you use them on campus computers that are not your own • “Frequency Tables” are done using PivotTable and Descriptive Statistics

  5. Multivariate Statistics • Crosstabs • Display relationships and patterns between multiple variables. • Display the value of one or more variables conditional on the value of one or more other variables • Excel allows for ‘grouping’

  6. Crosstabs • Education and Income • Race and Income • Sex and Income • Race and Education • Sex and Education

  7. Examples of Conditional Averages From Indiana Note here there is an example of a weighted average. The overall average of $37,174 is the average of the individuals (or weighted by observations here), rather than the average of the averages, which would be $65,901. See WeightAvg sheet

  8. Crosstab of Income by Race and Education Given you are black and have a high school diploma, the expected value or average income would be $27,700. If you are black, and have some college, the expected value of income rises to $33,268, a difference of $5,568. Note, the difference for a white HS grad and some college is $5,840

  9. Conditional Mean Function • Using EastNorthCentralFTWorkers.xls • Conditional Mean: the average (mean) of y given x. Often written in expectations form E(y|x). The vertical line signifies given or conditional. • Group Annual Earnings by Education • What is average annual income of individuals with 12 years of education? • What about if you know their income is between $20,000 and $39,999?

  10. SPSS Example • First step, get data into SPSS • To make similar tables in SPSS, use AnalyzeCompare Means • Put the variable you want to summarize (annual income here) as dependent, and put the different variables you want in the x-tab as two separate layers.

More Related