Computer Science Question

computer science spreadsheet and need the explanation and answer to help me learn.

please make sure that all parts compleated
Requirements: all questions
1 CIS 2640 ASSIGNMENT 2 Check the due date on E-learning Introduction U.S. Census data (Assignment2.xlsx) shows that 100 counties had enjoyed the largest population growth between 2010 and 2014. You work for Acme Inc., which is actively planning to expand its sales to new regions. It is natural to select a few top states that had the largest above-average growth. Which ones do you recommend? (Don’t forget that there are multiple counties of a state listed in this top 100 list.) Download Assignment2.xlsx for the assignment. Follow the instructions below carefully. Section 1: Data Preparation 1. The first task is to apply the text manipulation skills from Chapter 5 to break up column B into County and State. Insert three columns after column B and follow the instructions below: a. Column C: use the FIND() function to find the location of comma in column B. b. Column D: use LEFT() and other functions to extract only county name from column B. Make sure (a) the function(s) reference column C, and (b) there is no leading and trailing space in the result. c. Column E: use RIGHT () and other functions to extract only state name from column B. Make sure (a) the function(s) reference column C, and (b) there is no leading and trailing space in the result. d. The result should look like the following. Again, don’t just type the data in columns C,D and E. Use functions mentioned in the above steps instead. Note that all column headings should be in the same row for the PivotTable in the next section. 2. Column H: calculate the population growth between 2010 and 2014. 3. Column I: use IF statement to determine the growth in column H is above or below average of column H. If it is equal to the average, put it in the “below average” category. 4. The result at this point should look like the following.
2 Section 2: PivotTable Analysis 5. Create a pivot table that looks like the following. Make sure that your pivot table looks like the snapshot. For example, you need to show the average growth with two decimal digits. Name the tab PivotTable. 6. Sort by Average of Growth in descending order so that the state with the largest average population growth is at the top.  Save this Pivot table and make sure to not to make any change on that. If you need to make any change on the pivot table to answer the following questions create a new pivot table, make the changes, take the snapshots you need to answer the following questions and then delete the new pivot table when you are done with the assignment. Section 3: Analysis 7. Create a new worksheet called Analysis. Answer the following questions. Type the question and put your answer below the question. Make sure you cite numbers from the PivotTable to support your answer. This must be done to receive points. 8. Q1: (a) Recommend two states with the largest average population growth, and (b) recommend two states the largest number of counties on this top 100 list two states. Explain your reason in detail. Provide a screenshot of your pivot table to help illustrate your reason. Hint: You need to filter the pivot table to answer the questions. 9. Q2: Since Acme, Inc. is a supermarket chain store, one good strategy is to expand its territory into the states near its home state – Illinois (States which share border with Illinois). This could reduce the cost of transportation. Recommend two states from this top 100 list (no matter they are in the Above Average or Below Average category). Explain your reason in detail. a. Note: In order to answer this question, you will “reshape” the pivot table by removing the Above Average? column from the pivot table and sorting the result by Average of Growth in descending order. Use the slicer to filter the near states. Provide a screenshot of your final pivot table to help illustrate your reason. 10. Re-order your tabs this way: Fastest Growing Counties, Pivot Table, and Analysis. Submission You will submit only one Excel file called Assignment2_XXX.xlsx, where XXX is your last name.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *