QUESTION NO: 1
Project 6 of 7: Bike Tours
Overview
You are the owner of a small bicycle tour company summarizing trail rides that have been booked for the next
six months.
In cell M10 on the "Summer Bookings" worksheet, insert a function that calculates the total amount of sales
from the "Total" column for groups containing 12 or more people even if the order of the rows is changed.
Project 6 of 7: Bike Tours
Overview
You are the owner of a small bicycle tour company summarizing trail rides that have been booked for the next
six months.
In cell M10 on the "Summer Bookings" worksheet, insert a function that calculates the total amount of sales
from the "Total" column for groups containing 12 or more people even if the order of the rows is changed.
Correct Answer:
See explanation below.
Explanation
1. In cell M10 on the "Summer Bookings", insert the followingSUMIFformula: "=SUMIF(G6:G24, ">= 12",
I6:I24)."'
References:
https://support.office.com/en-us/article/SUMIF-function-169B8C99-C05C-4483-A712-1697A653039B
Explanation
1. In cell M10 on the "Summer Bookings", insert the followingSUMIFformula: "=SUMIF(G6:G24, ">= 12",
I6:I24)."'
References:
https://support.office.com/en-us/article/SUMIF-function-169B8C99-C05C-4483-A712-1697A653039B
QUESTION NO: 2
Project 1 of 7: Tailspin Toys
Overview
You recently opened an online toy store. You have sold products to 500 individual customers. You are
evaluating customer data and order data.
On the "Products" worksheet, apply a number format to display the numbers in the "Weight" column to three
decimal places.
Project 1 of 7: Tailspin Toys
Overview
You recently opened an online toy store. You have sold products to 500 individual customers. You are
evaluating customer data and order data.
On the "Products" worksheet, apply a number format to display the numbers in the "Weight" column to three
decimal places.
Correct Answer:
See explanation below.
Explanation
1. Select the cells in the "Weight" column which you want to apply the number formatting to.
2. ClickIncrease Decimaltill the desired decimal places of three is selected, this is found on theHometab.
OR
1. Select the cells in the "Weight" column which you want to apply the number formatting to.
2. On theHometab, in theNumbergroup, click the arrow next to the list of number formats, then selectMore
Number Formats.
3. Select the type of data you have in theCategorylist, in this case it would beScientific.
4. Enter the desired amount ofdecimal placesyou want to display in the Decimal places box, in this case it
would be three.
References:
https://support.office.com/en-us/article/Round-a-number-to-the-decimal-places-I-want-49B936F9-6904-425D-A
Explanation
1. Select the cells in the "Weight" column which you want to apply the number formatting to.
2. ClickIncrease Decimaltill the desired decimal places of three is selected, this is found on theHometab.
OR
1. Select the cells in the "Weight" column which you want to apply the number formatting to.
2. On theHometab, in theNumbergroup, click the arrow next to the list of number formats, then selectMore
Number Formats.
3. Select the type of data you have in theCategorylist, in this case it would beScientific.
4. Enter the desired amount ofdecimal placesyou want to display in the Decimal places box, in this case it
would be three.
References:
https://support.office.com/en-us/article/Round-a-number-to-the-decimal-places-I-want-49B936F9-6904-425D-A
QUESTION NO: 3
Project 5 of 7: City Sports
Overview
The city events manager wants to analyze the enrollment changes over the past five years for various adult and
youth sports programs. You have been tasked to prepare tables for the analysis.
Add the Alternative Text Title "Adult Enrollment" to the "Adult_Program" table.
Project 5 of 7: City Sports
Overview
The city events manager wants to analyze the enrollment changes over the past five years for various adult and
youth sports programs. You have been tasked to prepare tables for the analysis.
Add the Alternative Text Title "Adult Enrollment" to the "Adult_Program" table.
Correct Answer:
See explanation below.
Explanation
1. Right-click the text title "Adult_Program" and clickFormat Objectthen clickAlt Text.
2. Type "Adult Enrollment" in theTitlebox as desired.
3. ClickOK.
References:
https://support.office.com/en-us/article/add-alternative-text-to-a-shape-picture-chart-smartart-graphic-or-other-ob
Explanation
1. Right-click the text title "Adult_Program" and clickFormat Objectthen clickAlt Text.
2. Type "Adult Enrollment" in theTitlebox as desired.
3. ClickOK.
References:
https://support.office.com/en-us/article/add-alternative-text-to-a-shape-picture-chart-smartart-graphic-or-other-ob
QUESTION NO: 4
Project 1 of 7: Tailspin Toys
Overview
You recently opened an online toy store. You have sold products to 500 individual customers. You are
evaluating customer data and order data.
On the "Customers by Order" worksheet, use an Excel data tool to remove all records with duplicate
"CustomerID" values from the table. Do not remove any other records.
Project 1 of 7: Tailspin Toys
Overview
You recently opened an online toy store. You have sold products to 500 individual customers. You are
evaluating customer data and order data.
On the "Customers by Order" worksheet, use an Excel data tool to remove all records with duplicate
"CustomerID" values from the table. Do not remove any other records.
Correct Answer:
See explanation below.
Explanation
1. Select the cells in the "CustomerID" column which you want to have checked for duplicates.
2. SelectRemove Duplicatesfrom theDatatab.
3. Select the "CustomerID" column which you want to have checked under theColumnsoptions.
4. ClickOK.
References:
https://support.office.com/en-us/article/Find-and-remove-duplicates-00E35BEA-B46A-4D5D-B28E-66A552DC
Explanation
1. Select the cells in the "CustomerID" column which you want to have checked for duplicates.
2. SelectRemove Duplicatesfrom theDatatab.
3. Select the "CustomerID" column which you want to have checked under theColumnsoptions.
4. ClickOK.
References:
https://support.office.com/en-us/article/Find-and-remove-duplicates-00E35BEA-B46A-4D5D-B28E-66A552DC