Final Project
TableOfContents
| Table of contents with hyperlinks for this document | |
| Excluding standard worksheets that come with the original data | |
| Sheet name | Purpose |
| NotesOnDataPrep!A1 | Tips and tricks for students in doing data analysis in Excel |
| SalaryPivotTable!A1 | Using a histogram of salary to compare other variables in terms of chunks of salary |
| DescriptiveStatsForFrequency!A1 | Example of producing descriptive stats for chunks of a numeric variable (grouping, frequency table as 'categories') |
| VariableDescriptiveStatsPHStat!A1 | Example of descriptive stats produced by PHStat and then edited, items removed that are not needed |
| Correlations!A1 | Instructor reference for how all variables are inter-related |
| RegressionAge!A1 | Example of regression output highighting output to pay attention to |
| SPSSRegressionAllEnter!A1 | Instructor reference - regressing salary on all independent variables to discern stongest, independent predictors |
| PivotTableCreatePercentPolygon!A1 | Example of comparing distributions between two categories with different number of cases or different scales, i.e., version of percent polygon |
| Analysis results | |
| Gender univariate descriptive statistics | |
| GenderAnalysis!A1 | Gender/Salary; Gender/Job Grade Classification analysis; Gender/other independent variables |
| Salary histogram, distribution | |
| Compare gender/salary descriptive statistics | |
| GenderCompareDescriptives!A1 | Comparison Table gender descriptive statistics in terms of all variables. This might be something worth doing. |
| EthnicitySalaryAnalysis!A1 | Ethnicity/Salary analysis |
| OptionalEthnicitySalaryAnalysis!A1 | Optional ethnicity/salary analysis - distribution of ethnicity over chunks of salary, percent polygon |
| EthnicityJGClassAnalysis!A1 | Ethnicity/Job Grade Classification analysis |
| AgeSalaryAnalysis!A1 | Age/Salary analysis |
| AgeJobGradeClassAnalysis!A1 | Age/Job grade classification analysis |
| YearsWorkedSalaryAnalysis!A1 | Years worked/Salary analysis |
| Years worked/Job grade classification analysis | |
| Relationship between endogenous variables | |
| Job grade classification/Salary analysis | |
| Relationship between independent variables | |
| PercentPolygonGenderYearsWorked!A1 | Compare years worked distribution by gender; Example of comparing distributions between two categories with different number of cases or different scales, i.e., version of percent polygon |
| Standard sheets that come with the data | |
| Variable INFO'!A1 | Information on variables |
| Human Resources DATA'!A1 | Data |
| Cross-Class-Table'!A1 | |
| Summary Table'!A1 | |
| Histogram!A1 | |
| % Polygons 2 Groups'!A1 | |
| Freq. & % Distribution'!A1 |
Variable INFO
| TableOfContents!A1 | |||||||||
| The data are a random sample of 120 responses to a survey conducted by the VP of Human Resources at a large company. | |||||||||
| Source: | INFO 501 class at Montclair State University | ||||||||
| Variables | |||||||||
| Salary | in thousands of dollars (K) | ||||||||
| Age | in years | ||||||||
| YrsWork | in years | ||||||||
| JGClass | job-grade classification of 1, 3, 5, 7, 9, 11 (lowest skill job to highest skill job) | ||||||||
| Ethnicity | 1=Minority | 0=Not Minority | |||||||
| Gender | (Male, Female) | ||||||||
| Named ranges created in this worksheet - use these names to address the data more quickly then manually selecting data | |||||||||
| Use the name of the range in dialog boxes rather than clicking and dragging ranges. | |||||||||
| Example of using names instead of manual ranges: | |||||||||
| 50681.1320754717 | Female salary | ||||||||
| 56465.671641791 | male salary | ||||||||
| -10% | Percent difference |
Human Resources DATA
| TableOfContents!A1 | |||||||
| Salary | Age | YrsWork | JGClass | EthnicityCODE | Gender code | Ethnicity | Gender |
| $31,200 | 19 | 1 | 3 | 1 | 0 | Minority | Female |
| $40,400 | 28 | 3 | 3 | 0 | 0 | Not Minority | Female |
| $42,600 | 29 | 3 | 5 | 1 | 0 | Minority | Female |
| $39,800 | 26 | 2 | 5 | 0 | 0 | Not Minority | Female |
| $33,300 | 22 | 2 | 3 | 1 | 0 | Minority | Female |
| $35,600 | 28 | 4 | 3 | 0 | 0 | Not Minority | Female |
| $34,200 | 38 | 6 | 3 | 1 | 0 | Minority | Female |
| $43,600 | 35 | 3 | 5 | 0 | 0 | Not Minority | Female |
| $37,600 | 28 | 5 | 5 | 1 | 0 | Minority | Female |
| $34,600 | 27 | 6 | 3 | 1 | 0 | Minority | Female |
| $37,700 | 36 | 1 | 3 | 0 | 0 | Not Minority | Female |
| $48,100 | 28 | 3 | 5 | 0 | 0 | Not Minority | Female |
| $38,900 | 36 | 2 | 5 | 0 | 0 | Not Minority | Female |
| $46,700 | 33 | 10 | 5 | 1 | 0 | Minority | Female |
| $58,000 | 49 | 3 | 9 | 0 | 0 | Not Minority | Female |
| $52,200 | 38 | 6 | 5 | 0 | 0 | Not Minority | Female |
| $46,500 | 45 | 3 | 7 | 0 | 0 | Not Minority | Female |
| $52,300 | 47 | 2 | 7 | 0 | 0 | Not Minority | Female |
| $50,000 | 30 | 8 | 5 | 0 | 0 | Not Minority | Female |
| $54,200 | 39 | 6 | 7 | 1 | 0 | Minority | Female |
| $47,000 | 60 | 10 | 5 | 0 | 0 | Not Minority | Female |
| $57,500 | 47 | 3 | 7 | 0 | 0 | Not Minority | Female |
| $47,700 | 62 | 4 | 9 | 0 | 0 | Not Minority | Female |
| $49,000 | 39 | 9 | 5 | 0 | 0 | Not Minority | Female |
| $70,100 | 53 | 5 | 7 | 0 | 0 | Not Minority | Female |
| $60,000 | 57 | 7 | 7 | 0 | 0 | Not Minority | Female |
| $48,600 | 43 | 2 | 7 | 0 | 0 | Not Minority | Female |
| $57,000 | 61 | 5 | 7 | 0 | 0 | Not Minority | Female |
| $57,700 | 33 | 7 | 7 | 0 | 0 | Not Minority | Female |
| $47,800 | 44 | 8 | 7 | 0 | 0 | Not Minority | Female |
| $47,600 | 51 | 3 | 5 | 0 | 0 | Not Minority | Female |
| $59,000 | 49 | 6 | 9 | 0 | 0 | Not Minority | Female |
| $72,000 | 47 | 3 | 7 | 0 | 0 | Not Minority | Female |
| $43,500 | 53 | 7 | 7 | 1 | 0 | Minority | Female |
| $70,000 | 39 | 12 | 9 | 0 | 0 | Not Minority | Female |
| $54,100 | 48 | 3 | 5 | 0 | 0 | Not Minority | Female |
| $55,500 | 49 | 5 | 5 | 0 | 0 | Not Minority | Female |
| $60,000 | 54 | 6 | 7 | 0 | 0 | Not Minority | Female |
| $52,300 | 48 | 4 | 3 | 0 | 0 | Not Minority | Female |
| $67,000 | 50 | 5 | 7 | 0 | 0 | Not Minority | Female |
| $58,000 | 50 | 15 | 7 | 1 | 0 | Minority | Female |
| $38,700 | 50 | 3 | 3 | 0 | 0 | Not Minority | Female |
| $62,100 | 51 | 3 | 7 | 0 | 0 | Not Minority | Female |
| $65,500 | 53 | 9 | 9 | 0 | 0 | Not Minority | Female |
| $43,200 | 62 | 3 | 5 | 0 | 0 | Not Minority | Female |
| $67,500 | 57 | 12 | 11 | 1 | 0 | Minority | Female |
| $56,700 | 56 | 6 | 7 | 0 | 0 | Not Minority | Female |
| $39,600 | 58 | 3 | 5 | 0 | 0 | Not Minority | Female |
| $39,200 | 60 | 14 | 5 | 0 | 0 | Not Minority | Female |
| $58,500 | 61 | 8 | 7 | 0 | 0 | Not Minority | Female |
| $39,800 | 64 | 5 | 5 | 0 | 0 | Not Minority | Female |
| $67,500 | 66 | 2 | 9 | 0 | 0 | Not Minority | Female |
| $68,900 | 67 | 5 | 9 | 0 | 0 | Not Minority | Female |
| $39,600 | 24 | 1 | 5 | 0 | 1 | Not Minority | Male |
| $33,400 | 20 | 2 | 3 | 1 | 1 | Minority | Male |
| $42,100 | 24 | 2 | 5 | 1 | 1 | Minority | Male |
| $54,100 | 31 | 1 | 7 | 1 | 1 | Minority | Male |
| $46,100 | 27 | 4 | 5 | 0 | 1 | Not Minority | Male |
| $56,300 | 39 | 2 | 7 | 1 | 1 | Minority | Male |
| $45,600 | 37 | 3 | 5 | 1 | 1 | Minority | Male |
| $48,500 | 35 | 2 | 7 | 1 | 1 | Minority | Male |
| $54,600 | 30 | 7 | 7 | 0 | 1 | Not Minority | Male |
| $50,100 | 39 | 4 | 7 | 0 | 1 | Not Minority | Male |
| $47,100 | 37 | 6 | 5 | 0 | 1 | Not Minority | Male |
| $46,800 | 40 | 2 | 5 | 0 | 1 | Not Minority | Male |
| $44,100 | 28 | 3 | 7 | 1 | 1 | Minority | Male |
| $56,100 | 42 | 4 | 7 | 0 | 1 | Not Minority | Male |
| $37,500 | 31 | 5 | 3 | 0 | 1 | Not Minority | Male |
| $45,500 | 33 | 9 | 5 | 0 | 1 | Not Minority | Male |
| $43,500 | 59 | 9 | 7 | 1 | 1 | Minority | Male |
| $45,000 | 49 | 5 | 7 | 0 | 1 | Not Minority | Male |
| $67,500 | 58 | 7 | 7 | 0 | 1 | Not Minority | Male |
| $62,000 | 54 | 6 | 9 | 1 | 1 | Minority | Male |
| $56,700 | 41 | 4 | 7 | 0 | 1 | Not Minority | Male |
| $48,100 | 32 | 6 | 3 | 0 | 1 | Not Minority | Male |
| $45,000 | 50 | 2 | 7 | 1 | 1 | Minority | Male |
| $50,000 | 45 | 5 | 7 | 1 | 1 | Minority | Male |
| $75,500 | 40 | 12 | 9 | 0 | 1 | Not Minority | Male |
| $66,000 | 56 | 4 | 11 | 1 | 1 | Minority | Male |
| $62,200 | 40 | 14 | 9 | 1 | 1 | Minority | Male |
| $47,500 | 59 | 5 | 7 | 1 | 1 | Minority | Male |
| $53,000 | 56 | 8 | 5 | 0 | 1 | Not Minority | Male |
| $56,700 | 48 | 7 | 7 | 0 | 1 | Not Minority | Male |
| $54,900 | 42 | 3 | 5 | 0 | 1 | Not Minority | Male |
| $53,200 | 38 | 4 | 7 | 1 | 1 | Minority | Male |
| $45,600 | 36 | 9 | 7 | 1 | 1 | Minority | Male |
| $56,300 | 49 | 2 | 5 | 0 | 1 | Not Minority | Male |
| $43,300 | 49 | 2 | 3 | 0 | 1 | Not Minority | Male |
| $46,400 | 36 | 5 | 7 | 1 | 1 | Minority | Male |
| $64,300 | 54 | 3 | 5 | 0 | 1 | Not Minority | Male |
| $61,000 | 36 | 7 | 9 | 0 | 1 | Not Minority | Male |
| $48,100 | 38 | 9 | 7 | 1 | 1 | Minority | Male |
| $38,600 | 48 | 6 | 5 | 1 | 1 | Minority | Male |
| $56,000 | 47 | 14 | 7 | 1 | 1 | Minority | Male |
| $60,500 | 51 | 9 | 7 | 0 | 1 | Not Minority | Male |
| $64,500 | 49 | 7 | 7 | 1 | 1 | Minority | Male |
| $52,500 | 51 | 9 | 5 | 0 | 1 | Not Minority | Male |
| $79,000 | 52 | 15 | 11 | 0 | 1 | Not Minority | Male |
| $76,500 | 52 | 7 | 9 | 0 | 1 | Not Minority | Male |
| $60,000 | 49 | 9 | 9 | 0 | 1 | Not Minority | Male |
| $62,500 | 54 | 8 | 9 | 1 | 1 | Minority | Male |
| $72,200 | 55 | 15 | 11 | 1 | 1 | Minority | Male |
| $61,500 | 56 | 9 | 7 | 0 | 1 | Not Minority | Male |
| $68,700 | 56 | 10 | 9 | 0 | 1 | Not Minority | Male |
| $82,300 | 57 | 15 | 11 | 0 | 1 | Not Minority | Male |
| $67,800 | 57 | 5 | 9 | 0 | 1 | Not Minority | Male |
| $61,000 | 58 | 12 | 7 | 1 | 1 | Minority | Male |
| $67,800 | 59 | 7 | 9 | 1 | 1 | Minority | Male |
| $81,100 | 59 | 15 | 11 | 0 | 1 | Not Minority | Male |
| $45,600 | 60 | 16 | 5 | 0 | 1 | Not Minority | Male |
| $77,500 | 62 | 10 | 11 | 0 | 1 | Not Minority | Male |
| $68,000 | 63 | 9 | 9 | 0 | 1 | Not Minority | Male |
| $73,000 | 63 | 15 | 11 | 0 | 1 | Not Minority | Male |
| $68,000 | 68 | 8 | 9 | 0 | 1 | Not Minority | Male |
| $43,200 | 69 | 10 | 5 | 0 | 1 | Not Minority | Male |
| $76,000 | 70 | 9 | 9 | 0 | 1 | Not Minority | Male |
| $69,500 | 71 | 18 | 9 | 0 | 1 | Not Minority | Male |
| $39,900 | 72 | 8 | 5 | 1 | 1 | Minority | Male |
| $64,200 | 73 | 15 | 9 | 1 | 1 | Minority | Male |
| $46,500 | 74 | 10 | 5 | 0 | 1 | Not Minority | Male |
NotesOnDataPrep
| Tips and tricks |
| 1. It will make the student's life easier to create named ranges in the data for the ranges they need. Simply sort, highlight the range, and in the box upper left, type in a name. Use that name in functions and formulas (e.g., quartile(), or descriptive stats - you can use named ranges in PHStat and Data Analysis Toolpack) |
| 2. Note that Pivot tables can provide all descriptive statistics except median, quartiles, IQR. If Zscores indicate that there is an outlier on one side, students should not be using the mean, but as a work around, you can ask them to note that, discuss what it means and then use the mean/SD anyway; OR you can require them to manually create those separately from the pivot table (or don't use a pivot table, use the data analysis toolpack or PHSTat). |
| 3. Instructions for producing a histogram/frequency table with a Pivot Table: |
| a. Create a pivot table using the numeric variable (age) as the row label |
| b. Group the row label - Group button on ribbon. Choose chunks in dialog box. |
| make sure you click in the data, not the header, or the button will be greyed out |
| Play with the beginning, end value and chunks to make bins common sense, i.e., 1-10, not 1-11 |
| c. Add anything you want to the Values box. Add items multiple times to get multiple stats about the same item. |
| d. To work with data, it is frequently easier to copy pivot table data and paste as - paste as values. |
| e. Word of warning: If you divide data into subcategories - chunks of salary for women, men - if there are no values for a category, Excel won't list it - you have to manually put a zero in for the value. |
| 4. Getting Excel stuff into Word for a report: It might be easier to paste as a picture object - easier to manipulate. |
PercentDifference
| Count of Gender | Column Labels | ||
| Row Labels | Female | Male | Grand Total |
| 3 | 16.98% | 5.97% | 10.83% |
| 5 | 33.96% | 25.37% | 29.17% |
| 7 | 33.96% | 35.82% | 35.00% |
| 9 | 13.21% | 22.39% | 18.33% |
| 11 | 1.89% | 10.45% | 6.67% |
| Grand Total | 100.00% | 100.00% | 100.00% |
| Count of Gender | Column Labels | ||
| Row Labels | Female | Male | |
| 3 | 17% | 6% | 96% |
| 5 | 34% | 25% | 29% |
| 7 | 34% | 36% | 5% |
| 9 | 13% | 22% | 52% |
| 11 | 2% | 10% | 139% |
Percent difference in Male to female
Proportions
3 5 7 9 11 0.95950920245398763 0.28951115329852861 5.3268765133171914E-2 0.51582278481012667 1.3881278538812787 3 5 7 9 11 3 5 7 9 11
3 5 7 9 11 1
Job Levels
LevelGender
| Row Labels | Average of Salary |
| 3 | 38484.6153846154 |
| Female | 37555.5555555556 |
| Male | 40575 |
| 5 | 46345.7142857143 |
| Female | 45250 |
| Male | 47505.8823529412 |
| 7 | 54809.5238095238 |
| Female | 57194.4444444444 |
| Male | 53020.8333333333 |
| 9 | 65740.9090909091 |
| Female | 62371.4285714286 |
| Male | 67313.3333333333 |
| 11 | 74825 |
| Female | 67500 |
| Male | 75871.4285714286 |
| Grand Total | 53910.8333333333 |
Average salary of gender on each level
Total
Female Male Female Male Female Male Female Male Female Male 3 5 7 9 11 37555.555555555555 40575 45250 47505.882352941175 57194.444444444445 53020.833333333336 62371.428571428572 67313.333333333328 67500 75871.428571428565
BiVariateDistributionChart
| Average of Salary | Column Labels | ||
| Row Labels | Female | Male | Grand Total |
| 30000-39999 | 36938.4615384615 | 37800 | 37177.7777777778 |
| 40000-49999 | 45878.5714285714 | 45680 | 45761.7647058823 |
| 50000-59999 | 55533.3333333333 | 54321.4285714286 | 54948.275862069 |
| 60000-69999 | 64812.5 | 64578.9473684211 | 64648.1481481481 |
| 70000-79999 | 70700 | 75671.4285714286 | 74180 |
| 80000-90000 | 81700 | 81700 | |
| Grand Total | 50681.1320754717 | 56465.671641791 | 53910.8333333333 |
Gender average salary comparison by salary level
Female 30000-39999 40000-49999 50000-59999 60000-69999 70000-79999 80000-90000 36938.461538461539 4587 8.571428571428 55533.333333333336 64812.5 70700 Male 30000-39999 40000-49999 50000-59999 60000-69999 70000-79999 80000-90000 37800 45680 54321.428571428572 64578.947368421053 75671.428571428565 81700
Salary ranges
Average salary
GenderDescriptiveStats
| TableOfContents!A1 | |||||||||
| Gender/salary comparison - descriptive statistics | |||||||||
| Run descriptive statistics twice - once with named range "malesalary" and again with "femalesalary", then copy and paste them next to each other | |||||||||
| Column1 | Column1 | Comparing male and female salary | <== table title centered across columns | ||||||
| Statistic | Male | Female | <==row headers differentiated from data | ||||||
| Mean | 56465.671641791 | Mean | 50681.1320754717 | Mean | $ 56,466 | $ 50,681 | <==number formatting | ||
| Standard Error | 1474.68546001 | Standard Error | 1515.0288634913 | Standard Error | $ 1,475 | $ 1,515 | <==all statistics that are NOT being used are REMOVED | ||
| Median | 56000 | Median | 49000 | Median | $ 56,000 | $ 49,000 | |||
| Mode | 45600 | Mode | 39800 | Standard Deviation | $ 12,071 | $ 11,030 | |||
| Standard Deviation | 12070.8207177324 | Standard Deviation | 11029.5766116484 | Range | $ 48,900 | $ 40,800 | |||
| Sample Variance | 145704712.799638 | Sample Variance | 121651560.232221 | Minimum | $ 33,400 | $ 31,200 | |||
| Kurtosis | -0.8044449928 | Kurtosis | -0.9313514963 | Maximum | $ 82,300 | $ 72,000 | |||
| Skewness | 0.3244762148 | Skewness | 0.1887693789 | Count | 67 | 53 | |||
| Range | 48900 | Range | 40800 | ||||||
| Minimum | 33400 | Minimum | 31200 | ||||||
| Maximum | 82300 | Maximum | 72000 | ||||||
| Sum | 3783200 | Sum | 2686100 | ||||||
| Count | 67 | Count | 53 |
GenderDescriptiveStats (2)
| TableOfContents!A1 | ||||||||||
| Gender/salary comparison - descriptive statistics | ||||||||||
| Run descriptive statistics twice - once with named range "malesalary" and again with "femalesalary", then copy and paste them next to each other | ||||||||||
| Table 1 | <== Start with labeleling each table by number, sequentially (charts too - call them "Figure x") | |||||||||
| Column1 | Column1 | Comparing male and female salary | <== table title centered across columns or left justified, meaningful, not abstract | |||||||
| Statistic | Malea | Female | <==row headers differentiated from data (bold); lines above and below column headers | |||||||
| Mean | 56465.671641791 | Mean | 50681.1320754717 | Count | 67 | 53 | <==If you want to show subsets of statistics, use an italicized header, indent following | |||
| Standard Error | 1474.68546001 | Standard Error | 1515.0288634913 | Measures of central tendency | <==indented to show part of type of statistic | |||||
| Median | 56000 | Median | 49000 | Mean | $ 56,466 | $ 50,681 | <==number formatting | |||
| Mode | 45600 | Mode | 39800 | Median | $ 56,000 | $ 49,000 | ||||
| Standard Deviation | 12070.8207177324 | Standard Deviation | 11029.5766116484 | Measures of central variance | 90% | |||||
| Sample Variance | 145704712.799638 | Sample Variance | 121651560.232221 | Standard Deviation | $ 12,071 | $ 11,030 | ||||
| Kurtosis | -0.8044449928 | Kurtosis | -0.9313514963 | Minimum | $ 33,400 | $ 31,200 | 0.9375 | |||
| Skewness | 0.3244762148 | Skewness | 0.1887693789 | Maximum | $ 82,300 | $ 72,000 | 0.7162162162 | |||
| Range | 48900 | Range | 40800 | Range | $ 48,900 | $ 40,800 | 0.9137387481 | |||
| Minimum | 33400 | Minimum | 31200 | Test for outliers | ||||||
| Maximum | 82300 | Maximum | 72000 | Zscore of Minimum | -1.9 | -1.8 | ||||
| Sum | 3783200 | Sum | 2686100 | Zscore of Maximum | 2.1 | 1.9 | ||||
| Count | 67 | Count | 53 | Source: Random sample of 120 RJCorp employees, June 2015 | <==Note: All statistics that are NOT being used are REMOVED | |||||
| a Notation if needed (superscript used after header "Male" above as an example | ||||||||||
SalaryDistributionHistogram
| Table of contents | |
| Salary histogram/distribution | |
| Row Labels | Count of Salary |
| 30000-39999 | 18 |
| 40000-49999 | 34 |
| 50000-59999 | 29 |
| 60000-69999 | 27 |
| 70000-79999 | 10 |
| 80000-90000 | 2 |
| Grand Total | 120 |
| Row Labels | Count of Salary |
| 30-39K | 18 |
| 40-49K | 34 |
| 50-59K | 29 |
| 60-69K | 27 |
| 70-79K | 10 |
| 80-89K | 2 |
Histogram of salary
Total 30000-39999 40000-49999 50000-59999 60000-69999 70000-79999 80000-90000 18 34 29 27 10 2
Salary levels (in dollars)
Number of employees
Figure 1: Distribution of salaries in RJ Corp
Count of Salary 30-39K 40-49K 50-59K 60-69K 70-79K 80-89K 18 34 29 27 10 2
Salary
Number of employees
GenderDescriptiveStatistics (2
| Categorical variable descriptive statistics produced through a pivot table | |||||
| Pivot table output | |||||
| Row Labels | Count of Gender | Count of Gender2 | |||
| Female | 53 | 44.17% | 12% | ||
| Male | 67 | 55.83% | |||
| Grand Total | 120 | 100.00% | |||
| Copy, paste special, paste as a value: | |||||
| Row Labels | Count of Gender | Count of Gender2 | |||
| Female | 53 | 0.4416666667 | |||
| Male | 67 | 0.5583333333 | |||
| Grand Total | 120 | 1 | |||
| Format in an attractive manner by standards of good table formatting (see Chapter 9, or PowerPoint) | |||||
| Note: I've used format as a table from the Home ribbon, the selected "Convert to Range" button to get rid of special drop downs. | |||||
| Table 1 | |||||
| Gender descriptive statistics | <==Title centered across columns or left justified, bold; meaningful | ||||
| Gender | Count | Percent of total | <==Column/row headers formatted to distinguish from data, centered | ||
| Female | 53 | 44% | <==Number formatting used - percentage formatting in this case | ||
| Male | 67 | 56% | |||
| Grand Total | 120 | 100% | |||
| 0.2641509434 |
SalaryDescriptiveStatistics (2
| Table of contents | |||||
| Salary descriptive statistics | |||||
| Column1 | Table 2 | ||||
| Salary descriptive statistics | <== table title centered across columns or left justified; meaningful | ||||
| Mean | 53910.8333333333 | Statistic | Figures | <==row / column headers differentiated from data | |
| Standard Error | 1088.9229612112 | Mean | $ 53,911 | <==number formatting | |
| Median | 53100 | Median | $ 53,100 | <==all statistics that are NOT being used are REMOVED | |
| Mode | 48100 | Standard Deviation | $ 11,929 | ||
| Standard Deviation | 11928.5533848133 | Range | $ 51,100 | ||
| Sample Variance | 142290385.854342 | Minimum | $ 31,200 | ||
| Kurtosis | -0.6661524346 | Maximum | $ 82,300 | ||
| Skewness | 0.3069257671 | Count | 120 | ||
| Range | 51100 | ||||
| Minimum | 31200 | ||||
| Maximum | 82300 | ||||
| Sum | 6469300 | ||||
| Count | 120 |
Formatted output from Data Analysis Toolpack, Descriptive Statistics function
GenderAgeSalary
| Average of Salary | Column Labels | Average of Salary | Column Labels | |||
| Row Labels | Female | Male | Row Labels | Female | Male | |
| <20 | $31,200 | 30000-39999 | 36938.4615384615 | 37800 | ||
| 20-29 | $39,000 | $41,060 | 40000-49999 | 45878.5714285714 | 45680 | |
| 30-39 | $48,564 | $49,447 | 50000-59999 | 55533.3333333333 | 54321.4285714286 | |
| 40-49 | $54,873 | $54,840 | 60000-69999 | 64812.5 | 64578.9473684211 | |
| 50-59 | $56,638 | $63,914 | 70000-79999 | 70700 | 75671.4285714286 | |
| 60-69 | $52,089 | $62,550 | 80000-90000 | 81700 | ||
| 70-80 | $59,220 |
Comparing gender average salary by age group
Female < 20 20-29 30-39 40-49 50-59 60-69 70-80 31200 39000 48563.63636363636 54872.727272727272 56638.461538461539 52088.888888888891 Male < 20 20-29 30-39 40-49 50-59 60-69 70-80 41060 49446.666666666664 54840 63914.285714285717 62550 59220
Age groups
Average salary
GenderSalaryAvg
| Row Labels | Average of Salary | Count of Salary | Percent difference |
| Female | $50,681 | 53 | -10.24% |
| Male | $56,466 | 67 | |
| Grand Total | $53,911 | 120 | |
| Gender | Average of Salary | Count of Salary | Percent difference |
| Female | $ 50,681 | 53 | -10% |
| Male | $ 56,466 | 67 |
Average of Salary Female Male 50681.132075471702 56465.671641791043
AgeAnalysis
| Pivot table producing descriptive statistics for chunks of age (age histogram) | |||||||||||
| TableOfContents!A1 | |||||||||||
| Row Labels | Count of Age | Average of Salary | StdDev of Salary | Min of Salary | Max of Salary | ||||||
| <20 | 1 | $31,200 | ERROR:#DIV/0! | $31,200 | $31,200 | ||||||
| 20-29 | 13 | $39,792 | $4,773 | $33,300 | $48,100 | ||||||
| 30-39 | 26 | $49,073 | $7,724 | $34,200 | $70,000 | ||||||
| 40-49 | 26 | $54,854 | $8,235 | $38,600 | $75,500 | ||||||
| 50-59 | 34 | $61,132 | $11,434 | $38,700 | $82,300 | ||||||
| 60-69 | 15 | $56,273 | $13,295 | $39,200 | $77,500 | ||||||
| 70-80 | 5 | $59,220 | $15,388 | $39,900 | $76,000 | ||||||
| Grand Total | 120 | $53,911 | $11,929 | $31,200 | $82,300 | ||||||
| Row Labels | Count of Age | Average of Salary | StdDev of Salary | Min of Salary | Max of Salary | range | coefficient of variation | negative Zscore | positive Zscore | ||
| 15-24 | 5 | $ 35,920 | $ 4,670 | $ 31,200 | $ 42,100 | $ 10,900 | 13% | -1.01 | 1.32 | ||
| 25-34 | 17 | $ 44,888 | $ 6,832 | $ 34,600 | $ 57,700 | $ 23,100 | 15% | -1.51 | 1.88 | ||
| 35-44 | 26 | $ 51,165 | $ 9,192 | $ 34,200 | $ 75,500 | $ 41,300 | 18% | -1.85 | 2.65 | ||
| 45-54 | 35 | $ 56,926 | $ 9,876 | $ 38,600 | $ 79,000 | $ 40,400 | 17% | -1.86 | 2.24 | ||
| 55-64 | 28 | $ 59,293 | $ 12,956 | $ 39,200 | $ 82,300 | $ 43,100 | 22% | -1.55 | 1.78 | ||
| 65-75 | 9 | $ 60,411 | $ 13,371 | $ 39,900 | $ 76,000 | $ 36,100 | 22% | -1.53 | 1.17 | ||
| Instructions: | |||||||||||
| 1. Create a pivot table using the numeric variable (age) as the row label | |||||||||||
| 2. Group the row label - Group button on ribbon. Choose chunks in dialog box. | |||||||||||
| 3. Add anything you want to the Values box. Add items multiple times to get multiple stats about the same item. | |||||||||||
| 4. To work with data, it is frequently easier to copy pivot table data and paste as - paste as values. |
copy this, paste as values below
Age Line Fit Plot
Salary 19 28 29 26 22 28 38 35 28 27 36 28 36 33 49 38 45 47 30 39 60 47 62 39 53 57 43 61 33 44 51 49 47 53 39 48 49 54 48 50 50 50 51 53 62 57 56 58 60 61 64 66 67 24 20 24 31 27 39 37 35 30 39 37 40 28 42 31 33 59 49 58 54 41 32 50 45 40 56 40 59 56 48 42 38 36 49 49 36 54 36 38 48 47 51 49 51 52 52 49 54 55 56 56 57 57 58 59 59 60 62 63 63 68 69 70 71 72 73 74 31200 40400 42600 39800 33300 35600 34200 43600 37600 34600 37700 48100 38900 46700 58000 52200 46500 52300 50000 54200 47000 57500 47700 49000 70100 60000 48600 57000 57700 47800 47600 59000 72000 43500 70000 54100 55500 60000 52300 67000 58000 38700 62100 65500 43200 67500 56700 39600 39200 58500 39800 67500 68900 39600 33400 42100 54100 46100 56300 45600 48500 54600 50100 47100 46800 44100 56100 37500 45500 43500 45000 67500 62000 56700 48100 45000 50000 75500 66000 62200 47500 53000 56700 54900 53200 45600 56300 43300 46400 64300 61000 48100 38600 56000 60500 64500 52500 79000 76500 60000 62500 72200 61500 68700 82300 67800 61000 67800 81100 45600 77500 68000 73000 68000 43200 76000 69500 39900 64200 46500 Predicted Salary 19 28 29 26 22 28 38 35 28 27 36 28 36 33 49 38 45 47 30 39 60 47 62 39 53 57 43 61 33 44 51 49 47 53 39 48 49 54 48 50 50 50 51 53 62 57 56 58 60 61 64 66 67 24 20 24 31 27 39 37 35 30 39 37 40 28 42 31 33 59 49 58 54 41 32 50 45 40 56 40 59 56 48 42 38 36 49 49 36 54 36 38 48 47 51 49 51 52 52 49 54 55 56 56 57 57 58 59 59 60 62 63 63 68 69 70 71 72 73 74 1Age
Salary
Simply create formulas here referencing values to the left
VariableDescriptiveStatsPHStat
| TableOfContents!A1 | ||||||
| PHStat ouput - Descriptive Statistics for HumanResources.xlsx | ||||||
| Descriptive Summary | ||||||
| Salary | Age | YrsWork | JGClass | Ethnicity | Gender | |
| Mean | $53,911 | 47 | 6.47 | 6.62 | 0.32 | 0.56 |
| Median | $53,100 | 49 | 6 | 7 | 0 | 1 |
| Mode | $48,100 | 49 | 3 | 7 | 0 | 1 |
| Minimum | $31,200 | 19 | 1 | 3 | 0 | 0 |
| Maximum | $82,300 | 74 | 18 | 11 | 1 | 1 |
| Range | $51,100 | 55 | 17 | 8 | 1 | 1 |
| Variance | 142290385.8543 | 167.7815 | 15.9485 | 4.5913 | 0.2182 | 0.2487 |
| Standard Deviation | $11,929 | 12.9531 | 3.9936 | 2.1427 | 0.4671 | 0.4987 |
| Coeff. of Variation | 22.13% | 27.56% | 61.76% | 32.38% | 147.51% | 89.31% |
| Skewness | 0.3069 | -0.0986 | 0.8545 | 0.1834 | 0.7982 | -0.2379 |
| Kurtosis | -0.6662 | -0.7283 | 0.0532 | -0.5082 | -1.3862 | -1.9766 |
| Count | 120 | 120 | 120 | 120 | 120 | 120 |
| Standard Error | 1088.9230 | 1.1824 | 0.3646 | 0.1956 | 0.0426 | 0.0455 |
| Descriptive statistics summary | ||||||
| Salary | Gender | |||||
| Mean | $53,911 | 0.56 | ||||
| Median | $53,100 | 1 | ||||
| Mode | $48,100 | 1 | ||||
| Minimum | $31,200 | 0 | ||||
| Maximum | $82,300 | 1 | ||||
| Range | $51,100 | 1 | ||||
| Standard Deviation | $11,929 | 0.4987 | ||||
| Coeff. of Variation | 22.13% | 89.31% | ||||
| Count | 120 | 120 | ||||
Students should get rid of anything that is not covered in the course and they don't understand in the output.
Tables should have headers differentiated, number formatting done, centered data.
GenderAnalysis
| TableOfContents!A1 | ||||||||||
| Analysis of varibles in terms of gender via pivot table | ||||||||||
| Row Labels | Count of Gender | Percent | Average of Salary | StdDev of Salary | Average of Age | Average of YrsWork | Average of JGClass | Average of EthnicityCODE | ||
| Female | 53 | 44.17% | $50,681 | $11,030 | 45.3 | 5.3 | 6.0 | 0.2 | ||
| Male | 67 | 55.83% | $56,466 | $12,071 | 48.3 | 7.4 | 7.1 | 0.4 | ||
| Grand Total | 120 | 100.00% | $53,911 | $11,929 | 47.0 | 6.5 | 6.6 | 0.3 | ||
| Instructions: | ||||||||||
| 1. Create a pivot table using the categorical variable (gender) as the row label | ||||||||||
| 2. Add anything you want to the Values box. Add items multiple times to get multiple stats about the same item. | ||||||||||
| 3. To work with data, it is frequently easier to copy pivot table data and paste as - paste as values. | ||||||||||
| Descriptive Statistics | ||||||||||
| Endogenous variables | Other independent variables | |||||||||
| Salary | JGClass | Age | YrsWork | EthnicityCODE | ||||||
| Female | Male | Female | Male | Female | Male | Female | Male | Female | Male | |
| Mean | $ 50,681 | $ 56,466 | 5.98 | 7.12 | 45.34 | 48.31 | 5.30 | 7.39 | 0.21 | 0.40 |
| Standard Error | $ 1,515 | $ 1,475 | 0.27 | 0.26 | 1.72 | 1.61 | 0.45 | 0.53 | 0.06 | 0.06 |
| Median | $ 49,000 | $ 56,000 | 5 | 7 | 48 | 49 | 5 | 7 | 0 | 0 |
| Mode | $ 39,800 | $ 45,600 | 5 | 7 | 28 | 49 | 3 | 9 | 0 | 0 |
| Standard Deviation | $ 11,030 | $ 12,071 | 1.99 | 2.14 | 12.55 | 13.21 | 3.24 | 4.30 | 0.41 | 0.49 |
| Range | $ 40,800 | $ 48,900 | 8 | 8 | 48 | 54 | 14 | 17 | 1 | 1 |
| Minimum | $ 31,200 | $ 33,400 | 3 | 3 | 19 | 20 | 1 | 1 | 0 | 0 |
| Maximum | $ 72,000 | $ 82,300 | 11 | 11 | 67 | 74 | 15 | 18 | 1 | 1 |
| Count | 53 | 67 | 53 | 67 | 53 | 67 | 53 | 67 | 53 | 67 |
| Coefficient of variance | 22% | 21% | 33% | 30% | 28% | 27% | 61% | 58% | 197% | 123% |
| Zscore negative | $ (1.77) | $ (1.91) | -1.50 | -1.92 | -2.10 | -2.14 | -1.33 | -1.48 | -0.51 | -0.82 |
| Zscore positive | $ 1.93 | $ 2.14 | 2.53 | 1.81 | 1.73 | 1.94 | 2.99 | 2.47 | 1.94 | 1.21 |
| Quartile 1 | $ 40,400 | $ 46,250 | 5 | 5 | 36 | 38 | 3 | 4 | na | na |
| Quartile 3 | $ 58,000 | $ 65,250 | 7 | 9 | 54 | 54 | 7 | 9 | na | na |
| Inter Quartile Range | $ 17,600 | $ 19,000 | 2 | 4 | 18 | 16 | 4 | 5 | na | na |
| Note: I created special named ranges in the data to make it easier - e.g., SalaryFemale, SalaryMale |
SalaryPivotTable
| TableOfContents!A1 | ||||||
| Analysis of variables in terms of chunks of salary | ||||||
| Row Labels | Count of Salary | Average of Age | Average of EthnicityCODE | Average of Gender code | Average of YrsWork | Average of JGClass |
| 30000-39999 | 18 | 38.17 | 0.44 | 0.28 | 4.22 | 4.00 |
| 40000-49999 | 34 | 43.65 | 0.38 | 0.59 | 5.59 | 5.71 |
| 50000-59999 | 29 | 44.93 | 0.24 | 0.48 | 5.55 | 6.45 |
| 60000-69999 | 27 | 56.19 | 0.33 | 0.70 | 8.00 | 8.33 |
| 70000-79999 | 10 | 53.30 | 0.10 | 0.70 | 10.30 | 9.40 |
| 80000-89999 | 2 | 58.00 | 0.00 | 1.00 | 15.00 | 11.00 |
| Grand Total | 120 | 47 | 0.3166666667 | 0.5583333333 | 6.4666666667 | 6.6166666667 |
| Instructions: | ||||||
| 1. Create a pivot table using the numeric variable (age) as the row label | ||||||
| 2. Group the row label - Group button on ribbon. Choose chunks in dialog box. | ||||||
| 3. Add anything you want to the Values box. Add items multiple times to get multiple stats about the same item. | ||||||
| 4. To work with data, it is frequently easier to copy pivot table data and paste as - paste as values. |
GenderCompareDescriptives
| TableOfContents!A1 | ||||||||||
| Table comparing descriptve statistics for all variables in terms of gender | ||||||||||
| Salary | Age | YrsWork | JGClass | EthnicityCODE | ||||||
| Female | Male | Female | Male | Female | Male | Female | Male | Female | Male | |
| Mean | $ 50,681 | $ 56,466 | 45.3 | 48.3 | 5.3 | 7.4 | 6.0 | 7.1 | 0.2 | 0.4 |
| Standard Error | $ 1,515 | $ 1,475 | 1.7 | 1.6 | 0.4 | 0.5 | 0.3 | 0.3 | 0.1 | 0.1 |
| Median | $ 49,000 | $ 56,000 | 48 | 49 | 5 | 7 | 5 | 7 | 0 | 0 |
| Mode | $ 39,800 | $ 45,600 | 28 | 49 | 3 | 9 | 5 | 7 | 0 | 0 |
| Standard Deviation | $ 11,030 | $ 12,071 | 12.5 | 13.2 | 3.2 | 4.3 | 2.0 | 2.1 | 0.4 | 0.5 |
| Sample Variance | 121651560.232221 | 145704712.799638 | 157.3824383164 | 174.5517865219 | 10.5224963716 | 18.513794663 | 3.9419448476 | 4.591587517 | 0.1676342525 | 0.2442333786 |
| Kurtosis | -0.9313514963 | -0.8044449928 | -0.92511818 | -0.6818428647 | 1.0936677151 | -0.4368448489 | -0.4548349394 | -0.5676589436 | 0.2105423988 | -1.8936805556 |
| Skewness | 0.1887693789 | 0.3244762148 | -0.2357663046 | -0.0428389974 | 1.1727443433 | 0.5747426633 | 0.2109272442 | 0.1068805146 | 1.4846023258 | 0.4046946723 |
| Range | $ 40,800 | $ 48,900 | 48 | 54 | 14 | 17 | 8 | 8 | 1 | 1 |
| Minimum | $ 31,200 | $ 33,400 | 19 | 20 | 1 | 1 | 3 | 3 | 0 | 0 |
| Maximum | $ 72,000 | $ 82,300 | 67 | 74 | 15 | 18 | 11 | 11 | 1 | 1 |
| Sum | 2686100 | 3783200 | 2403 | 3237 | 281 | 495 | 317 | 477 | 11 | 27 |
| Count | 53 | 67 | 53 | 67 | 53 | 67 | 53 | 67 | 53 | 67 |
| Male | ||||||||||
| Salary | Age | YrsWork | JGClass | EthnicityCODE | ||||||
| Mean | 56465.671641791 | Mean | 48.3134328358 | Mean | 7.3880597015 | Mean | 7.1194029851 | Mean | 0.4029850746 | |
| Standard Error | 1474.68546001 | Standard Error | 1.6140788534 | Standard Error | 0.5256665231 | Standard Error | 0.2617845621 | Standard Error | 0.0603761071 | |
| Median | 56000 | Median | 49 | Median | 7 | Median | 7 | Median | 0 | |
| Mode | 45600 | Mode | 49 | Mode | 9 | Mode | 7 | Mode | 0 | |
| Standard Deviation | 12070.8207177324 | Standard Deviation | 13.211804817 | Standard Deviation | 4.3027659317 | Standard Deviation | 2.1427989913 | Standard Deviation | 0.4941997355 | |
| Sample Variance | 145704712.799638 | Sample Variance | 174.5517865219 | Sample Variance | 18.513794663 | Sample Variance | 4.591587517 | Sample Variance | 0.2442333786 | |
| Kurtosis | -0.8044449928 | Kurtosis | -0.6818428647 | Kurtosis | -0.4368448489 | Kurtosis | -0.5676589436 | Kurtosis | -1.8936805556 | |
| Skewness | 0.3244762148 | Skewness | -0.0428389974 | Skewness | 0.5747426633 | Skewness | 0.1068805146 | Skewness | 0.4046946723 | |
| Range | 48900 | Range | 54 | Range | 17 | Range | 8 | Range | 1 | |
| Minimum | 33400 | Minimum | 20 | Minimum | 1 | Minimum | 3 | Minimum | 0 | |
| Maximum | 82300 | Maximum | 74 | Maximum | 18 | Maximum | 11 | Maximum | 1 | |
| Sum | 3783200 | Sum | 3237 | Sum | 495 | Sum | 477 | Sum | 27 | |
| Count | 67 | Count | 67 | Count | 67 | Count | 67 | Count | 67 |
PivotTableCreatePercentPolygon
| TableOfContents!A1 | ||||||
| Pivot table used to create percent polygon - comparing percents of males vs. females in terms of chunks of age | ||||||
| Row Labels | Count of Age | Count of Age2 | ||||
| Female | 53 | 44.17% | Female | Male | ||
| <20 | 1 | 1.89% | 15-24 | 3.77% | 4.48% | |
| 20-29 | 8 | 15.09% | 25-34 | 18.87% | 10.45% | |
| 30-39 | 11 | 20.75% | 35-44 | 18.87% | 23.88% | |
| 40-49 | 11 | 20.75% | 45-54 | 33.96% | 25.37% | |
| 50-59 | 13 | 24.53% | 55-64 | 20.75% | 25.37% | |
| 60-69 | 9 | 16.98% | 65-75 | 3.77% | 10.45% | |
| Male | 67 | 55.83% | ||||
| 20-29 | 5 | 7.46% | ||||
| 30-39 | 15 | 22.39% | ||||
| 40-49 | 15 | 22.39% | ||||
| 50-59 | 21 | 31.34% | ||||
| 60-69 | 6 | 8.96% | ||||
| 70-80 | 5 | 7.46% | ||||
| Grand Total | 120 | 100.00% | ||||
| Instructions | ||||||
| 1. Pivot table created using gender and then age as row labels | ||||||
| 2. Group age row labels | ||||||
| 3. Create a count column (not necessary) | ||||||
| 4. Drag age again to the values box. | ||||||
| 5. Chage values - click Show Values As, choose Percent Of Parent Row Total | ||||||
| 6. Copy data, paste as values, then create a line chart with that | ||||||
| - you will have to check the row labels - if there are no values in a chunk, Excel will not show the chunk. Simply type it in manually and insert a value of zero. | ||||||
| Comparing counts of gender by bins of age | ||||||
| male | female | |||||
| <20 | 0 | 1 | ||||
| 20-34 | 10 | 11 | ||||
| 35-49 | 25 | 19 | ||||
| 50-64 | 25 | 20 | ||||
| 65-80 | 7 | 2 |
Comparing gender by age
male < 20 20-34 35-49 50-64 65-80 0 10 25 25 7 female < 20 20-34 35-49 50-64 65-80 1 11 19 20 2
PercentPolygonGenderYearsWorked
| TableOfContents!A1 | |||||||||
| Compare distributions of male vs. female in terms of years worked | |||||||||
| Row Labels | Count of YrsWork | Count of YrsWork2 | Years worked | Male | Female | ||||
| Female | 53 | 44.17% | 1-3 | 0.2089552239 | 0.4150943396 | ||||
| 1-4 | 25 | 47.17% | 4-6 | 0.2388059701 | 0.320754717 | ||||
| 5-8 | 20 | 37.74% | 7-9 | 0.3134328358 | 0.1509433962 | ||||
| 9-12 | 6 | 11.32% | 10-12 | 0.0895522388 | 0.0754716981 | ||||
| 13-16 | 2 | 3.77% | 13-15 | 0.1194029851 | 0.0377358491 | ||||
| Male | 67 | 55.83% | 16-18 | 0.0298507463 | 0 | ||||
| 1-4 | 20 | 29.85% | |||||||
| 5-8 | 21 | 31.34% | |||||||
| 9-12 | 16 | 23.88% | |||||||
| 13-16 | 9 | 13.43% | |||||||
| 17-20 | 1 | 1.49% | |||||||
| Grand Total | 120 | 100.00% | |||||||
| Instructions | |||||||||
| 1. Pivot table created using gender and then age as row labels | |||||||||
| 2. Group age row labels | |||||||||
| 3. Create a count column (not necessary) | |||||||||
| 4. Drag age again to the values box. | |||||||||
| 5. Chage values - click Show Values As, choose Percent Of Parent Row Total | |||||||||
| 6. Copy data, paste as values, then create a line chart with that | |||||||||
| - you will have to check the row labels - if there are no values in a chunk, Excel will not show the chunk. Simply type it in manually and insert a value of zero. | |||||||||
| years worked | male | female | |||||||
| 1-4 | 20 | 25 | 30% | 47% | |||||
| 5-8 | 21 | 20 | 31% | 38% | |||||
| 9-12 | 16 | 6 | 24% | 11% | |||||
| 13-16 | 9 | 2 | 13% | 4% | |||||
| 17-20 | 1 | 0 | 1% | 0% | |||||
| total | 67 | 53 |
Comparing percents in years worked by gender
Male 1-3 4-6 7-9 10-12 13-15 16-18 0.20895522388059701 0.23880597014925373 0.31343283582089554 8.9552238805970144E-2 0.11940298507462686 2.9850746268656716E-2 Female 1-3 4-6 7-9 10-12 13-15 16-18 0.41509433962264153 0.32075471698113206 0.15094339622641509 7.5471698113207544E-2 3.7735849056603772E-2 0% Of Each Age Category per Age Grouping
Male 1-3 4-6 7-9 10-12 13-15 16-18 0.20895522388059701 0.23880597014925373 0.31343283582089554 8.9552238805970144E-2 0.11940 298507462686 2.9850746268656716E-2 Female 1-3 4-6 7-9 10-12 13-15 16-18 0.41509433962264153 0.32075471698113206 0.15094339622641509 7.5471698113207544E-2 3.7735849056603772E-2 0Years worked
Overall percentage
Comparing counts in years worked by gender
male 1-4 5-8 9-12 13-16 17-20 20 21 16 9 1 female 1-4 5-8 9-12 13-16 17-20 25 20 6 2 0
EthnicitySalaryAnalysis
| TableOfContents!A1 | |||||
| Ethnicity and salary | |||||
| Row Labels | Count of Ethnicity | Average of Salary | StdDev of Salary2 | Max of Salary | Min of Salary2 |
| Minority | 38 | $50,097 | $11,216 | 72200 | 31200 |
| Not Minority | 82 | $55,678 | $11,899 | 82300 | 35600 |
| Grand Total | 120 | $53,911 | $11,929 | 82300 | 31200 |
| Row Labels | Count of Ethnicity | Average of Salary | StdDev of Salary2 | Max of Salary | Min of Salary2 |
| Minority | 38 | $50,097 | $11,216 | $72,200 | $31,200 |
| Not Minority | 82 | $55,678 | $11,899 | $82,300 | $35,600 |
| Grand Total | 120 | $53,911 | $11,929 | $82,300 | $31,200 |
| Non Minority | Minority | ||||
| Coefficient of variance | 21% | 22% | |||
| Zscore negative | -1.6873 | -1.6849 | |||
| Zscore positive | 2.2372 | 1.9706 | |||
| Range | $46,700 | $41,000 |
OptionalEthnicitySalaryAnalysis
| TableOfContents!A1 | |||||||
| Copy, Paste Values below: | |||||||
| Optional Ethnicity Salary Analiysis - percent polygon | Note: For minority, a row label is missing because there is no data, | ||||||
| you need to manually add that and input a value of zero | |||||||
| Column Labels | |||||||
| Count of Ethnicity | Count of Ethnicity2 | Minority | Non-Minority | ||||
| Row Labels | Minority | Not Minority | Minority | Not Minority | 30000-39999 | 21% | 12% |
| 30000-39999 | 8 | 10 | 21.05% | 12.20% | 40000-49999 | 34% | 26% |
| 40000-49999 | 13 | 21 | 34.21% | 25.61% | 50000-59999 | 18% | 27% |
| 50000-59999 | 7 | 22 | 18.42% | 26.83% | 60000-69999 | 24% | 22% |
| 60000-69999 | 9 | 18 | 23.68% | 21.95% | 70000-79999 | 3% | 11% |
| 70000-79999 | 1 | 9 | 2.63% | 10.98% | 80000-90000 | 0% | 2% |
| 80000-90000 | 2 | 0.00% | 2.44% | ||||
| Minority | Non-minority | ||||||
| 30000-39999 | 8 | 10 | |||||
| 40000-49999 | 13 | 21 | |||||
| 50000-59999 | 7 | 22 | |||||
| 60000-69999 | 9 | 18 | |||||
| 70000-79999 | 1 | 9 | |||||
| 80000-90000 | 0 | 2 |
Comparing % of non/minority by bins of salary
Minority 30000-39999 40000-49999 50000-59999 60000-69999 70000-79999 80000-90000 0.21052631578947367 0.34210526315789475 0.18421052631578946 0.23684210526315788 2.6315789473684209E-2 0 Non-Minority 30000-39999 40000-49999 50000-59999 60000-69999 70000-79999 80000-90000 0.12195121951219512 0.25609756097560976 0.26829268292682928 0.21951219512195122 0.10975609756097561 2.4390243902439025E-2Comparing non/minority counts by bins of salary
Minority 30000-39999 40000-49999 50000-59999 60000-69999 70000-79999 80000-90000 8 13 7 9 1 0 Non-minority 30000-39999 40000-49999 50000-59999 60000-69999 70000-79999 80000-90000 10 21 22 18 9 2
EthnicityJGClassAnalysis
| TableOfContents!A1 | |||||
| Ethnicity/Job Grade Classification analysis | |||||
| Row Labels | Count of JGClass | Average of JGClass4 | StdDev of JGClass3 | Min of JGClass2 | Max of JGClass |
| Minority | 38 | 6.6842105263 | 2.1573916077 | 3 | 11 |
| Not Minority | 82 | 6.5853658537 | 2.1485063403 | 3 | 11 |
| Grand Total | 120 | 6.6166666667 | 2.1427357575 | 3 | 11 |
| Non Minority | Minority | ||||
| Coefficient of variance | 33% | 32% | |||
| Zscore negative | -1.6688 | 2.0547 | |||
| Zscore positive | 2.0547 | -1.6688 | |||
| Range | 8 | 8 |
YearsWorkedSalaryAnalysis
| YearsWorkedSalaryAnalysis!A1 | ||||||||
| Years worked and salary | ||||||||
| Row Labels | Count of YrsWork | Average of Salary4 | StdDev of Salary2 | Max of Salary | Min of Salary | Average of JGClass | Average of EthnicityCODE | Average of Gender code |
| 1-4 | 45 | $ 48,476 | $ 9,496 | $ 72,000 | $ 31,200 | 5.67 | 0.29 | 0.44 |
| 5-8 | 41 | $ 54,300 | $ 10,909 | $ 76,500 | $ 34,200 | 6.61 | 0.34 | 0.51 |
| 9-12 | 22 | $ 58,150 | $ 11,703 | $ 77,500 | $ 43,200 | 7.36 | 0.27 | 0.73 |
| 13-16 | 11 | $ 64,800 | $ 14,319 | $ 82,300 | $ 39,200 | 8.82 | 0.45 | 0.82 |
| 17-20 | 1 | $ 69,500 | ERROR:#DIV/0! | $ 69,500 | $ 69,500 | 9.00 | 0.00 | 1.00 |
| Grand Total | 120 | $ 53,911 | $ 11,929 | $ 82,300 | $ 31,200 | 6.62 | 0.32 | 0.56 |
| Row Labels | Count of YrsWork | Average of Salary3 | StdDev of Salary2 | Max of Salary | Min of Salary | |||
| 1-4 | 45 | 48475.5555555556 | 9496.0794674711 | 72000 | 31200 | |||
| 5-8 | 41 | 54300 | 10908.8954527945 | 76500 | 34200 | |||
| 9-12 | 22 | 58150 | 11702.6553763299 | 77500 | 43200 | |||
| 13-16 | 11 | 64800 | 14319.1480193481 | 82300 | 39200 | |||
| 17-20 | 1 | 69500 | ERROR:#DIV/0! | 69500 | 69500 | |||
| 1-4 | 5-8 | 9-12 | 13-16 | 17-20 | ||||
| Coefficient of variance | 20% | 20% | 20% | 22% | ERROR:#DIV/0! | |||
| Zscore negative | ||||||||
| Zscore positive | ||||||||
| Range | ||||||||
AgeSalaryAnalysis
| TableOfContents!A1 | ||||||||
| Age/Salary Analysis | ||||||||
| Age and Salary | Age and other variables | |||||||
| Row Labels | Count of Age | Average of Salary4 | StdDev of Salary3 | Max of Salary2 | Min of Salary | Average of YrsWork | Average of Gender code | Average of JGClass |
| <20 | 1 | $ 31,200 | ERROR:#DIV/0! | $ 31,200 | $ 31,200 | 1 | 0 | 3 |
| 20-29 | 13 | $ 39,792 | $ 4,773 | $ 48,100 | $ 33,300 | 3.0769230769 | 0.3846153846 | 4.3846153846 |
| 30-39 | 26 | $ 49,073 | $ 7,724 | $ 70,000 | $ 34,200 | 5.7307692308 | 0.5769230769 | 5.8461538462 |
| 40-49 | 26 | $ 54,854 | $ 8,235 | $ 75,500 | $ 38,600 | 5.3076923077 | 0.5769230769 | 6.6153846154 |
| 50-59 | 34 | $ 61,132 | $ 11,434 | $ 82,300 | $ 38,700 | 7.7647058824 | 0.6176470588 | 7.7058823529 |
| 60-69 | 15 | $ 56,273 | $ 13,295 | $ 77,500 | $ 39,200 | 8.2666666667 | 0.4 | 7.4 |
| 70-80 | 5 | $ 59,220 | $ 15,388 | $ 76,000 | $ 39,900 | 12 | 1 | 7.4 |
| Grand Total | 120 | $ 53,911 | $ 11,929 | $ 82,300 | $ 31,200 | 6.5 | 0.6 | 6.6 |
| <20 | 20-34 | 35-49 | 50-64 | 65-80 | ||||
| Coefficient of variance | ||||||||
| Zscore negative | ||||||||
| Zscore positive | ||||||||
| Range |
AgeJobGradeClassAnalysis
| TableOfContents!A1 | ||||||
| Age and Job Grade Classification analysis | ||||||
| Row Labels | Count of JGClass | Average of JGClass5 | StdDev of JGClass4 | Max of JGClass3 | Min of JGClass2 | |
| <20 | 1 | 3 | ERROR:#DIV/0! | 3 | 3 | |
| 20-29 | 13 | 4.3846153846 | 1.2608503439 | 7 | 3 | |
| 30-39 | 26 | 5.8461538462 | 1.7132964178 | 9 | 3 | |
| 40-49 | 26 | 6.6153846154 | 1.6988684017 | 9 | 3 | |
| 50-59 | 34 | 7.7058823529 | 2.0820941056 | 11 | 3 | |
| 60-69 | 15 | 7.4 | 2.2928460169 | 11 | 5 | |
| 70-80 | 5 | 7.4 | 2.19089023 | 9 | 5 | |
| Grand Total | 120 | 6.6166666667 | 2.1427357575 | 11 | 3 | |
| <20 | 3 | ERROR:#DIV/0! | 3 | 3 | ||
| 20-34 | 4.7142857143 | 1.45405836 | 7 | 3 | ||
| 35-49 | 6.4090909091 | 1.702504063 | 9 | 3 | ||
| 50-64 | 7.5777777778 | 2.1583757455 | 11 | 3 | ||
| 65-80 | 7.6666666667 | 2 | 9 | 5 | ||
| <20 | 20-34 | 35-49 | 50-64 | 65-80 | ||
| Coefficient of variance | ||||||
| Zscore negative | ||||||
| Zscore positive | ||||||
| Range | ||||||
| Row Labels | Count of JGClass | Average of JGClass5 | StdDev of JGClass4 | Max of JGClass3 | Min of JGClass2 | |
| <20 | 1 | 3 | ERROR:#DIV/0! | 3 | 3 | |
| 20-34 | 21 | 4.7142857143 | 1.45405836 | 7 | 3 | |
| 35-49 | 44 | 6.4090909091 | 1.702504063 | 9 | 3 | |
| 50-64 | 45 | 7.5777777778 | 2.1583757455 | 11 | 3 | |
| 65-80 | 9 | 7.6666666667 | 2 | 9 | 5 | |
| Grand Total | 120 | 6.6166666667 | 2.1427357575 | 11 | 3 |
DataCopy
| TableOfContents!A1 | |||||||
| Salary | Age | YrsWork | JGClass | EthnicityCODE | Gender code | Ethnicity | Gender |
| 31200 | 19 | 1 | 3 | 1 | 0 | Minority | Female |
| 40400 | 28 | 3 | 3 | 0 | 0 | Not Minority | Female |
| 42600 | 29 | 3 | 5 | 1 | 0 | Minority | Female |
| 39800 | 26 | 2 | 5 | 0 | 0 | Not Minority | Female |
| 33300 | 22 | 2 | 3 | 1 | 0 | Minority | Female |
| 35600 | 28 | 4 | 3 | 0 | 0 | Not Minority | Female |
| 34200 | 38 | 6 | 3 | 1 | 0 | Minority | Female |
| 43600 | 35 | 3 | 5 | 0 | 0 | Not Minority | Female |
| 37600 | 28 | 5 | 5 | 1 | 0 | Minority | Female |
| 34600 | 27 | 6 | 3 | 1 | 0 | Minority | Female |
| 37700 | 36 | 1 | 3 | 0 | 0 | Not Minority | Female |
| 48100 | 28 | 3 | 5 | 0 | 0 | Not Minority | Female |
| 38900 | 36 | 2 | 5 | 0 | 0 | Not Minority | Female |
| 46700 | 33 | 10 | 5 | 1 | 0 | Minority | Female |
| 58000 | 49 | 3 | 9 | 0 | 0 | Not Minority | Female |
| 52200 | 38 | 6 | 5 | 0 | 0 | Not Minority | Female |
| 46500 | 45 | 3 | 7 | 0 | 0 | Not Minority | Female |
| 52300 | 47 | 2 | 7 | 0 | 0 | Not Minority | Female |
| 50000 | 30 | 8 | 5 | 0 | 0 | Not Minority | Female |
| 54200 | 39 | 6 | 7 | 1 | 0 | Minority | Female |
| 47000 | 60 | 10 | 5 | 0 | 0 | Not Minority | Female |
| 57500 | 47 | 3 | 7 | 0 | 0 | Not Minority | Female |
| 47700 | 62 | 4 | 9 | 0 | 0 | Not Minority | Female |
| 49000 | 39 | 9 | 5 | 0 | 0 | Not Minority | Female |
| 70100 | 53 | 5 | 7 | 0 | 0 | Not Minority | Female |
| 60000 | 57 | 7 | 7 | 0 | 0 | Not Minority | Female |
| 48600 | 43 | 2 | 7 | 0 | 0 | Not Minority | Female |
| 57000 | 61 | 5 | 7 | 0 | 0 | Not Minority | Female |
| 57700 | 33 | 7 | 7 | 0 | 0 | Not Minority | Female |
| 47800 | 44 | 8 | 7 | 0 | 0 | Not Minority | Female |
| 47600 | 51 | 3 | 5 | 0 | 0 | Not Minority | Female |
| 59000 | 49 | 6 | 9 | 0 | 0 | Not Minority | Female |
| 72000 | 47 | 3 | 7 | 0 | 0 | Not Minority | Female |
| 43500 | 53 | 7 | 7 | 1 | 0 | Minority | Female |
| 70000 | 39 | 12 | 9 | 0 | 0 | Not Minority | Female |
| 54100 | 48 | 3 | 5 | 0 | 0 | Not Minority | Female |
| 55500 | 49 | 5 | 5 | 0 | 0 | Not Minority | Female |
| 60000 | 54 | 6 | 7 | 0 | 0 | Not Minority | Female |
| 52300 | 48 | 4 | 3 | 0 | 0 | Not Minority | Female |
| 67000 | 50 | 5 | 7 | 0 | 0 | Not Minority | Female |
| 58000 | 50 | 15 | 7 | 1 | 0 | Minority | Female |
| 38700 | 50 | 3 | 3 | 0 | 0 | Not Minority | Female |
| 62100 | 51 | 3 | 7 | 0 | 0 | Not Minority | Female |
| 65500 | 53 | 9 | 9 | 0 | 0 | Not Minority | Female |
| 43200 | 62 | 3 | 5 | 0 | 0 | Not Minority | Female |
| 67500 | 57 | 12 | 11 | 1 | 0 | Minority | Female |
| 56700 | 56 | 6 | 7 | 0 | 0 | Not Minority | Female |
| 39600 | 58 | 3 | 5 | 0 | 0 | Not Minority | Female |
| 39200 | 60 | 14 | 5 | 0 | 0 | Not Minority | Female |
| 58500 | 61 | 8 | 7 | 0 | 0 | Not Minority | Female |
| 39800 | 64 | 5 | 5 | 0 | 0 | Not Minority | Female |
| 67500 | 66 | 2 | 9 | 0 | 0 | Not Minority | Female |
| 68900 | 67 | 5 | 9 | 0 | 0 | Not Minority | Female |
| 39600 | 24 | 1 | 5 | 0 | 1 | Not Minority | Male |
| 33400 | 20 | 2 | 3 | 1 | 1 | Minority | Male |
| 42100 | 24 | 2 | 5 | 1 | 1 | Minority | Male |
| 54100 | 31 | 1 | 7 | 1 | 1 | Minority | Male |
| 46100 | 27 | 4 | 5 | 0 | 1 | Not Minority | Male |
| 56300 | 39 | 2 | 7 | 1 | 1 | Minority | Male |
| 45600 | 37 | 3 | 5 | 1 | 1 | Minority | Male |
| 48500 | 35 | 2 | 7 | 1 | 1 | Minority | Male |
| 54600 | 30 | 7 | 7 | 0 | 1 | Not Minority | Male |
| 50100 | 39 | 4 | 7 | 0 | 1 | Not Minority | Male |
| 47100 | 37 | 6 | 5 | 0 | 1 | Not Minority | Male |
| 46800 | 40 | 2 | 5 | 0 | 1 | Not Minority | Male |
| 44100 | 28 | 3 | 7 | 1 | 1 | Minority | Male |
| 56100 | 42 | 4 | 7 | 0 | 1 | Not Minority | Male |
| 37500 | 31 | 5 | 3 | 0 | 1 | Not Minority | Male |
| 45500 | 33 | 9 | 5 | 0 | 1 | Not Minority | Male |
| 43500 | 59 | 9 | 7 | 1 | 1 | Minority | Male |
| 45000 | 49 | 5 | 7 | 0 | 1 | Not Minority | Male |
| 67500 | 58 | 7 | 7 | 0 | 1 | Not Minority | Male |
| 62000 | 54 | 6 | 9 | 1 | 1 | Minority | Male |
| 56700 | 41 | 4 | 7 | 0 | 1 | Not Minority | Male |
| 48100 | 32 | 6 | 3 | 0 | 1 | Not Minority | Male |
| 45000 | 50 | 2 | 7 | 1 | 1 | Minority | Male |
| 50000 | 45 | 5 | 7 | 1 | 1 | Minority | Male |
| 75500 | 40 | 12 | 9 | 0 | 1 | Not Minority | Male |
| 66000 | 56 | 4 | 11 | 1 | 1 | Minority | Male |
| 62200 | 40 | 14 | 9 | 1 | 1 | Minority | Male |
| 47500 | 59 | 5 | 7 | 1 | 1 | Minority | Male |
| 53000 | 56 | 8 | 5 | 0 | 1 | Not Minority | Male |
| 56700 | 48 | 7 | 7 | 0 | 1 | Not Minority | Male |
| 54900 | 42 | 3 | 5 | 0 | 1 | Not Minority | Male |
| 53200 | 38 | 4 | 7 | 1 | 1 | Minority | Male |
| 45600 | 36 | 9 | 7 | 1 | 1 | Minority | Male |
| 56300 | 49 | 2 | 5 | 0 | 1 | Not Minority | Male |
| 43300 | 49 | 2 | 3 | 0 | 1 | Not Minority | Male |
| 46400 | 36 | 5 | 7 | 1 | 1 | Minority | Male |
| 64300 | 54 | 3 | 5 | 0 | 1 | Not Minority | Male |
| 61000 | 36 | 7 | 9 | 0 | 1 | Not Minority | Male |
| 48100 | 38 | 9 | 7 | 1 | 1 | Minority | Male |
| 38600 | 48 | 6 | 5 | 1 | 1 | Minority | Male |
| 56000 | 47 | 14 | 7 | 1 | 1 | Minority | Male |
| 60500 | 51 | 9 | 7 | 0 | 1 | Not Minority | Male |
| 64500 | 49 | 7 | 7 | 1 | 1 | Minority | Male |
| 52500 | 51 | 9 | 5 | 0 | 1 | Not Minority | Male |
| 79000 | 52 | 15 | 11 | 0 | 1 | Not Minority | Male |
| 76500 | 52 | 7 | 9 | 0 | 1 | Not Minority | Male |
| 60000 | 49 | 9 | 9 | 0 | 1 | Not Minority | Male |
| 62500 | 54 | 8 | 9 | 1 | 1 | Minority | Male |
| 72200 | 55 | 15 | 11 | 1 | 1 | Minority | Male |
| 61500 | 56 | 9 | 7 | 0 | 1 | Not Minority | Male |
| 68700 | 56 | 10 | 9 | 0 | 1 | Not Minority | Male |
| 82300 | 57 | 15 | 11 | 0 | 1 | Not Minority | Male |
| 67800 | 57 | 5 | 9 | 0 | 1 | Not Minority | Male |
| 61000 | 58 | 12 | 7 | 1 | 1 | Minority | Male |
| 67800 | 59 | 7 | 9 | 1 | 1 | Minority | Male |
| 81100 | 59 | 15 | 11 | 0 | 1 | Not Minority | Male |
| 45600 | 60 | 16 | 5 | 0 | 1 | Not Minority | Male |
| 77500 | 62 | 10 | 11 | 0 | 1 | Not Minority | Male |
| 68000 | 63 | 9 | 9 | 0 | 1 | Not Minority | Male |
| 73000 | 63 | 15 | 11 | 0 | 1 | Not Minority | Male |
| 68000 | 68 | 8 | 9 | 0 | 1 | Not Minority | Male |
| 43200 | 69 | 10 | 5 | 0 | 1 | Not Minority | Male |
| 76000 | 70 | 9 | 9 | 0 | 1 | Not Minority | Male |
| 69500 | 71 | 18 | 9 | 0 | 1 | Not Minority | Male |
| 39900 | 72 | 8 | 5 | 1 | 1 | Minority | Male |
| 64200 | 73 | 15 | 9 | 1 | 1 | Minority | Male |
| 46500 | 74 | 10 | 5 | 0 | 1 | Not Minority | Male |
Cross-Class-Table
| TableOfContents!A1 | |||
| Cross Classification Table | |||
| Count of Ethnicity | Gender | ||
| Ethnicity | Female | Male | Grand Total |
| Minority | 11 | 27 | 38 |
| Not Minority | 42 | 40 | 82 |
| Grand Total | 53 | 67 | 120 |
Summary Table
| TableOfContents!A1 | |
| One-Way Summary Table | |
| Count of Ethnicity | |
| Ethnicity | Total |
| Minority | 38 |
| Not Minority | 82 |
| Grand Total | 120 |
Bar Chart
Bar Chart
Total Minority Not Minority 38 82Ethnicity
Histogram
| TableOfContents!A1 |
Freq. & % Distribution
| TableOfContents!A1 | ||||||
| Frequency Distribution for Salary | ||||||
| bins | midpts | Frequency | Percentage | |||
| 29999.9 | 0 | 0.0% | ||||
| 39999.9 | 35000 | 18 | 15.0% | |||
| 49999.9 | 45000 | 34 | 28.3% | |||
| 59999.9 | 55000 | 29 | 24.2% | |||
| 69999.9 | 65000 | 27 | 22.5% | |||
| 79999.9 | 75000 | 10 | 8.3% | |||
| 89999.9 | 85000 | 2 | 1.7% | |||
| Total | 120 | 100.0% |
% Polygons 2 Groups
| TableOfContents!A1 |
SideBySide Bar Chart
Side-By-Side Chart
Female Minority Not Minority 11 42 Male Minority Not Minority 27 40