Free Microsoft 77-727 Practice Test & Real Exam Questions
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.
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
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 C8 on the "Summer Bookings" worksheet, insert a function that joins the customer "Lastname" to the
customer "Firstname" separated by a comma and space. (Example: Campbell, David).
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 C8 on the "Summer Bookings" worksheet, insert a function that joins the customer "Lastname" to the
customer "Firstname" separated by a comma and space. (Example: Campbell, David).
Correct Answer:
See explanation below.
Explanation
1. In cell C8 on the "Summer Bookings" worksheet, insert the followingCONCATfunction: "=CONCAT(E6 ",
"D6)"OR"=E6& ", ", D6".
References:
https://support.office.com/en-us/article/Combine-text-from-two-or-more-cells-into-one-cell-81ba0946-ce78-42ed
Explanation
1. In cell C8 on the "Summer Bookings" worksheet, insert the followingCONCATfunction: "=CONCAT(E6 ",
"D6)"OR"=E6& ", ", D6".
References:
https://support.office.com/en-us/article/Combine-text-from-two-or-more-cells-into-one-cell-81ba0946-ce78-42ed
Project 4 of 7: Car Inventory
Overview
You manage the office of a used car business. You have been asked to prepare the inventory list for a big
annual sale.
The discount price is 95 percent of the price. Modify column J to show the discount price for each car.
Overview
You manage the office of a used car business. You have been asked to prepare the inventory list for a big
annual sale.
The discount price is 95 percent of the price. Modify column J to show the discount price for each car.
Correct Answer:
See explanation below.
Explanation
1. In cell J10 on the "Car Inventory" worksheet enter "=G10 *.95" OR "=G10 * 95%".
2. Select cell J10, hold theShiftkey on your keyboard, and use the down arrow key until all the cells the
formula is required to be applied (J40).
3. Hold down theCtrlkey on your keyboard and press D which would "copy down" the formula for the rows
below.
OR
1. In cell J10 on the "Car Inventory" worksheet enter "=G10 *.95" OR "=G10 * 95%".
2. Select cell J10, then Right-click and selectCopy,ORclickCopyfrom theClipboardgroup situated on
theHometab.
3.Pastethe formula by selecting Paste from theClipboardgroup situated on theHometab,ORRight-click then
selectPaste,ORclickPastefrom theClipboardgroup situated on theHometab, then clickPaste Specialand
selectFormulas,ORyou could selectValuesfor the formula results instead.
4. Verify that the cell references in the formula are as desired.
References:
https://www.excelforum.com/excel-general/745891-calculate-discount-for-full-column.html
https://support.office.com/en-us/article/move-or-copy-a-formula-1f5cf825-9b07-41b1-8719-bf88b07450c6
Explanation
1. In cell J10 on the "Car Inventory" worksheet enter "=G10 *.95" OR "=G10 * 95%".
2. Select cell J10, hold theShiftkey on your keyboard, and use the down arrow key until all the cells the
formula is required to be applied (J40).
3. Hold down theCtrlkey on your keyboard and press D which would "copy down" the formula for the rows
below.
OR
1. In cell J10 on the "Car Inventory" worksheet enter "=G10 *.95" OR "=G10 * 95%".
2. Select cell J10, then Right-click and selectCopy,ORclickCopyfrom theClipboardgroup situated on
theHometab.
3.Pastethe formula by selecting Paste from theClipboardgroup situated on theHometab,ORRight-click then
selectPaste,ORclickPastefrom theClipboardgroup situated on theHometab, then clickPaste Specialand
selectFormulas,ORyou could selectValuesfor the formula results instead.
4. Verify that the cell references in the formula are as desired.
References:
https://www.excelforum.com/excel-general/745891-calculate-discount-for-full-column.html
https://support.office.com/en-us/article/move-or-copy-a-formula-1f5cf825-9b07-41b1-8719-bf88b07450c6
Project 3 of 7: Tree Inventory
Overview
You are updating the inventory worksheet for a local tree farm.
Check the spreadsheet for accessibility problems. Correct the error by adding "Tree Nursery Inventory" as an
alternative text file. You do not need to fix the warning.
Overview
You are updating the inventory worksheet for a local tree farm.
Check the spreadsheet for accessibility problems. Correct the error by adding "Tree Nursery Inventory" as an
alternative text file. You do not need to fix the warning.
Correct Answer:
See explanation below.
Explanation
1. To check the accessibility select theReviewtab from the ribbon.
2. SelectCheck Accessibility.
3. Review the results.
4. Exit theAccessibility Checker.
5. Right-click on the worksheet then clickFormatand then clickAlt Text.
6. Type "Tree Nursery Inventory" in theDescriptionbox.
7. ClickOK.
References:
https://support.office.com/en-us/article/Use-the-Accessibility-Checker-to-find-accessibility-issues-a16f6de0-2f39
Explanation
1. To check the accessibility select theReviewtab from the ribbon.
2. SelectCheck Accessibility.
3. Review the results.
4. Exit theAccessibility Checker.
5. Right-click on the worksheet then clickFormatand then clickAlt Text.
6. Type "Tree Nursery Inventory" in theDescriptionbox.
7. ClickOK.
References:
https://support.office.com/en-us/article/Use-the-Accessibility-Checker-to-find-accessibility-issues-a16f6de0-2f39
