M131 Project Fall 2013

m131_project_fall_13.xlsx

I actually have the form for excel with the actual format and requirements to send you.The given information shows the Ages and Heights for 100 men and 100 women. For each gender:1, Create a single bar graph using two different colors to represent the male and female categories. Arrange each gender according to the following age groups: 10-19, 20-29, 30-39, 40-49, 50-59, and 60-69. Then for each age group and gender, find the average height for all the subjects in each age group. The bar graph should show both men and women! Please see the tab labeled “Bar Chart Hint” for a couple of helpful hints on how to approach this chart.2. Create a pie chart (one for each gender) to show the percentage of heights in each age group for each gender. Please see the tab labeled “Pie Chart Hint” for a couple of helpful hints on how to approach this chart.3. Find the mean, median, mode, midrange, and standard deviation for each gender’s heights given. See the tab labeled “Summary Statistics” for a couple of helpful hint on how to approach this summary4. The submission for this assignment is a single Excel file.

Instructions

Height

65

49

69

72 69

19

70 70 55

19 72

62

70 65

49 72 49 54
29 70 33 60
42

29 57

42 72

62

72 49

66 38 56

48

41 62

71 38 58

29 65

61

70 45 55

30

29 56

69 32 62

49 68 44 62

68 32 60

29 66 33 55
40 70 38 55
45 71 28 59
42 73

59

43 73

57

35 73 44 58
38 68 49 62
44

48 61

40 69 30 60
47 73 41 63
27 71

58

45 69 36 57

69 42 62

28 70 37 62

68 40 64

30 71 32 62
38 69

55

38 75 28 64
34 70 45 58
30 72 33 61

70 30 63

31 70 45 62
27 69 42 56
29 71 38 54
27 73 45 58

75 47 63

19 71 59 69
56

64

64 77 64 62
31 77 23 68
42 76 38 60
49 71 43 68
50 76 19

38 71 32 66

71 57 56

29 76

69

57 73 35 57
50

22 70

69 77 34 70
29 74 52 58
60 76 30 64
22 74 54 60
18 75 69 61
43 75 39 69

72 59 62

28 72 32 65
29 75 60 64
21 77 63 70
21 75 48 64
19 76 44 57
22 76 43 70
41 76 46 52
28 71 62

74 63 61

76 38 62

26 74 50 68
66 76 44 68
56 75 39 64
19 73 26 70
68 72 28 65
58 71 24 55
47 74

55

43 72 61 55
46 77 68 65

76 51 53

73 55 69

21 77 18 62
65 74 35 52
42 76 23 68
55 71 36 60
41 75 36 69
58 74 63 70
53 75 47 61
67 75 39 67
26 76 19 60

																																																																																																																																																																																																																																																																																																																																																																																							Men						Women
		Age						Height			Age
											35																								65																								49												54
																																72												48																																																62
																													64																																																69																											58																								57
																							19																																									7		0																								61
																							42												33																					63
																													29																																	55
																	32			80															66
														41																																							76													70																					56
																													60
											40
											50																																							71
																																			68
																	44
																																38															59
																				30
																																75
																				45
											36
																				28
																										73
											27
																	43
														47
					37
								67
					51
								31
								46
								34
											39
											26
																				77									23
											52
											22
								18
																							74
											21
								53
		17
					24
		25
		16
		20

Instructions:
The given information shows the Ages and Heights for 100 men and 100 women. For each gender:
1, Create a single bar graph using two different colors to represent the male and female categories. Arrange each gender according to the following age groups: 10-19, 20-29, 30-39, 40-49, 50-59, and 60-69. Then for each age group and gender, find the average height for all the subjects in each age group. The bar graph should show both men and women! Please see the tab labeled “Bar Chart Hint” for a couple of helpful hints on how to approach this chart.
2. Creat a pie chart (one for each gender) to show the percentage of heights in each age group for each gender. Please see the tab labeled “

Pie Chart Hint

” for a couple of helpful hints on how to approach this chart.
3. Find the mean, median, mode, midrange, and standard deviation for each gender’s heights given. See the tab labeled “

Summary Statistics

” for a couple of helpful hint on how to approach this summary
4. The submission for this assignment is a single Excel file. I encourage as much discussion as necessary!
Have fun,
Jeff

Bar Graph Hint

Men Women
Age Height Age Height
35 65 49 54
65 72 48 62
64 69 58 57

19 70 49 61

Men Women

42 69 33 63

72 69 29 55

32 80 19 66

41 76 70 56

70 70 55 60

19 72 40 62

50 71 70 65
49 72 49 54
29 70 33 60
42 68 29 57
42 72 44 62
38 72 49 59
30 66 38 56
48 75 41 62
45 71 38 58
29 65 36 61
28 70 45 55
30 73 29 56
27 69 32 62
49 68 44 62
43 68 32 60
29 66 33 55
40 70 38 55
45 71 28 59
42 73 47 59
43 73 37 57
35 73 44 58
38 68 49 62
44 67 48 61
40 69 30 60
47 73 41 63
27 71 51 58
45 69 36 57
31 69 42 62
28 70 37 62
46 68 40 64
30 71 32 62
38 69 34 55
38 75 28 64
34 70 45 58
30 72 33 61
39 70 30 63
31 70 45 62
27 69 42 56
29 71 38 54
27 73 45 58
26 75 47 63
19 71 59 69
56 77 23 64
64 77 64 62
31 77 23 68
42 76 38 60
49 71 43 68
50 76 19 52
38 71 32 66
22 71 57 56
29 76 18 69
57 73 35 57
50 74 22 70
69 77 34 70
29 74 52 58
60 76 30 64
22 74 54 60
18 75 69 61
43 75 39 69
21 72 59 62
28 72 32 65
29 75 60 64
21 77 63 70
21 75 48 64
19 76 44 57
22 76 43 70
41 76 46 52
28 71 62 53
17 74 63 61
24 76 38 62
26 74 50 68
66 76 44 68
56 75 39 64
19 73 26 70
68 72 28 65
58 71 24 55
47 74 25 55
43 72 61 55
46 77 68 65
16 76 51 53
20 73 55 69
21 77 18 62
65 74 35 52
42 76 23 68
55 71 36 60
41 75 36 69
58 74 63 70
53 75 47 61
67 75 39 67
26 76 19 60

	Age Group
	Teens
	Twenties
	Thirties
	Forties
	Fifties
	Sixties

Hint for Bar Graph
1. For the bar graph, you will need a table similar to what you see to the left. For each of the red cells, you will want to compute the average of all men/women who fit each decade based on your data. I suggest you start by organizing the age/heights of each gender in ascending order by age!! If, for example, all your males between 10 and 19 are in cells A3:A7, a simple approach to calculate the average height of these subjects would be to put the following equation in cell J7: =average(b3:b7). Continue this approach to fill out the remainder of the table.
2. From here, you can use Excel to build your chart. Be sure to show both men and women on the same chart distinguished by different colors. Depending on which version of Excel you are currently using, launching the chart wizard is slightly different. If you are unsure how to launch your chart wizard, you can use the HELP function within Excel or I encourage you to GOOGLE “Microsoft Chart in Excel XX (the XX being your particular version of Excel). From there, you will be entering the values in your table; Excel will do the rest!

Pie Chart Hint

Men Women
Age Height Age Height
35 65 49 54
65 72 48 62
64 69 58 57

19 70 49 61 Age Group Men Women
42 69 33 63 Teens
72 69 29 55 Twenties
32 80 19 66 Thirties
41 76 70 56 Forties
70 70 55 60 Fifties
19 72 40 62 Sixties
50 71 70 65

0 0

49 72 49 54
29 70 33 60
42 68 29 57
42 72 44 62
38 72 49 59
30 66 38 56
48 75 41 62
45 71 38 58
29 65 36 61
28 70 45 55
30 73 29 56
27 69 32 62
49 68 44 62
43 68 32 60
29 66 33 55
40 70 38 55
45 71 28 59
42 73 47 59
43 73 37 57
35 73 44 58
38 68 49 62
44 67 48 61
40 69 30 60
47 73 41 63
27 71 51 58
45 69 36 57
31 69 42 62
28 70 37 62
46 68 40 64
30 71 32 62
38 69 34 55
38 75 28 64
34 70 45 58
30 72 33 61
39 70 30 63
31 70 45 62
27 69 42 56
29 71 38 54
27 73 45 58
26 75 47 63
19 71 59 69
56 77 23 64
64 77 64 62
31 77 23 68
42 76 38 60
49 71 43 68
50 76 19 52
38 71 32 66
22 71 57 56
29 76 18 69
57 73 35 57
50 74 22 70
69 77 34 70
29 74 52 58
60 76 30 64
22 74 54 60
18 75 69 61
43 75 39 69
21 72 59 62
28 72 32 65
29 75 60 64
21 77 63 70
21 75 48 64
19 76 44 57
22 76 43 70
41 76 46 52
28 71 62 53
17 74 63 61
24 76 38 62
26 74 50 68
66 76 44 68
56 75 39 64
19 73 26 70
68 72 28 65
58 71 24 55
47 74 25 55
43 72 61 55
46 77 68 65
16 76 51 53
20 73 55 69
21 77 18 62
65 74 35 52
42 76 23 68
55 71 36 60
41 75 36 69
58 74 63 70
53 75 47 61
67 75 39 67
26 76 19 60

Totals

Hint for Pie Chart:
1. For each of the red cells, you will want to compute the percentage of all men/women who fit each decade based on your data. Similar to what you did with the pie chart, I suggest you start by putting each set of data in ascending order. The sum of the Men and Women columns should each equal 100% once you have completed the computations. Remember, you are counting the number of observations in each age group and dividing it by 100.
2. From here, you can use the Chart Wizard in Excel to build your chart. Make sure you build separate pie chart for each gender.

Summary Statistics

Men Women

Measure

Mean

Median

Mode

Midrange

Standard Deviation

This portion should be self-explanatory based on your understanding of each of these measures! You are to consider all heights for each gender. There are a couple ways to easily come up with these values:
1. Within Excel, there are individual, built-in functions for each of these measures. For example the AVERAGE function will compute the mean/average of any string of cells you enter.
2. Within Excel, you may use the Analysis Toolpak and the Descriptive Statistics function that resides within that feature to compute all these values.
Once again, you can find details on using the built in functions or the Analysis Toolpak options by using the HELP feature or by GOOGLING your particular version of Excel. If all else fails, please post a note on the discussion board or send and email and I will gladly give you some pointers!