SAM_EX19_MOS_4a_JustinAujla_Report_1

.xlsx

School

University of the Fraser Valley *

*We aren’t endorsed by this school

Course

203

Subject

Computer Science

Date

Jan 9, 2024

Type

xlsx

Pages

6

Uploaded by JusticeApeMaster941

MOS Excel 2019 | Module 4: SAM Project 1a Justin Aujla GE ver. 15.0.0-rc0000 1. 7/7 Change the theme of a workbook. 2. 7/7 Create a formula using a function. Copy a formula into a range. 3. 7/7 Create a formula using a function. Copy a formula into a range. 4. 7/7 Create a formula using a function. Create a formula using a function. Create a formula using a function. Copy a formula into a range. 5. 7/7 Use mixed references in a formula. Copy a formula into a range. 6. 7/7 Change the orientation of cell content. Format text as a hyperlink. Set the ScreenTip for a hyperlink. Add a hyperlink to an area of the workbook. 7. 7/7 Insert a row in a table. 8. 7/7 Set the print area for a worksheet. Add a header to the worksheet. 9. 0/7 Unfreeze a column. In the January Sales worksheet, the first column should be unfrozen. Freeze rows. In the January Sales worksheet, rows 1-7 should be frozen. 10. 6/7 Align cell content vertically. Enter text in a cell. Enter a formula without a function. Wrap text in a cell. Add a border to a range. 11. 7/7 Enter text in a cell. Use Flash Fill. 12. 7/7 Indent cell contents. Create a formula using a function. Modify a cell style. 13. 0/8 Modify a cell style. 14. 0/8 Add a column to a table. In the January Sales worksheet, a column should be added between columns E and F. SUBMISSION #1 | SCORE IS: 76 OUT OF 100 Kendra Han is an administrative assistant for the Prism Real Estate Agency in Princeton, New Jersey. She is using an Excel workbook to consolidate information about the firm's agents and sales. She asks for your help with incorporating text data and inserting links and charts to complete the workbook. Go to the Agents worksheet. Kendra plans to print this worksheet for a graphic designer who is creating a brochure for the agency. Apply the Office theme to coordinate with other Prism Real Estate workbooks. Kendra imported the data in the range A16:F28 from another source and needs to transfer the data to the range A4:E15, which is where the brochure information should appear. The imported data separates first and last names, but Kendra wants to list the full names on the Agents worksheet. List the first and last names of each agent in a single cell as follows: a. In cell A4, enter a formula using the CONCAT function that displays the first name shown in cell A17 followed by a space ( " " ), and then the last name shown in cell B17. b. Fill the range A5:A15 with the formula in cell A4 to list the full names of the agents. The graphic designer needs to know how many characters each name contains, including spaces, so he can provide enough room in the brochure design. Insert the number of characters in each name as follows: a. In cell B4, enter a formula using the LEN function to count the number of characters, including spaces, in the first agent's name (cell A4 ). b. Fill the range B5:B15 with the formula in cell B4 to count the characters in the names of the other agents. Incorporate the remaining imported data as follows: a. In cell C4, enter a formula using the LEFT function to insert the first 2 characters on the left of cell C17. Copy the formula in cell C4 to the range C5:C15. b. In cell D4, enter a formula using the RIGHT function to insert the last 4 characters on the right of cell D17. Copy the formula in cell D4 to the range D5:D15. c. In cell E4, enter a formula using the TEXTJOIN function to insert the text in cell E17 followed by the text in cell F17, separated by a comma and a space ( ", " ), and ignoring empty cells. Copy the formula in cell E4 to the range E5:E15. Kendra wants to provide a commission calculator for agents so that they can quickly estimate their commissions on sales. Calculate the commissions in the range H5:M15 as follows: a. In cell H5, enter a formula without using a function that multiplies the sales amount in cell G5 by the commission percentage in cell H4 . b. Change the cell references to mixed cell references so that the formula always refers to column G and row 4 when copied. c. Copy the formula in cell H5 to the range I5:M5, and then to the range H6:M15. Kendra asks you to format merged cell N3 to better display its contents and to complete the list of links in the range O3:O6. a. Rotate the contents of cell N3 by 90 degrees to display the complete text of the cell. b. In cell O3, insert a hyperlink to the website prism.example.com to provide access to the agency's website. c. Add a ScreenTip to the hyperlink in cell O3 that displays the following text: Prism home page d. In cell O4, insert a hyperlink to the January Sales worksheet. Insert a row above row 16 to separate the imported data from the brochure data, formatting the new row the same as the one below it. When Kendra prints the worksheet, she does not want to include the imported data or the commission information. She does want to provide a phone number so that the graphic designer can contact her if necessary. Prepare the worksheet for printing as follows: a. Set the print area to include the range A1:E15. b. In the center section of the header for the Agents worksheet, type the following text: Kendra Han (609) 555-3422 Go to the January Sales worksheet, which contains agent sales data for January. Kendra does not need to scroll this worksheet horizontally but does want to display the first seven rows as she scrolls vertically. Unfreeze and freeze panes as follows: a. Unfreeze the first column. b. Freeze the first seven rows. Complete and format the data in the first five rows of the worksheet as follows: a. Middle Align the worksheet title in cell A1 vertically to match the format of the Agents worksheet. b. Format cell B2 as Text to prepare for entering an employee ID, which has a leading zero. c. In cell B2, enter 0253 as Kendra's employee ID. d. In cell B5, enter a formula without using a function that divides the total sales from last year (cell B3 ) by 12 and then multiples the result by the targeted increase (cell B4 ). e. Wrap the text in cell A5 to display the entire contents of the cell. f. Add a bottom border to the range A5:E5 using the second dotted style in the Style list and the Blue- Gray, Text 2 color to separate the overview information from the agent sales data. In the January Sales worksheet, cells A5:E5 should have a bottom border formatted using the second dotted style in the Style list and the Blue-Gray, Text 2 color. In the range E8:E19, Kendra wants to list each agent's email address, which all follow the same pattern. Type bob.coleman@example.net in cell E8. Select the range E8:E19, and then use Flash Fill to enter the remaining email addresses. ( Hint : You must use Flash Fill to receive proper credit for this step.) Complete and format the summary information as follows: a. Indent the range A23:A24 to clarify that the information is part of the SUMMARY section. b. In cell B23, insert a formula using the COUNTBLANK function to count the blank sales in the range C8:C19 and identify the number of agents with no sales in January. c. Clear the formatting from the range A25:B25 to keep the note separate from the summary data. Kendra plans to create worksheets for each month and wants to format totals as they are shown on the January Sales worksheet. Create a custom cell style named SalesTotal based on the formatting in cell C20. In the January Sales worksheet, the SalesTotal cell style should use the Black, Text 1 font color and the Blue, Accent 5, Lighter 40% fill color. Insert a column to the left of column F, formatting the new column the same as the one on the right, to separate the agent demographics from the rest of the worksheet.
SAM Excel 2019 | Module 4: SAM Project 1a Prism Real Estate IMPORT DATA, FORMAT WORKSHEETS, AND INSERT FORMULAS Author: Justin Aujla Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy the file from the SAM website.
y of
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help