Tuesday, May 20, 2025

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.


Wednesday, May 7, 2025

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”)




Tuesday, April 1, 2025

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.


Tuesday, March 25, 2025

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.

Tuesday, March 18, 2025

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".





Sunday, March 9, 2025

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.



Thursday, February 20, 2025

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.


Tuesday, February 4, 2025

Week 21/22/23 - First functions in spreadsheets

CLASS OBJECTIVE

Know the definition and use of the Functions of a spreadsheet, such as Google Sheet, which are used to improve information analysis and problem solving.


** Class notes**

FUNCTIONS

Today, you know that in a spreadsheet we can use arithmetic operations such as addition, subtraction, multiplication and division, but not all problems will be solved only with this resource.

In all the spreadsheets, there are some instructions already programmed that we can be use to make the job easier. These instructions are called FUNCTIONS.

Functions must always be indicated under certain rules for the spreadsheet to understand. We call these rules Syntax of a Function:

  • As in formulas, the first symbol in the cell must be the equal sign (=)
  • Immediately after, without spaces, the name of the Function must be indicated.
  • Parentheses will always open and close at the end of the name.
  • In most cases, the data with which the Function will operate will be indicated within the parentheses. This information is called "argument".
  • When it is necessary to add more than one argument, they will be separated by commas.
  • When all the arguments are within a range (adjacent cells) we can select then with the pointer, by dragging over them.


Examples of Functions


= SUM ()

An alternative to add numerical data is to use this function.

An addition like the following

= A1 + A2 + A3 + A4 + A5 + A6 + A7 + A8 + A9

can be replaced by

= SUM (A1: A9)



= PRODUCT ()

As in addition, a multiplication could be replaced for the Producto function as follows:

= A3 * B4 * C5

With a function it would be

= PRODUCT (A3, B4, C5)



=AVERAGE()

To calculate an average we must do more than one operation (addition and division), but there is already a function that does all the work, and it is not even necessary to consider the hierarchy of operations.

= (G5 + G6 + G7 + G8 + G9) / 5

can be replaced by

= AVERAGE (G5: G9)


There are hundreds of functions already programmed and not all of them do mathematical calculations, we will eventually present more functions.




ACTIVITY

As on other occasions, you must create the table with the format shown below to carry out today's activity,

in this table you will put into practice what you have learned in the example and thus solve everything that

is asked:


Once opened, the first thing we will do is solve with arithmetic operations. We will use multiplication to obtain the requested results. Note that the existing data correspond to consumption per minute and the intake of 100 grams.
  • Now, instead of the formula, you will use the Function =PRODUCT ()
Then you will do a statistical analysis. For this, averages will be calculated using formulas, by categories, as shown in the image below.
  • Later, you will calculate the average of each category using the Function =PROMEDIO().
    • Daily activities
    • Sport activities
    • Vegetable products
    • Animal products
    • Processed products



Once finished, rename the file to "BASIC FUNCTIONS" and submit it by Classroom.


As you already investigated in the previous homework, there are not only the functions of =SUM, =PRODUCT, =AVERAGE, =MAX and =MIN, we have many more options such as the counting functions that we will practice in later activities, lets do it some examples in class.

NOTES AND REFERENCES

It is important to clarify that the information used in this exercise is real, although not completely accurate.

the references from which the information was obtained are presented below in case you want to review

them and I suggest you consult an expert if you have questions or if you want to know more about your

diet.



http://www.antiidolo.com/Ar-Salud/390.Tabla-calorias-por-ejercicio-3.asp?sec=13#.YCbyw2j0nIW


https://www.foodspring.es/magazine/tabla-calorica


http://www.imss.gob.mx/salud-en-linea/apps-sano/calculadora-calorias#tmb