Excel exercise

1,250-word count minimum with three scholarly sources in APA format.

  1. This is an Excel exercise where you chart the rank of your name to as far back as the data allow. If your name is unique and does not fall in the top 1,000, pick another name in your family (such as your mother or father).

Social Security Administration (SSA)

  • Each year the Social Security Administration (SSA) puts out a press release on the most popular baby names. This list is based on the names sent to the SSA when applying for a Social Security number. Search the SSA website for most popular baby names. https://www.ssa.gov/OACT/babynames/#ht=2

 

  • Go to the Web site and towards the bottom of the page choose the option for Popularity of a Name. Enter your name and search for up to 100 years. The website will give you the rank of your name for each of the past 100 years. It is very possible that some names fall in and out of favor over time. Years will be omitted if your name is not within the top 1,000. If your name does not show up, substitute the name of another family member.

 

  • The web table can be easily copied and pasted into Excel. Just grab the two columns (year and rank) and copy them. Then open Excel and paste the results in. It should go smoothly.

 

  • We will use Excel to create a chart of the rank of your name over the past 100 years. This is relatively easy to do in Excel. I am going to walk you through the steps.
Data and Sort
  • The data are in reverse order (newest to oldest). We want to sort by year, oldest to newest. You do this by grabbing both columns (with the headers) and selecting Data and Sort. Sort allows you to note that there are headers, choose the sorting variable, and whether it is ascending or descending. If you make a mistake, click Edit and Undo.

 

  • Grab the rank variable starting at the earliest year and then start inserting a graph (Insert, and choose the graph type – I recommend a line graph).

 

  • After the initial graph, you can select the Year column as x-axis by clicking on the Chart, selecting Chart Design, selecting Select Data, and clicking on the horizontal (Category) axis labels (look for the icon in the right corner to actually choose the column and rows for the x-axis).

 

  • For the rest of the graph, you are on your own to add a connecting line, title, and subtitles. This is dressing up the chart to show to others. Explore with right clicks or double clicks and you will find a way forward, or use Google to search “Excel how do I _____?”

 

  • Submit your graph.
  1. The following is a small data set of 25 observations. I want you to calculate some statistics by hand to cement the class material. Excel can be used to solve this problem.

 

  • Var: 28; 4; 27; 23; 17; 38; 21; 16; 28; 15; 23; 33; 34; 42; 42; 14; 14; 28; 22; 31; 18; 28; 17; 17; 30
  • Create a stem and leaf plot of the data (it is easy to do in Word by making a two column table and entering the numbers in there, bolding the stems).
  • Calculate the following (show your work): Mean, Median, and Mode.

The Organization for Economic Co-operation and Development (OECD)

  1. Below is the data for infant mortality for 34 OECD countries. The Organization for Economic Co-operation and Development (OECD) is an international economic organization of 34 countries founded in 1961 to stimulate economic progress and world trade. OECD’s website provided some data on infant mortality for 34 countries. Infant mortality (the rate of death of children under 1 year of age per 1,000 live births) is a measure of development. The table below has the data for 34 OECD countries.
  • Create a stem and leaf plot of the data.
  • Calculate the mean, median, and mode for this data.
  • Briefly describe the distribution—focus on the shape of the distribution and whether there are an outliers or strange values.
  • The sum of x Sum(x) for 34 OECD countries is 128.20.
  1. Each year the Academy of the Screen Actors Guild gives an award for the best actor and actress in a motion picture. The name and age of each has been recorded since 1996. The data for males and females is given below (the sample size, n =20). The sum of their age is also given.

stem and leaf plot

  • Construct a stem and leaf plot for each group to compare the distributions. You should use the same scale on both graphs to make a better comparison.
  • Calculate the measures of central tendency for each group. You will need to use the full data from above (use all decimal places to calculate these measures). Use two decimal places for your answers. You can use Excel to help you do this.
  • Calculate the measures of variability for each group (range, variance, standard deviation, CV). You will need to use the full data from above (use all decimal places to calculate these measures). Use two decimal places for your answers. You can use Excel to help you do this.
  • Make a table showing the summary measures for each group. Make a comparison in words summarizing your results.
  1. Below is the data for infant mortality for 34 OECD countries. The Organization for Economic Co-operation and Development (OECD) is an international economic organization of 34 countries founded in 1961 to stimulate economic progress and world trade. OECD’s website provided some data on infant mortality for 34 countries. Infant mortality (the rate of death of children under 1 year of age per 1,000 live births) is a measure of development. The table below has the data for 34 OECD countries.
  • Previously, we calculate the mean, median, and mode for this data. Now, add the range, variance, standard deviation, and coefficient of variation for this data.
  • Briefly describe the distribution—focus on the shape of the distribution and whether there are an outliers or strange values.
  • The sum of x [Sum(x)] for 34 OECD countries is 128.20. The Sum(x^2) is 664.42.

Journal of the American Revolution

  1. Todd Andrlik, founder and editor of Journal of the American Revolution, wrote a piece about how young many of the founding fathers were when the Declaration of Independence was first signed in 1776. There were 56 signers of the Declaration of Independence, and their ages are given below, sorted by age.
  • Calculate the measures of central tendency. In addition, calculate the range, variance, standard deviation, and coefficient of variation for this data.
  • The sum of all the values is Sum(x) = 2,479.
  • The sum of the squares of all the values is Sum(x^2) = 116,015.
  • Briefly describe the distribution—focus on the shape of the distribution and whether there are any outliers or strange values.

 

Last Updated on February 11, 2019