SAM_EX19_MOS_4a_JustinAujla_Report_1
.xlsx
keyboard_arrow_up
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
Related Questions
python" You have an Internet connection with a download speed of 41 Megabits per
second (Mbps) and you want to calculate how long it will take to download a files based
on their size.
Write a function named "download_time" that takes an integer as a parameter. The
parameter represents the size of a file in Gigabytes that you want to download. The
function should return the number of seconds it will take to download that file if your
download speed is 41 Megabits per second. To convert the file size from Gigabytes to
Megabits remember that 1 Gigabyte equals 1000 Megabytes and that 1 Megabyte equals
8 Megabits.
arrow_forward
python programming turtle.
arrow_forward
Hello! Please help me find the C language code of this. Thank You!
arrow_forward
C++
arrow_forward
Python task:
Task OverviewYour task is to implement a variation of the classic word game Hangman, which involves playersguessing the letters in a word chosen at random with a finite number of guesses. While there arealternate versions such as category Hangman and Wheel of Fortune, which involve playersguessing idioms, places, names and so on, we will be sticking with the traditional version.If you are unfamiliar with the rules of the game, please read the following before starting:http://en.wikipedia.org/wiki/Hangman_(game). You can also play an online version here. Don’tbe intimidated - You’ll be given some skeleton code to get you started, it's easier than it looks!
You will implement a function called main that allows users to play an interactive hangman gameagainst the computer. The computer should pick a word, and players should then try to guess lettersin the word until they win or run out of guesses.Here is the overarching behaviour we expect:1. The program should load a list of…
arrow_forward
python
What is the syntax for creating a variable containing every other value from 10 to -10 (in that order)? I.e., [10., 8, 6., ... -8., -10.]
arrow_forward
MSIT 501/BMME 8050 Project #1 Description: You are working for a carpeting and flooring company. You need a program to create an estimate for customers. There are three types of rooms that customers might have: square, rectangle and circle. Yes, some people have houses with rooms that are circles. The program should ask for the customer’s name and address. Then ask for the dimensions of the room, in feet. It should then determine the cost to put flooring in the room. We compute flooring based upon the area of the room in square feet. Flooring material costs $2.00 per square feet and installation costs $1.50 per square foot. The equations for calculating the square footage of rooms are as follows: • Square: area = side1 ^ 2 • Rectangle: area = side1 * side2 • Circle: area = radius ^ 2 * pi Once the user has entered the information, the program should print out the customer information. Then it should print the total square feet in the room followed by the estimate for the materials and…
arrow_forward
Decryption Key
A|B|C| D|E|F|G|H|I|J|K|L|M
N|O|P|Q|R|S|T|U|V|W|X|Y|Z
(letter above equals below, and vice versa)
As you can see, A becomes N, B becomes O and so on.
Your job is to write a program, with at least four functions, including main, which must do the following:
1. Ask user whether they want to encode or decode a message - if no, then terminate
2. Take the input string from the user, store it in dynamic memory (use new)
3. As appropriate, encode or decode the message using Rot13.
4. Output the encoded/decoded message
5. Delete the input string from dynamic memory (use delete)
arrow_forward
Multiple Upvotes
arrow_forward
Fill in the Blank Word GameThis exercise involves creating a fill in the blank word game. Try prompting the user to enter a noun,verb, and an adjective. Use the provided responses to fill in the blanks and then display the story.First, write a short story. Remove a noun, verb, and an adjective.Create a function that asks for input from the user.Create another function that will fill the blanks in the story you’ve just created.Ensure that each function contains a docstring.After the noun, verb, and adjective have been collected from the user, display the story that has beencreated using their input.
use python to ode.
arrow_forward
void fx(char
ch) is a
function with
no return
value
int fx(char x)
is a function
with int
return value
int fx(char
&x) is a
function with
parameter
passed by
value
char fx( int x)
is a function
with int
return value
arrow_forward
JAVA Script
A Happy Year is the year with only distinct digits. Create a function that takes an integer year and
returns the next happy year.
Examples
happyYear (2017) → 2018
// 2018 has all distinct digits
happyYear(1990) 2013
happyYear(2021) ➡2031
arrow_forward
python
arrow_forward
USE PYTHON
Source: en.wikipedia.org/wiki/Camel_case
In some languages, it’s common to use camel caseLinks to an external site. (otherwise known as “mixed case”) for variables’ names when those names comprise multiple words, whereby the first letter of the first word is lowercase but the first letter of each subsequent word is uppercase. For instance, whereas a variable for a user’s name might be called name, a variable for a user’s first name might be called firstName, and a variable for a user’s preferred first name (e.g., nickname) might be called preferredFirstName.
Python, by contrast, recommendsLinks to an external site. snake caseLinks to an external site., whereby words are instead separated by underscores (_), with all letters in lowercase. For instance, those same variables would be called name, first_name, and preferred_first_name, respectively, in Python.
In a file called camel.py, implement a program that prompts the user for the name of a variable in camel case and outputs…
arrow_forward
use python
arrow_forward
Python Project:
Problem #1: How much should I study outside of class?Issue:Your fellow students liked the previous version of study hour’s application andwant to expand it again by adding the features listed below.Minimum Study Hours per Week per Class Grade15 A12 B9 C6 D0 FProject Specifications:1. The program asks the user their name, employee id, and department.2. The program also asks the user who they are creating the report for, their employeeid, and the department they work in. (hint #1 & 2 are instances of a class – A classmust be created and used within the program)3. The menu driven program has the following options:A. Determine Hours to StudyB. Determine GradeC. Display Averages and TotalsD. QuitThe user can select any menu option in any order they want.For example:o The user can start the program run option A, then option C, then option B,then option D.o They restart the program, run option C, then D.o They restart the program, run option A, then D.o They restart the…
arrow_forward
Function Name: leapYear Parameters: year – an nonnegative integer representing the year Test Cases: >>>leapYear(1996) The year 1996 is a leap year. Enjoy your extra day! >>>leapYear(1901) The year 1901 is not a leap year. Description: Write a function which calculates whether or not a given year is a leap year and has 366 days instead of 365. A general algorithm is as follows: 1. A year will be a leap year if it is divisible by 4 but not by 100. 2. If a year is divisible by 4 and by 100, it is not a leap year unless it is also divisible by 400. Years such as 1996, 1992, 1988 and so on are leap years because they are divisible by 4 but not by 100. For century years, the 400 rule is important. Century years (ex: 1900, 1800, 1700) are exactly divisible by 4 AND exactly divisible by 100. They are not divisible by 400 and are thus not leap years. You fuction should print out "The year XXXX is a leap year. Enjoy your extra day!" if the year XXXX is a leap year. Print "The…
arrow_forward
Sin with Drawline c++ HW
Call the file: sinDrawline.cpp
Create 2 functions:
1) Degrees2Radians: which has input Degrees, and returns Radians
2) Drawline: which takes two parameters: a character and numRepetitions and prints the
character numRepetitions times, followed by a newline
Inside main()
For values between 0 and 360, in 5 degree increments:
calculate and print the value of sin (degrees),
After every 90 degrees, print out a line of 30 dashes (minus signs '-')
Extra information required:
#include // for sin function
In order to have the precise format we want:
Put this at the top of main:
cout.setf(ios::fixed);
cout.setf(ios::showpoint); // show decimals even if not needed
cout.precision(5); // show 5 digits to right of decimal
arrow_forward
will upvote
arrow_forward
Do not use numpy module or any other module.
language:PYTHON
arrow_forward
// In python //
def compute_stats():"""Write a function compute_stats, such that given a string of grades as an input parameter,it calls the function get_new_grades (defined in theprevious question) and returns a tuple that containsthe average value of the initial grades andthe average value after running the get_new_grades function.""" pass
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education
Related Questions
- python" You have an Internet connection with a download speed of 41 Megabits per second (Mbps) and you want to calculate how long it will take to download a files based on their size. Write a function named "download_time" that takes an integer as a parameter. The parameter represents the size of a file in Gigabytes that you want to download. The function should return the number of seconds it will take to download that file if your download speed is 41 Megabits per second. To convert the file size from Gigabytes to Megabits remember that 1 Gigabyte equals 1000 Megabytes and that 1 Megabyte equals 8 Megabits.arrow_forwardpython programming turtle.arrow_forwardHello! Please help me find the C language code of this. Thank You!arrow_forward
- C++arrow_forwardPython task: Task OverviewYour task is to implement a variation of the classic word game Hangman, which involves playersguessing the letters in a word chosen at random with a finite number of guesses. While there arealternate versions such as category Hangman and Wheel of Fortune, which involve playersguessing idioms, places, names and so on, we will be sticking with the traditional version.If you are unfamiliar with the rules of the game, please read the following before starting:http://en.wikipedia.org/wiki/Hangman_(game). You can also play an online version here. Don’tbe intimidated - You’ll be given some skeleton code to get you started, it's easier than it looks! You will implement a function called main that allows users to play an interactive hangman gameagainst the computer. The computer should pick a word, and players should then try to guess lettersin the word until they win or run out of guesses.Here is the overarching behaviour we expect:1. The program should load a list of…arrow_forwardpython What is the syntax for creating a variable containing every other value from 10 to -10 (in that order)? I.e., [10., 8, 6., ... -8., -10.]arrow_forward
- MSIT 501/BMME 8050 Project #1 Description: You are working for a carpeting and flooring company. You need a program to create an estimate for customers. There are three types of rooms that customers might have: square, rectangle and circle. Yes, some people have houses with rooms that are circles. The program should ask for the customer’s name and address. Then ask for the dimensions of the room, in feet. It should then determine the cost to put flooring in the room. We compute flooring based upon the area of the room in square feet. Flooring material costs $2.00 per square feet and installation costs $1.50 per square foot. The equations for calculating the square footage of rooms are as follows: • Square: area = side1 ^ 2 • Rectangle: area = side1 * side2 • Circle: area = radius ^ 2 * pi Once the user has entered the information, the program should print out the customer information. Then it should print the total square feet in the room followed by the estimate for the materials and…arrow_forwardDecryption Key A|B|C| D|E|F|G|H|I|J|K|L|M N|O|P|Q|R|S|T|U|V|W|X|Y|Z (letter above equals below, and vice versa) As you can see, A becomes N, B becomes O and so on. Your job is to write a program, with at least four functions, including main, which must do the following: 1. Ask user whether they want to encode or decode a message - if no, then terminate 2. Take the input string from the user, store it in dynamic memory (use new) 3. As appropriate, encode or decode the message using Rot13. 4. Output the encoded/decoded message 5. Delete the input string from dynamic memory (use delete)arrow_forwardMultiple Upvotesarrow_forward
- Fill in the Blank Word GameThis exercise involves creating a fill in the blank word game. Try prompting the user to enter a noun,verb, and an adjective. Use the provided responses to fill in the blanks and then display the story.First, write a short story. Remove a noun, verb, and an adjective.Create a function that asks for input from the user.Create another function that will fill the blanks in the story you’ve just created.Ensure that each function contains a docstring.After the noun, verb, and adjective have been collected from the user, display the story that has beencreated using their input. use python to ode.arrow_forwardvoid fx(char ch) is a function with no return value int fx(char x) is a function with int return value int fx(char &x) is a function with parameter passed by value char fx( int x) is a function with int return valuearrow_forwardJAVA Script A Happy Year is the year with only distinct digits. Create a function that takes an integer year and returns the next happy year. Examples happyYear (2017) → 2018 // 2018 has all distinct digits happyYear(1990) 2013 happyYear(2021) ➡2031arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education