Data Exercise 1

PA 497 | Data Exercise 1 | Fall 2021 | Dr. Swayne

Now that you have learned the basics of the Excel interface and how to enter data, please create three (3) worksheets in Excel with the data our class collected at our field site.

 

Don't use plagiarized sources. Get Your Custom Essay on
Data Exercise 1
Just from $13/Page
Order Essay
  • Each of your worksheets should be formatted in a different way — show off your formatting skills. For example, you may want to use a bold Arial font for the headers of one worksheet, and do a colored font for the headers on your next worksheet. No two sheets should look alike.
  • If there are special numeric types (currency, date, time, phone number, etc.), please make sure to format your data to reflect this.

 

Submit your final work as an Excel workbook and a PDF.

 

PART 1

Worksheet 1: List of best-selling physical singles

Listen while you work on Spotify

 

Worksheet 1 should include a list of the top 10 best-selling singles of all time. The rank, artist, song name, release year, total sales, and run time should each be inputted into their own column. Data taken from: https://en.wikipedia.org/wiki/List_of_best-selling_singles

 

Rank. Artist –  Song Name –  Year –  Sales (in millions) – Run Time

  1. Bing Crosby – White Christmas – 1942 –  50 – 3:03
  2. Elton John –  Candle in the Wind –  1997 –  30 – 4:10
  3. Mungo Jerry – In the Summertime – 1970 – 30 –  3:31
  4. Whitney Houston, I Will Always Love You – 1992 –  20 –  4:31
  5. Bill Haley & His Comets –  Rock Around the Clock – 1954 – 25 – 2:59
  6. Elvis Presly – It’s Now or Never – 1960 – 20 – 3:14
  7. USA for Africa – We Are the World – 1985 – 20 – 7:07
  8. The Ink Spots –  If I Didn’t Care –  1939 – 19 – 3:01
  9. Baccara –  Yes Sir, I Can Boogie –  1977 – 18 – 4:32
  10. Celine Dion –  My Heart Will Go On – 1977 – 18 – 4:40
  11. Mariah Carey –  All I Want for Christmas is You – 1994 – 16 – 4:01
  12. Bryan Adams –  (Everything I Do) I Do It for You – 1991, 15 – 6:34
  13. John Travolta and Olivia Newton-John –  You’re the One That I Want – 1978 – 15 – 2:49
  14. Scorpions –  Wind of Change – 1991 –  15 – 5:12

 

Worksheet 2: Top 10 largest states by area

State Name   Sq. Miles        Population  State FIPS Code

  1. Alaska                   665,384                 731,158                                 02
  2. Texas                    268,596                 29,360,759                           48
  3. California             163,694                 39,368,759                           06
  4. Montana              147,039                 1,080,577                             30
  5. New Mexico       121,590                 2,106,319                             35
  6. Arizona                 113,990                 7,421,401                             04
  7. Nevada 110,571                 3,138,259                             32
  8. Colorado              104,093                 5,807,719                             08
  9. Oregon 98,378                   4,241,507                             41
  10. Wyoming             97,813                   582,328                                 56

Worksheet 3: Employees at Excel, Inc.

First Name, Middle Name, Last Name, Date of Birth, Employee Number, Position

  1. Iveta, Ofra, Bystrom, January 1, 1990, 610001, Data Scientist 1
  2. Ryu, Digby, Pasternak, March 4, 1970, 901632, Vice President
  3. Amanda, Arne, Kumar, April 10, 1991, 117450, CEO
  4. Bethany, Olga, Moreno, June 22, 2000, 928131, CTO
  5. Svanhild, Christa, Schnell, February 17, 1956, 825661, Administrative Assistant
  6. Claudia, Fedelmid, Havener, September 29, 1969, 559333, Administrative Assistant
  7. Myron, Atefeh, Popa, October 29, 1991, 827214, Data Scientist 1
  8. Chad Delphine Merrill, January 2, 1964, 565588, Data Scientist 2
  9. Alex, Leonid, Bures, August 14, 1972, 200827, Data Scientist 1
  10. Cassia, Grigol, Rae, July 4, 1776, 892632, Data Scientist 2

 

 

After you have entered data as described above, please complete Part 2 below.

 

PART 2

For Part 2, you will need to do operations in Excel and copy some of your answers into this document. Submit both your Excel workbook and Word document when you are done. When specific formulas or Excel functions are specified, it is important that you do them in your Excel workbook. Your grade will be based on whether or not you have used the functions as directed.

Worksheet 1: List of best-selling physical singles

Worksheet 1 should include a list of the top 10 best-selling singles of all time. The rank, artist, song name, release year, total sales, and run time should each be inputted into their own column.

 

In Excel:

With this data, please do the following in Excel.

  1. Create data filters for each of your columns.
  2. Sort by Artist name (Z -> A)
  3. Sort by Song name (A->Z)
  4. Using the SUM function, calculate the total sales (in millions) for all songs on the list.
  5. Using the AVERAGE function, calculate the average run time for all songs on the list

 

In Word:

  1. Provide an alphabetical list (Z->A) of the artists.
  2. Provide an alphabetical list (A->Z) of the song names
  3. What is the total sales (in dollars) for all songs?
  4. What is the average run time for all songs?

 

Worksheet 2: Top 10 largest states by area

In Excel:

  1. Create data filters for each column in the worksheet
  2. Using the SUM function, calculate the total square miles of land included in all of the states on your list
  3. Calculate the number of people per square mile in each of the states. You will need to take Population/Sq. Miles for each row.
  4. The total population of the U.S. is 328,000,000. Calculate what percentage of the US population is included in the 10 largest states.
  5. Using the + function, add the population of Alaska, Texas, and Wyoming
  6. Using the AVERAGE function, calculate the average population across all states in your list

In Word:

  1. What is the total square mileage for all states in the list?
  2. Provide the ppl/sq mile for each of the states
  3. What is the portion of US population in the 10 largest states
  4. Enter the combined population of Alaska, Texas, and Wyoming
  5. Enter the average population across all states in the list

Worksheet 3: Employees at Excel, Inc.

In Excel:

  1. Using the CONCATENATE function, combine each employees first, middle, and last names into a single cell. Make sure to put spaces between each name.
  2. Create data filters for each of your columns. Alphabetize the employees by first name (use the column you just created with concatenate)
  3. Using the MIN function, find the employee with the lowest employee number. Using the MAX function, find the employee with the largest employee number.
  4. Make a new worksheet in your file. Title your worksheet “Reference Information.” Paste the information in the worksheet linked here into your new, blank worksheet
  5. Add 3 new columns to your original Worksheet 3 (Base Pay, Bonus, Total Pay). Using VLOOKUP find the Base Pay and Bonus for each of the employees in your original Worksheet 3. Using the SUM function, calculate the total pay for each employee in your list.
  6. Find the SUM of all employees’ pay (including base pay and bonus).
  7. Calculate the average employee base pay

In Word:

  1. Enter an alphabetical list of employees by first name
  2. Enter the smallest & largest employee numbers
  3. What is the sum of all employees’ pay (including base pay and bonus)?
  4. What is the average employee base pay?

 

Recap of deliverables for Exercise 1

When you have completed the steps for each of your worksheets, save your Excel workbook. Upload the workbook to Canvas along with this document.

  1. Your Excel workbook should include all data entered and all operations used.
  2. Your Word document should include answers to all blue questions in Part 2 of this assignment sheet. Your answers can be typed directly into this document or submitted separately.

 

ORDER NOW »»

and taste our undisputed quality.