Week 37- Create graphs/charts in a spreadsheet

 CLASS OBJECTIVE

Know and create Bar/Column graphs to represent the results of a statistical analysis in a spreadsheet.

COMPONENTS OF A GRAPH

As you already know, a graph is a visual representation of data or values ​​that allows us to make an interpretation or comparison visually, in spreadsheets it is very important to know the process to create the graphs, although it is simple, it is more important to know how to express what you want and analyze what you observe in it.

In most graphs we will find the following components, draw the following illustration in your notebook:

COLUMN/BAR CHARTS

Today as an example we will first create together a table with your grades to understand the process for creating Column/Bar charts, follow the teacher's instructions.

As you have previously investigated, these types of graphs are generally used to compare numerical data, either with columns arranged vertically, or bars arranged horizontally. The extent of these figures represents the numerical values ​​in the tables.

For the creation of a graph, the correct selection of the data in the tables is important, that is, we will not always have to select the complete tables.

Let´s create an example!!!! 

FOLLOWING THE PROCESS...

When starting to create a spreadsheet graph, it is obviously necessary to have a data table which must contain numerical data, the creation process is very simple, for our example (which will not yet be a custom chart) you must follow the following steps:

1.- After opening the spreadsheet with the data, you must select the information you want to include in your graph.

2.- With the data selected you have to click on the graph icon found in the toolbar 

3.- Now your graph will appear automatically with a generic design that you can modify with the options you want to activate. 

 4.- The options to modify the graph are:

Now you have your basic graph!!! In the next class we will explore more of these options.

ACTIVITY INSTRUCTIONS

Week 35 - 36 NESTED IF()

 CLASS OBJECTIVE 

Learn that using IF() function as a recursive function allows the user to create more than 1 condition in order to analyze the content of a cell in a spreadsheet. This method is also called nested IF() function.

NESTED IF() FUNCTION

The nested IF() function allows the user to analyze, using a comparison operation, the content in a cell. In real life we usually have more than two options, for example, if we ask about one of the colors from the Mexico's flag, we will have 3 possible answers, green, white and red. In this kinds of situations is where we are using the nested IF() function.

In this example, the first IF() asks if the answer is "green", but if not then we use a second IF()

The second if asks if the answer is "white", but if not we use another IF().

Finally, the third if asks if the answer is "red" and if not, we give the final result, "incorrect answer".

If you only read the nested function it might be a little complicated to understand, so we recommend you to observe the next flowchart.

As you can see, when using the IF() function as the result when the first condition is False, allows you to ask for a new condition.

We suggest  you to try this first example, with your teacher guidance, using a spreadsheet before doing the class activity.

CLASS ACTIVITY

In today's activity you are going to create an interactive menu. Just like the last session, the file to complete the activity is going to be available for you in Classroom.

1. Fill the cell with the text in the image below.
2. Use data validation to create a list of items with the options: small, medium, large.
3. Now use a nested IF() function to automatically choose a price for the products depending on the user's choice. Do not put the $ symbol, just the amount.
• Burger $50, $55, $60
• Cheese burger $55, $60, $65
• Fries  $30, $35, $40
• Club sándwich  $45, $50, $60
• Hot dog $30, $35, $40
• Nuggets  $35, $40, $45
• Soda  $20, $25, $30
• Milkshake $50, $55, $60

Add the prices for the selected products in the last sections: Total amount.

Finally send your work in Classroom.

Week 33/34 - Reaffirmation of the management and application of the function =IF

 OBJECTIVE OF THE CLASS

Reaffirm the necessary components for managing the =IF function, combining previous knowledge in the management of formulas and functions as well as conditional formatting and data validation.

REMEMBERING THE SYNTAX

The IF function in Google Sheets is a conditional function that allows you to evaluate a condition and return one result if that condition is true and another result if it is false. The basic syntax of the IF() function is as follows:

=IF(condition,result_if_true,result_if_false)

EXAMPLE (PREPARATION FOR THE ACTIVITY "MUSICAL DEL IdeC 2025")

Today we will go directly to preparing the test in a spreadsheet. For this activity you will have to create a completely new file.

• Before starting in Google Sheets, you will have to get 10 images of some famous people, for example, athletes, actors or actresses, singers, fictional characters, characters from a trilogy of movies or series (remember that it cannot be a drug apology, crimes, drug trafficking, etc.).

• To do this you will use the Google search engine, but you will use the image filter, the tools button and choose only icon type images. To download the image you will right click on it and select the "Save image as" option. This will store the images on your computer.

• Now, create a new spreadsheet file in Google Sheets. To begin, you will write down the main instruction in cell B2 (Write down the correct name of each person or character)

• In row 4, starting in cell B4, you will insert the images you downloaded, leaving a free column between each image. Use the Insert/Image/Image tab on the cell.

• After inserting all 10 images, you will now mark a cell below each image for someone else to write down each person's name. Just change the background color of these cells to gray.

• Finally, we program the evaluation of the answer noted in the previous cell with an IF() function. Look at the image.

• In the example in the image the content of cell B6 is being analyzed. If the name “Michael Jordan” is written there then it will say CORRECT, if it is false then it will say BAD ANSWER. Logically you will have to repeat so that the 10 images are evaluated.

• In addition to the activity with the =IF function, you will add formulas in which 2 points are awarded for each correct answer in a cell to turn it into a memory-style game.

IMPORTANT: Note that all texts within the IF() function are indicated in quotes, to prevent Google Sheets from confusing them with another function.

When finished you will have to send your file through Classroom.

Week 31/32 - Logical functions, IF() function

 OBJECTIVE OF THE CLASS

Know the logical function IF(), which allows situations and results to be analyzed in order to draw conclusions automatically, mainly based on a comparison operation.

INTRODUCTION LOGIC IN FUNCTIONS

As we have already told you, until today we have worked with the tools that give us results, such as those of a formula, function, filters, ordering, graphs, etc. Once we have these results, it is up to us to draw conclusions. For example, if I saw low grades on my report card, you concluded that I am at risk of failing. This is logical reasoning.

Among the functions of a spreadsheet there is a classification called Logical Functions, which do this type of "analysis and conclusion" work, and which are based on the comparison operations that you learned last week.

IF() FUNCTION

This is the first of the functions classified as logical. As will be stated in the introduction, this function will use a compare operation to "decide" which result to return. For example, look at this table with the temperature taken by several people:

The following example uses the IF() function to decide whether to return the word HEALTHY or SICK.

As you can see, the function will respond with whThe =IF function in Google Sheets is used to evaluate a condition. That is, when placing a logical test in the spreadsheet and entering the conditional formula, it will give you two results that can be “True” or “False” as appropriate.

These answers may vary depending on what you put in the syntax. For example, “Affirmative” or “Negative”, “Correct” and “incorrect”, etc. at you program, but it will still depend on the result of the comparison operation.

Syntax of the IF Function
The formula for the conditional If function in Google Sheets is as follows:

=IF(condition;value_if_true;value_if_false)

Remember that when starting a formula in Spreadsheets, you must always place the sign (=) before it. In the space where the condition is located, it is necessary to place the logical proof, for example “A>B”, “A<B”, etc.

With respect to the space where it says “value_if_true” or “value_if_false”, the response will be conditional on the initial condition. In other words, if the condition is correct, “True” will appear, otherwise “False” will appear.

If you want to modify the response words, this is possible by replacing “value_if_true” or “value_if_false” with the answers you want to receive. For example:

=IF(A5<8,”APPROVED”,”FAIL”)

Week 30 - Data Validation / Recovery of previous knowledge

  CLASS OBJECTIVE

Create and customize Google Sheets charts, by adjusting the fonts and colors of its elements, to highlight important information as well as the contribution that the use of data validation gives.

DATA VALIDATION

Data validation in Google Sheets is a feature that allows you to verify and ensure the accuracy of information entered into spreadsheets, reducing errors and facilitating consistent data management. Drop-down list: Limits the options available in a cell to specific items from a predefined list. Valid numbers: Restricts entry to certain numerical ranges or specific values. Valid dates: Ensures that only valid dates are entered according to a set format.

How to apply?

• Click and drag to select the cells you want to apply validation to. 

• Access the data validation menu: Go to the menu bar at the top and select “Data”. 

• Choose “Data Validation”.

• Then the validation options panel will appear to activate the desired options.

By having multiple options we will modify only one example and in subsequent classes or activities we will make and apply more examples.

Week 29 - Conditional Formatting

CLASS OBJECTIVE

Apply conditional formatting in a spreadsheet table to automatically highlight the relevant contents in a cell or a range.

INTRODUCTION

In this course we have already talked about cell formats, such as fill colors, borders, font formats, etc. Conditional formatting will AUTOMATICALLY alter the appearance of certain cells, but we must established a condition for this format to be applied.

For example, on a report card it is very common to see grades below 6 appear highlighted in red.

In this example of conditional formatting, when changing the grades below 6 to passing grades, the format should be updated automatically.

In today's activity you will have to use a table like the one above to apply these formats that will be applied when certain conditions are met.

CONDITIONAL FORMATTING

The procedure for applying conditional formatting in Google Sheets is pretty simple. As always, the main thing is to correctly select the cells in which you will apply this Conditional Formatting.

Once the selection of the cells has been made, in the FORMAT tab you have to select the CONDITIONAL FORMATTING option. This will open a new panel on the far right of the screen, like the one shown below.

 

If you correctly selected the cells before opening this panel, it will no longer be necessary to indicate where the format will be applied.

• FORMAT RULES. It is the condition that is established so that the formats are applied, for example, a number is greater or less than another, or that it contains a certain text, or that some date is met.
•  FORMATTING STYLE. They are the formats that will be applied in case the condition has been met. You will notice that they are only font or fill formats.

With the "Add another rule" button you can establish more conditional formats, but the order in which they are applied could override the previous ones.

Practice of the topic (done together with the teacher)

You should take your class notes before completing this week's activity. To do it completely, you'll need to remember several topics such as sorting, filtering, formulas, and functions (prior knowledge.

Now that we are about to close the school year, a spreadsheet report will be very useful to help you calculate your final grades and averages.

• To get started, open a new spreadsheet and you will create the following table. Apply static formats, that is, those that don't change automatically.

 

• Fill in the table with your grades from Periods 1 and 2. Make up grades for the third period.
• Calculate subject averages, period averages, maximum and minimum grade per period, using the AVERAGE, MAX and MIN functions.
• Set the "number format" so that grades are displayed with only one decimal place.
• Apply bold format to the results of the AVERAGE function.

Use this example table to compare with your results.

Now you will apply the conditional formats.

• Select the grades of all your subjects, use the following reference (C3: F13).
• Apply conditional formatting to these cells so that grades less than 6.0 have a red background and white letters.
• Now select only the results of the averages by period (C15: F15) and the averages by subject (F3: F13). Remember to use the CONTROL key to select both ranges.
• Apply conditional formatting so that grades less than 8.0 have a yellow background.

Since the grades of Period 3 are not real yet, I invite you to change them so that you can see how the format of the cells is automatically changed when the established condition is met. Make up some "under 6" grades and you will see the changes.

Week 28 - Advanced filters in the spreadsheet.

 CLASS OBJECTIVE

Apply Sorting and Filters with multiple criteria, to achieve greater precision in the organization and location of the information.

HOW TO ESTABLISH MORE THAN ONE SORTING CRITERIA

When sorting a table it is possible to use more than one criteria to arrange the records. This is very common when the data in a table is constantly repeating.

A very common example is the ordering of a school list, in which surnames are the main criteria but some of these are obviously repeated, such as García or Hernández. In these cases, a second sort criteria is used to rearrange the records that met the first criteria.

We can see another example in the following image, in which the first ordering criteria is the group, but to improve it, age was used as the second criteria and height as the third.

To add sorting criteria you will use the button marked in red.

HOW TO ESTABLISH MORE THAN ONE FILTER CRITERIA

Doing this means that we can filter a filtered table, we simply will not remove the first one.

In these cases, each time we filter the table we will have fewer records on the screen, so the filter can be used to locate information.

To show the entire table again you should deactivate ALL the filters.

FILTER BY CONDITION

There will be times when the list of items of a filter will not be enough. For this we’ll use the “Filter by Condition” option, which offers you the possibility to program the filter.

For example, we can filter by asking for the content of cells, for a word contained in them. We can also filter by asking for numerical ranges, such as "greater or less than".

Week 26/27 - Sorting and filtering a table.

 OBJECTIVE OF THE CLASS

Learn to organize information contained in a table, through the Sort and Filter tools of Google Sheets.

EXAM REVIEW Clarification of doubts, comments and important details after taking the exam and having obtained a grade. How did it go? What did you fail at? What was the most difficult? What do you think should be revised or strengthened? COVER PAGE PERIOD 3 After the review period, a short time will be determined for the creation of the cover of Period 3, which will continue with the theme of the spreadsheet with filters, sorting, logical and graphic functions, remember that the cover must use the same data than the previous period.

TABLE

We’ll call Table to a limited collection of information, stored and organized within a spreadsheet.

The line records are organized by the table headers, which we will call Fields.

For example:

In a "directory" we record the data of many persons but we write them by the headers, that is, in their corresponding field.

Name	Last Name	Age	Phone number	Municipality
Alberto	Gómez	14	3334445555	Zapopan
María	Herrera	13	3339998888	Guadalajara
Pedro	Acosta	15	3338887777	Zapopan
Ana	Valencia	14	3335556666	Tlajomulco

SORT

This is a spreadsheet tool specifically used on a data table. With it, we can place the data records according to a certain criteria, for example, ordered by surname, or by age. The order may be ascending or descending, that is, from A-Z or Z-A, from the smallest to the largest number or vice versa.

Obviously the order of the rows will be altered, but the data will not be mixed with other records.

This tool is used to organize and analyze the information contained in a table.

Look at the table above and tell: which record would be the first if the table is sorted by age in ascending order? or What would be the first if they are ordered by last name but in descending order?

To apply Sorts in Google Sheets I suggest the following procedure

1. Select the entire table, from the names of the fields to the last data.

2. Display the DATA tab and select the SORT RANGE option.

3. It will ask you if the table has headers, because it shouldn't involve them in the ordering.

4. Finally, you will choose the main Sorting criteria.

5. Optionally, you can use secondary Sortering criteria, which will be applied after the main one.

Let's do a couple of examples with the table proposed by your teacher, if the examples are done completely and correctly we will do an example of the filters tool.

FILTERS

This is another tool to analyze and locate information within a table but, unlike sorting, the filters will only show some records from the table, those that match the filter criteria.

For example, in the table above you can use filters to show only 14-year-olds. Who are they?

Another example, use a filter to show the people who live in Zapopan. Who are they?

To apply Filters in Google Sheets I suggest the following procedure

1. Select the entire table, from the names of the fields to the last data.

2. Display the DATA tab and select the CREATE FILTER option.

3. You will see a control in each header of the table that will show you the filtering options.

4. To filter, choose the filter criteria.

5. Optionally you can filter a table that has already been filtered, selecting the control of another header.

6. To show the complete table you will have to mark all the criteria again.

ACTIVITY 01

You will apply the following sorting and filters examples. You will make a copy of the result and paste it under the original table (as we did in the example together during class).

1. Sort the table using the Last Name, ascending, as the main criteria. Copy and paste the result. Change the header fill color to ORANGE.

2. Sort the table using age, descending, as the main criteria. Copy and paste the result. Change the header fill color to YELLOW.

3. Filter the original table so that only female people are shown. Copy and paste the result. Change the fill color of the header to GREEN.

4. Filter the original table so that only people who play “Fútbol” are shown. Copy and paste the result. Change the header fill color to BLUE.

5. Turn off filters to show the entire original table.

You will deliver the file with the 5 results or mini tables through Classroom, as shown below.

Week 24/25 - Summative Assessment Preparation Review

 CLASS OBJECTIVE

Review the topics on Spreadsheets in preparation for the Summative Evaluation of the period.

Summary of Period II

This week you will have to do a review of the topics we've been learning in Period II.

In your notebook you will answer the following questions, using your own words. Logically I suggest you use the class blog to recall all the information.

REVIEW QUESTIONNAIRE- GOOGLE SPREADSHEETS 

1.- In your own words, what is an spreadsheet?

2.- What is a cell, a row, a column and a range?

3.- Explain the procedures to select a cell, a row, a column and a range?

4.- Explain the next cell formats using examples

• Font
• Fill color
• Alignment
• Borders
• Number format (Currency, decimal positions)

5.- What are "comments" in a cell?

6.- What is a Data Table?

7.- Using examples, explain how to use the following arithmetic operations 

• Addition
• Subtraction
• Multiplication
• Division

8.- What is a Function in a spreadsheet?

9.- What is the "syntax of a function"?

10.- Write a clear example and explain what is the common use fot the next functions:

=COUNT

=COUNTA

=COUNTBLANK

=MINUS

=PRODUCT

=RANDBETWEEN

At the end, show to you teacher your questionnaire for signature.

ACTIVITY 1

Now it's time to carry out a small activity in which you will put into play your skills in handling data in spreadsheets, creating formulas and functions.