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.

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

Week 19/20 - Absolute and relative references

 CLASS OBJECTIVE 

Identify what the absolute references in the spreadsheet are and what they are for.

WHAT ARE ABSOLUTE AND RELATIVE REFERENCES?

A reference identifies a cell (or a range of cells) uniquely and specifically in spreadsheets. References are like addresses within a spreadsheet workbook that will allow formulas to find any cell and get its value to use in applying calculations in a formula, you already know about cell reference formulas, for example:

If you perform this formula with "cell reference" you identify that you will take the values ​​within the indicated cells and a calculation will be performed. Remember that writing the cells is not the same as writing the values ​​that are in the cell (which would be =38+21).

HOW TO MAKE A RELATIVE REFERENCE AND AN ABSOLUTE REFERENCE?

Surely in one of your activities you realized that when creating a formula, the same spreadsheet "helps" you to fill in the formulas that will be applied in the same column, identifying that the cells used are relative, that is, they are changed or updated at the same time. time to copy the same formula, look at this example:

When performing the formula to calculate the total to pay for each product =F2*G2 (=PRICE*QUANTITY) the result is calculated, immediately after the spreadsheet suggests you "fill in" the entire column of formulas by changing the REFERENCE of the cells for the result to be correct, it then suggests AUTOFILL with the same formula

This process is possible since a relative reference is made when automatically changing between the rows since you started with =F2*G2 when accepting it will be filled with the consecutive =F3*G3, =F4*G4, =F5*G5 etc.

WHAT THEN IS AN ABSOLUTE REFERENCE?

Unlike relative references, absolute references do not allow the spreadsheet to change them when you copy them. These references remain fixed no matter how many times they are copied. This means that if you created a formula with an absolute reference, the cell that has the absolute will never change and the one that does not.

In the previous example, you created the direct formula and it is auto-filled, likewise it changes when filling in consecutively and it is finished filling, on the contrary, if we carry out the formula by placing the $ symbol in the row and the column of the reference, the cell will remain look at the example:

On the contrary, if we put the $ symbol as seen in the example, the result when filling will be incorrect since instead of being consecutive, the first cell will now be fixed and the result would be this:

If we observe the price of the first product, it will be applied to all equally, carry out the example with your teacher.

THEN HOW TO MAKE AN ABSOLUTE REFERENCE?

To make a reference absolute we need to prepend the $ symbol to the column and row of the reference. The following formula has an absolute reference to cell C3 as in the class example:

=$C$3*D3

Remember that the difference between an absolute reference and a relative reference is that the first one uses the $ symbol to let the spreadsheet know that we want to leave it fixed even after having copied it to another cell.

 REMEMBER!!!!!

Activity 01

Open the file that is published for today's activity using the example we did together in class as a base, read and follow the instructions indicated by your teacher in the activity.