Computers and Technology

In this project, you will use Excel to analyze and summarize a subset of data from the Olympic Games. To complete the project, you will use text functions, conditional formatting, conditional aggregate functions, and more. This project covers the basic skills required for the Microsoft Office Specialist Exam 77-727 for Microsoft Excel 2016. Steps to Perform:
Step
Instructions
Points Possible
1 Start Excel 2016. Open the downloaded file named OlympicAnalysis_start. xlsx and save the file as OlympicAnalysis_LastFirst. xlsx, using your first and last names. 0
2 Apply a Berlin Theme to the workbook. 3
3 On the Data worksheet, in cell J1, type a new column heading of FirstName. In cell K1, type a new column heading of LastName. 2
4 In cell J2, use the LEFT and FIND functions to extract the first name from cell A2 and then autofill the function down to J55. Adjust the width of column J to 10.
=LEFT(A2,FIND(" ",A2)-1) 8
5 In cell K2, use the RIGHT, LEN, and FIND functions to extract the last name from cell A2 and then autofill the function down to K55. Adjust the width of column K to 15.
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITU TE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","" 8
6 Apply Conditional Formatting on the Total Medals data in cells I2:I55, so that values more than 1 appear with a White, Bold font, and a Light Blue fill. 6
7 On the Summary worksheet, in cell A1, enter the title Medals Earned and then apply the Bold and Italic font styles to the text. AutoFit the column width so that all text is visible. 5
8 In cell B2 of the Summary worksheet, use the SUMIF function to calculate the number of Gold medals earned by the United States using appropriate cell references to copy the formula down to row 5. In cell C2 of the Summary worksheet, use the SUMIF function to calculate the number of Silver medals earned by the United States using appropriate cell references to copy the formula down to row 5. Continue in this manner to create formulas in D2 and E2 to calculate the Bronze and Total medals using appropriate cell references to copy the formula down through row 5. AutoFill the formula down through row 5 so that all medal data is complete for all countries. 20
9 Convert the range A1:E5 on the Summary worksheet to a table with headers using Table Style Medium 5. 7
10 Add a Total Row to the table and sort the Total medals with the highest amount on top. 6
11 Create a Clustered Column chart that compares the gold, silver, and bronze medals earned by the United States, Great Britain, and China. The countries should make up the Legend Entries (Series) and the medals should make up the Horizontal (Category) Axis Labels. 11
12 Apply Chart Style 5 to the chart and change the color to the fourth option under Monochromatic. 4
13 Add a chart tile of Medals Earned and move the chart so that the top-left corner is in the top-left corner of cell G1. 4
14 Merge & Center cells A8:B8 on the Summary worksheet. 5
15 In cell B10, use the AVERAGEIF function to calculate the average age of Olympians from the United States. Write the formula with the appropriate cell referencing so that it can be copied down through row 12 using AutoFill. AutoFill the formula down through row 13. 8
16 Move the Summary worksheet to the left of the Data worksheet. 3
17 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Summary, Data. Save the file, close the Excel window, and then submit the file as directed. 0
Total Points 100

answer
Answers: 2

Other questions on the subject: Computers and Technology

image
Computers and Technology, 22.06.2019 11:00, sjackson0625p8w1a0
Which action is good business etiquette? a. switching your cell phone off before you enter a meeting b. keeping your cell phone on low volume before you enter a meeting c. setting a pleasant ring tone on your cell phone before you enter a meeting d. setting a standard ringtone on your cell phone before you enter a meeting
Answers: 1
image
Computers and Technology, 22.06.2019 21:30, Cheflulu5727
Write a function named printfloatrepresentation(float number) that will print the floating point representation of a number using the format given below. (sign bit) exponent in binary (assumed bit).significandfor example if the number passed an argument is 71 yourprogram should print (0) 10000101 (1).00011100000000000000000similarl y if the number passed to the function as argument is -71 the program should print (1) 10000101 (1).00011100000000000000000
Answers: 3
image
Computers and Technology, 22.06.2019 23:20, nina1390
How can you tell if someone sent you a text message to your email instead of a email
Answers: 1
image
Computers and Technology, 24.06.2019 08:00, Maxxboogie
Can someone work out the answer as it comes up in one of my computer science exams and i don't understand the cryptovariables
Answers: 1
Do you know the correct answer?
In this project, you will use Excel to analyze and summarize a subset of data from the Olympic Games...

Questions in other subjects:

Konu
Arts, 09.11.2020 16:40
Konu
Geography, 09.11.2020 16:40