Test / Course challenge

If you don’t have a Google account, you can download the spreadsheet directly from the attachment below.

Dataset for Project_ CONCAT functionXLSX File

Download file

Using the CONCAT and CONCATENATE functions

Occasionally, you will encounter a dataset with data values in separate cells that you want to combine as a single value in a single cell. This is common when dealing with names and dates. The dataset may have separate columns for first names and last names, but you may want a column with the full names. 

City/state and month/year combinations are also often desirable to have together, as they are likely to be recorded together. 

The CONCAT function in spreadsheets can combine these kinds of data.

Combining data from two cells

First, using the spreadsheet you downloaded, you’ll combine the two sets of names in columns First Name and Last Name in a new column called Full Name. 

To do this, follow these steps:

1. Click on cell F2. This is where you start the data for the new column. After you click on the cell, type =CONCAT(A2,B2) into the function bar and hit Enter (Windows) or Return (Mac).

Once you press enter, the following data should appear in the cell:

You have merged or, technically, concatenated the two data values from cells A2 and B2. Because you listed A2 first in the CONCAT function argument, it comes first in the final result.

Notice that the two names were combined without a space between them. 

If you want to put the space in between, you need to use the full CONCATENATE function, which allows you to combine multiple strings. 

2. Click again on the cell F2. In the function call, place a space in quotes between A2 and B2 separated by commas.

Once you press enter or return, your screen should appear like this:

Now there is a space between the first name and the last name.

Next, repeat this process for all the remaining cells in Column F. Of course, you don't want to do this manually for each cell. (Especially if the dataset were larger, it would be laborious to do this cell-by-cell.) Luckily, you can fill out the data in the column by using your mouse.

1. Click on the cell F2. Locate the small square in the lower-right corner of the highlighted boundary of the cell.

2. Click on this square, drag your mouse to the bottom of the column, and release. All the cells in the column should populate with the full name of the appropriate president. 

• Note: While it does not happen in this dataset, you may have extra spaces in your result after you CONCAT. If you notice you have extra spaces, you can use the TRIM function to remove them.

Combining data from three cells

The procedure for combining three pieces of data from different cells is almost identical to what you just did. The only difference is that you include a third cell in the full CONCATENATE argument.

Now, combine the month, day, and year into a single data value: Date. This will occupy column G. 

1. Click on the cell where you would like the new data to start. Here, this is cell G2. 2. Enter the CONCAT command as =CONCATENATE(C2," ",D2,", ",E2)

Pay particular attention to the extra strings you added between the month and the day, and between the day and the year. This is how you get the spaces and comma in your final result.

3. Fill out the rest of the column using the same click-and-drag technique as before. Your screen should appear like this:

Congratulations! You’ve combined data in spreadsheets using the CONCAT and CONCATENATE functions.

Confirmation and reflection

In an empty cell in the CONCAT function exercise spreadsheet, type the function =CONCATENATE(A7, " ", B7). What does this function return?

1 / 1 point

JohnQuincyAdams

#N/A

John Quincy Adams

John QuincyAdams

Correct

The function =CONCATENATE(A7, " ", B7) would return "John Quincy Adams." The correct function to use here would be CONCATENATE, which you can use to combine strings. Using CONCAT with these arguments would resolve this correctly in Microsoft Excel, but return an error in Google Sheets. Going forward, you can use this distinction to write proper spreadsheet functions.

2.

Question 2

In this activity, you used spreadsheet functions to combine data. In the text box below, write 2-3 sentences (40-60 words) in response to each of the following questions:

• What are some other kinds of data you could combine using CONCAT or CONCATENATE?

• What happens if a data analyst tries to work with dates or names that haven’t been combined with CONCAT or CONCATENATE?

1 / 1 point

CONCAT function make table more concise , easier to work. and other variables we can use CANCATENATE function are name, date, address, and many more.

Correct

Congratulations on completing this hands-on activity! A good response would include how combining data with CONCAT and CONCATENATE helps you simplify and clean data by combining string data from multiple cells into a single cell. 

In order to properly and efficiently analyze data, the data has to be clean and understandable. Data analysts use functions like CONCAT to make data easier to work with, which may require combining multiple cells. Coming up, you will learn more spreadsheet functions that will help you analyze data in spreadsheets.

 

*Course challenge*

Latest Submission Grade 87.5%

1.

Question 1

Scenario 1, question 1-5

You’ve just started a new job as a data analyst. You’re working for a midsized pharmacy chain with 38 stores in the American Southwest. Your supervisor shares a new data analysis project with you.

She explains that the pharmacy is considering discontinuing a bubble bath product called Splashtastic. Your supervisor wants you to analyze sales data and determine what percentage of each store’s total daily sales come from that product. Then, you’ll present your findings to leadership.

You know that it's important to follow each step of the data analysis process: ask, prepare, process, analyze, share, and act. So, you begin by defining the problem and making sure you fully understand stakeholder expectations.

One of the questions you ask is where to find the dataset you’ll be working with. Your supervisor explains that the company database has all the information you need.

Next, you continue to the prepare step. You access the database and write a query to retrieve data about Splashtastic. You notice that there are only 38 rows of data, representing the company’s 38 stores. In addition, your dataset contains five columns: Store Number, Average Daily Customers, Average Daily Splashtastic Sales (Units), Average Daily Splashtastic Sales (Dollars), and Average Total Daily Sales (All Products).

You know that spreadsheets work well for processing and analyzing a small dataset, like the one you’re using. To get the data from the database into a spreadsheet, what should you do?

1 / 1 point

Download the data as a .CSV file, then import it into a spreadsheet.

Use Tableau to convert the data into a spreadsheet.

Email a copy of the dataset to your company email address.

Copy and paste the data into a spreadsheet.

Correct

Downloading data from a database into a .CSV file, then importing it into a spreadsheet, will enable you to process and analyze the small dataset effectively.

2.

Question 2

Scenario 1 continued

You’ve downloaded the data from your company database and imported it into a spreadsheet. To use the dataset for this scenario, click the link below and select “Use Template.”

Link to template: Course Challenge - Scenario 1

OR

If you don’t have a Google account, you can download the template directly from the attachment below.

Course Challenge Dataset - Scenario 1 - Scenario 1_ Pharmacy Data - Part 1CSV File

Download file

Now, it’s time to process the data. As you know, this step involves finding and eliminating errors and inaccuracies that can get in the way of your results. While cleaning the data, you notice there’s missing data in one of the rows. What might you do to fix this problem? Select all that apply.

0.75 / 1 point

Delete the row with the missing data point

Ask a colleague on your team how they've handled similar issues in the past

Correct

You could ask your supervisor or a colleague for guidance. Asking questions helps you learn and avoid mistakes.

Sort the spreadsheet so the row with missing data is at the bottom

This should not be selected

Review the section on the data analysis process for a refresher.

Ask you supervisor for guidance

Correct

You could ask your supervisor or a colleague for guidance. Asking questions helps you learn and avoid mistakes.

3.

Question 3

Scenario 1 continued

Once you’ve found the missing information, you analyze your dataset.

During analysis, you create a new column F. At the top of the column, you add the attribute Average Percentage of Total Sales - Splashtastic. Select the correct definition for an attribute.

0 / 1 point

An observation of data within a column

All of the characteristics of something contained in a table

A headline or subhead

A characteristic or quality of data used to label a column

Incorrect

Review the video on columns, rows, and cells in spreadsheets for a refresher.

4.

Question 4

Scenario 1 continued

Next, you determine the average total daily sales over the past 12 months at all stores, The range that contains these sales is E2:E39. To do this, you use a function. Fill in the blank to complete the function correctly: =_____ (E2:E39).

1 / 1 point

TOTAL

AVERAGE

SALES

SUM

Correct

The function begins with an equal sign (=), then the word AVERAGE. The range is all of column E, represented by E:E.

5.

Question 5

Scenario 1 continued

You’ve reached the share phase of the data analysis process. It involves which of the following? Select all that apply.

1 / 1 point

Present your findings about Splashtastic to stakeholders.

Correct

The share phase involves creating data visualizations, preparing your presentation, and communicating your findings to stakeholders.

Prepare a slideshow about Splashtastic’s sales and practice your presentation.

Correct

The share phase involves creating data visualizations, preparing your presentation, and communicating your findings to stakeholders.

Stop selling Splashtastic because it doesn't represent a large percentage of total sales.

Create a data visualization to highlight the Splashtastic sales insights you've discovered.

Correct

The share phase involves creating data visualizations, preparing your presentation, and communicating your findings to stakeholders.

6.

Question 6

Scenario 2, questions 6-10

You’ve been working for the nonprofit National Dental Society (NDS) as a junior data analyst for about two months. The mission of the NDS is to help its members advance the oral health of their patients. NDS members include dentists, hygienists, and dental office support staff.

The NDS is passionate about patient health. Part of this involves automatically scheduling follow-up appointments after crown replacement, emergency dental surgery, and extraction procedures. NDS believes the follow-up is an important step to ensure patient recovery and minimize infection.

Unfortunately, many patients don’t show up for these appointments, so the NDS wants to create a campaign to help its members learn how to encourage their patients to take follow-up appointments seriously. If successful, this will help the NDS achieve its mission of advancing the oral health of all patients.

Your supervisor has just sent you an email saying that you’re doing very well on the team, and he wants to give you some additional responsibility. He describes the issue of many missed follow-up appointments. You are tasked with analyzing data about this problem and presenting your findings using data visualizations.

An NDS member with three dental offices in Colorado offers to share its data on missed appointments. So, your supervisor uses a database query to access the dataset from the dental group. The query instructs the database to retrieve all patient information from the member’s three dental offices, located in zip code 81137.

The table is dental_data_table, and the column name is zip_code. You have written the following query, but received an error when it ran. What is the clause that will correct this query?

1 / 1 point

WHERE_zip_code = 81137

WHERE zip_code 81137

WHERE = 81137

WHERE zip_code = 81137

Correct

The correct syntax is WHERE zip_code = 81137. WHERE indicates where to look for information. The column name is zip_code. And the database is being asked to return only records matching zip code 81137.

7.

Question 7

Scenario 2 continued

The dataset your supervisor retrieved and imported into a spreadsheet includes a list of patients, their demographic information, dental procedure types, and whether they attended their follow-up appointment. To use the dataset for this scenario, click the link below and select “Use Template.”

Link to template: Course Challenge - Scenario 2

OR

If you don’t have a Google account, you can download the template directly from the attachment below.

Course Challenge Dataset - Scenario 2CSV File

Download file

The patient demographic information includes data such as age and gender. As you’re learning, it’s your responsibility as a data analyst to make sure your analysis is fair. The fact that the dataset includes people who all live in the same zip code might get in the way of fairness.

1 / 1 point

True

False

Correct

It’s your responsibility as a data analyst to make sure your analysis is fair. Although many zip codes do reflect diverse populations, a better choice would be to include data about people who live in multiple zip codes.

8.

Question 8

Scenario 2 continued

As you’re reviewing the dataset, you notice that there are a disproportionate number of senior citizens. So, you investigate further and find out that this zip code represents a rural community in Colorado with about 800 residents. In addition, there’s a large assisted-living facility in the area. Nearly 300 of the residents in the 81137 zip code live in the facility.

You recognize that’s a sizable number, so you want to find out if age has an effect on a patient’s likelihood to attend a follow-up dental appointment. You analyze the data, and your analysis reveals that older people tend to miss follow-ups more than younger people.

So, you do some research online and discover that people over the age 60 are 50% more likely to miss dentist appointments. Sometimes this is because they’re on a fixed income. Also, many senior citizens lack transportation to get to and from appointments.

With this new knowledge, you write an email to your supervisor expressing your concerns about the dataset. He agrees with your concerns, but he’s also impressed with what you’ve learned and thinks your findings could be very important to the project. He asks you to change the business task. Now, the NDS campaign will be about educating dental offices on the challenges faced by senior citizens and finding ways to help them access quality dental care.

Changing the business task involves defining the new question or problem to be solved.

1 / 1 point

True

False

Correct

A business task is the question or problem data analysis answers for a business.

9.

Question 9

Scenario 2 continued

You continue with your analysis. In the end, your findings support what you discovered during your online research: As people get older, they’re less likely to attend follow-up dental visits.

But you’re not done yet. You know that data should be combined with human insights in order to lead to true data-driven decision-making. So, your next step is to share this information with people who are familiar with the problem. They’ll help verify the results of your data analysis.

The people who are familiar with a problem and help verify the results of data analysis are called subject-matter experts. What are their roles in the process? Select all that apply.

1 / 1 point

Collect, transform, and organize data

Validate the choices being made

Correct

Subject-matter experts can offer insights into the business problem, identify inconsistencies in the analysis, and validate the choices being made.

Offer insights into the business problem

Correct

Subject-matter experts can offer insights into the business problem, identify inconsistencies in the analysis, and validate the choices being made.

Identify inconsistencies in the analysis

Correct

Subject-matter experts can offer insights into the business problem, identify inconsistencies in the analysis, and validate the choices being made.

10.

Question 10

Scenario 2 continued

The subject-matter experts are impressed by your analysis. The team agrees to move to the next step: data visualization. You know it’s important that stakeholders at NDS can quickly and easily understand that older people are less likely to attend important follow-up dental appointments. This will help them create an effective campaign for members.

It’s time to create your presentation to stakeholders. It will include a data visualization that demonstrates the trend of people being less likely to attend follow-up appointments as they get older. For this, a pie chart will be most effective.

1 / 1 point

False

True

Correct

A pie chart is used to represent the proportions of certain data categories compared to the whole. A line chart would be effective for tracking trends over time, such as people attending fewer appointments as they get older.