From one type to another

Would you like to follow along with the instructor using the same spreadsheet?

To use the template for this spreadsheet, click the link below and select “Use Template.”

Link to template:
Movie Data Starter Project

OR

If you don't have a Google account, you can download the spreadsheet from the attachment below:

Movie Data Started Project - Format as Currency

Would you like to follow along with the instructor using the same spreadsheet?

To use the template for this spreadsheet, click the link below and select “Use Template.”

Link to template:
Weather Table - Data for CONVERT

OR

If you don't have a Google account, you can download the spreadsheet from the attachment below:

Weather Table - Data for CONVERT

For details on the correct syntax, refer to the

Google Help Center documentation for CONVERT .

If you're following along, do you want more practice?

Try converting the wind speed in Column D from miles per hour (mph) to meters per second (m/s) using CONVERT. In cell H2, enter:

=CONVERT(D2, "mph", "m/s")

You can check if your conversion is correct by entering 8.5248 in a metric conversion tool, metric-conversions.org/speed/miles-per-hour-to-meters-per-second.htm.

After filling the rest of Column H, your spreadsheet will appear like below. You can also copy and paste values only into Column I (not shown) as you did in Column G for the converted temperatures.

Question: To convert the temperature in cell B2 in a Google spreadsheet from degrees Celsius to degrees Fahrenheit, what is the correct syntax for the CONVERT function?

A. =CONVERT(B2, "F", "C")

B. =CONVERT(B2, "Fahrenheit", "Celsius")

C. =CONVERT(B2, "C", "F")

D. =CONVERT(B2, "Celsius", "Fahrenheit")

The correct answer is C. =CONVERT(B2, "C", "F"). Explain: In Google Sheets, to convert the temperature in cell B2 from degrees Celsius to degrees Fahrenheit, the correct syntax for the CONVERT function is =CONVERT(B2, "C", "F").

Data validation

Would you like to follow along with the instructor using the same spreadsheet? To use the spreadsheet template, click the link below and select "Use Template."

Link to template: Project Spreadsheet - for Data Validation

OR

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

Project Spreadsheet - for Data Validation

Note: Due to changes in the Google Sheets user interface, the dropdown menus for the next 20 seconds in the video won't match your spreadsheet version. Here are the updated steps:

1. After selecting the Data validation option in the Data menu, the following window will appear:

2. Click + Add rule to call up a new Criteria window that will allow you to define the validation values:

Note that the syntax in the Apply to range data box has been changed to 'Sheet1'!C2:C1000 (C1 changed to C2), because we don't need the validation rule applied to our Status title cell.

3. If it's not already chosen by default, select the Dropdown option underneath the Criteria section. You may also explore other options for different types of validations discussed in this video. This is the pop-up window that will appear:

4. Input the 3 different validation values within the boxes that contain Option 1 and Option 2 with the string items Not Yet Started, In Progress, and Ready: You will have to click on the Add another item button to add a third option. Here is what your setup should look like:

Once you have completed these steps and clicked the Done button, you will be in sync with the video at time 2:01.

We will be using column E to create checkboxes. Follow along as we create them!

A data analyst wants to add a spreadsheet dropdown list with three options: Draft, Edit, and Final. Which option from the Data Validation menu should they select?

A. Date

B. Text

C. List of items

D. Custom formula

The correct answer is C. List of items. Explain: The analyst should select List of Items. This option will enable them to select the three options Draft, Edit, Final.

Conditional formatting

If you followed along with the instructor in the Data Validation video, click Continue and use the same spreadsheet to continue following along.

If you didn't previously follow along, or hopped around videos, click the link below and select "Use Template" to follow along with this video.

Link to template: Project Spreadsheet - for Conditional Formatting

OR

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

Project Spreadsheet - for Conditional Formatting

Question: Conditional formatting can be used for which spreadsheet tasks? Select all that apply.

• Adding a dropdown menu
• Highlighting cells that contain the word “ready”
• Allowing users to input only structured data and formulas
• Color-coding cells that contain dates after today

Explain: Conditional formatting is a spreadsheet tool that changes how cells appear when values meet specific conditions. It can be used to change a cell’s color in order to highlight it.

What to do when you get stuck

Note: If the units of measurement in the timestamp columns are expressed in hours, adding 1 (day) to the formula would incorrectly calculate the results as a negative value, thus causing an error in the cell.

Update: Since 1 day = 24 hours, you could substitute the number "24" to satisfy the following statement:

Add 24 minus the start time to the formula that's being used for the multi-day trip; then try to apply to another trip that happened in the same day.

Updated Formula:

As a result of the different units of measurement as described in the last pop-up, the following formula would calculate the correct solution:

=IF(end>start, end-start, 24+end-start)

When to use which tool

R is a programming language frequently used for what tasks? Select all that apply.

• Statistical analysis
• Data analysis
• Visualization
• Protecting data

Explain: R is a programming language frequently used for data analysis, statistical analysis, and visualization.