Google Data Analytics - Analyze Data to
Answer Questions - Course 5
Question 1)
The client has asked you to send two separate mailings: one to people within 50 miles of
Rock Springs; the other to anyone outside that area. So, to research each donor's distance from
the city, you first need to find out where all of these people live.
You could scroll through 209 rows of data, but you know there is a more efficient way to
organize the cities.
Which of the following tools will enable you to sort your spreadsheet by city (Column K)
in ascending order? - ANSWERSort Sheet by Column K from A to Z
Question 1a)
Which of the following functions will enable you to sort your spreadsheet by city
(Column K) in ascending order? - ANSWER=SORT(A2:R210, 11, TRUE)
Question 2)
, You notice that many cells in the city column, Column K, are missing a value. So, you
use the zip codes to research the correct cities. Now, you want to add the cities to each donor's
row. However, you are concerned about making a mistake, such as a spelling error.
What spreadsheet tool can you use to add a drop-down list with predetermined options
for each city name? - ANSWERData validation
Question 3)
Now, you decide to address Tayen's request to include a handwritten note in the direct-
mail piece for anyone who gave at least $100 last year.
Which of the following spreadsheet tools will enable you to change how cells appear if
they contain a value of $100 or more? - ANSWERConditional formatting
Question 4)
At this point, you notice that the information about state and zip code is in the same cell.
However, your company's mailing list software requires states to be on a separate line from zip
codes.
, What function do you use to move the 5-digit zip code in cell L2 into its own column? -
ANSWER=RIGHT(L2,5)
Question 5)
Next, you duplicate your dataset twice using the Sheet Menu. You rename the first sheet
Donation Form List, and you remove the cities that are further than 50 miles from Rock Springs.
You rename the second sheet Postcard List, and you remove the cities that are within 50 miles of
Rock Springs.
Then, you import these datasets into your company's mailing list database. In a mailing
list database, you create two tables: Donation_Form_List and Postcard_List. You decide to clean
the Donation_Form_List first.
Your company's mailing list software requires units to be on the same line as street
addresses. However, they are currently in two separate columns (street_address and unit).
Which SQL function will instruct the database to combine two columns into a new
column called "address"? - ANSWERCONCAT
Question 6)
, Your database contains people who live in many areas of Wyoming. However, it's
important to align your in-house data with the data from Food Justice Rock Springs. You also
need to separate your data into the two lists: Donation_Form_List and Postcard_List. They will
be based on each city's distance from Rock Springs.
The zip codes are in a column called zip_code. To select all data from the
Donation_Form_List organized by zip code, you use the ORDER BY function. The syntax is:
SELECT *
FROM Donation_Form_List
ORDER BY zip_code
True or False? - ANSWERTrue
Question 7)
You finish cleaning your datasets, so you decide to review Tayen's email one more time
to make sure you completed the task fully. It's a good thing you checked because you forgot to
identify people who have served on the board of directors or board of trustees. She wants to write
them a thank-you note, so you need to locate them in the database.
Answer Questions - Course 5
Question 1)
The client has asked you to send two separate mailings: one to people within 50 miles of
Rock Springs; the other to anyone outside that area. So, to research each donor's distance from
the city, you first need to find out where all of these people live.
You could scroll through 209 rows of data, but you know there is a more efficient way to
organize the cities.
Which of the following tools will enable you to sort your spreadsheet by city (Column K)
in ascending order? - ANSWERSort Sheet by Column K from A to Z
Question 1a)
Which of the following functions will enable you to sort your spreadsheet by city
(Column K) in ascending order? - ANSWER=SORT(A2:R210, 11, TRUE)
Question 2)
, You notice that many cells in the city column, Column K, are missing a value. So, you
use the zip codes to research the correct cities. Now, you want to add the cities to each donor's
row. However, you are concerned about making a mistake, such as a spelling error.
What spreadsheet tool can you use to add a drop-down list with predetermined options
for each city name? - ANSWERData validation
Question 3)
Now, you decide to address Tayen's request to include a handwritten note in the direct-
mail piece for anyone who gave at least $100 last year.
Which of the following spreadsheet tools will enable you to change how cells appear if
they contain a value of $100 or more? - ANSWERConditional formatting
Question 4)
At this point, you notice that the information about state and zip code is in the same cell.
However, your company's mailing list software requires states to be on a separate line from zip
codes.
, What function do you use to move the 5-digit zip code in cell L2 into its own column? -
ANSWER=RIGHT(L2,5)
Question 5)
Next, you duplicate your dataset twice using the Sheet Menu. You rename the first sheet
Donation Form List, and you remove the cities that are further than 50 miles from Rock Springs.
You rename the second sheet Postcard List, and you remove the cities that are within 50 miles of
Rock Springs.
Then, you import these datasets into your company's mailing list database. In a mailing
list database, you create two tables: Donation_Form_List and Postcard_List. You decide to clean
the Donation_Form_List first.
Your company's mailing list software requires units to be on the same line as street
addresses. However, they are currently in two separate columns (street_address and unit).
Which SQL function will instruct the database to combine two columns into a new
column called "address"? - ANSWERCONCAT
Question 6)
, Your database contains people who live in many areas of Wyoming. However, it's
important to align your in-house data with the data from Food Justice Rock Springs. You also
need to separate your data into the two lists: Donation_Form_List and Postcard_List. They will
be based on each city's distance from Rock Springs.
The zip codes are in a column called zip_code. To select all data from the
Donation_Form_List organized by zip code, you use the ORDER BY function. The syntax is:
SELECT *
FROM Donation_Form_List
ORDER BY zip_code
True or False? - ANSWERTrue
Question 7)
You finish cleaning your datasets, so you decide to review Tayen's email one more time
to make sure you completed the task fully. It's a good thing you checked because you forgot to
identify people who have served on the board of directors or board of trustees. She wants to write
them a thank-you note, so you need to locate them in the database.
