This is a Clilstore unit. You can link all words to dictionaries.

UNIT 5 CALC

_{SPREADSHEET EXERCISES}

_{LIBREOFFICE CALC}

4 ESO - CLIL

Before beginning, download from Moodle your personal Calc template. You must make all these activities using this file. Your personalized file is a spreadsheet, containing 16 sheets, one for each exercise in this unit.

Dont' use, modify or delete the first three sheets, (hoja1, hoja2, hoja3). These sheets contain information used to make sure the file is used properly.

_EXERCISE_1._PAÍSES

Start LibreOffice.org Calc selecting the menu Aplicaciones→Libreoffice→ Hoja de cálculo. Open your personal template.

The gridded space is known as the Work area. Each rectangle in the grid is a cell (celda en español), and can be used to enter data. The active cell is marked by thicker borders and is the only one in which is possible to edit data.

Every column is named with a letter (A, B, C,..., AA, AB, AC,....)
Every row is numbered(1, 2, 3, 4,...).
Every cell is named using its column letter and row number. For example, in the image above the active cell is B2

Open the sheet 1.Países

Click in the cell B2 to turn it into the active cell. Write the text Francia into it . Notice that as you write in the cell the text is also shown in the formula bar (look at the image below). The text can also be edited in the formula bar

Write Italia into the cell B3 ,

Write 1 in the cell A2.
Write 2 in the cell A3
Select both cells. Notice that a little black square appears in the selection lower right corner. This square is called the Autofill Handle (control de relleno). Click on it and drag it down to the cell A11. This action will fill all the cells in the selected range with a sequence of numbers 1,2,3,4…. 10 as shown in the images below

We are going to create a table for a person who is planning a number of trips throughout Europe. The traveler is going to visit a different country the first day every month, starting with January.
Select D2 and write 01/01/2017. Then select D3 and write 01/02/2017
Select D2:D3 (this means from D2 to D3), and click in the autofill handle (remember, the little black square on the cell bottom right corner) and drag it down to D11

Result expected:

We can enter in a cell different data types, for example, so far you have entered Text, numbers, and dates. In Calc there are other data types like booleand data:

Select E2. Our traveller will write in this column Sí /No. depending on wheter he will travel by plane . The answer can only be Sí /No. This type of data is called Boolean data and represents information with only two possible values: yes/no, true/false, 1/0.
Write No at the column E for España, Francia and Portugal, and Sí for the other countries

One data type, can also be represented in different ways. For instance, numbers, can be represented as regular numbers (33,4), but also as money (33,40€), as percentage (33,4%), and in scientific notation

In the column F enter random (aleatorio) numbers between 1000 and 4000, with two decimals.
Select the cells F2:F11 and apply money format to them clicking on the money button (shown below) in the toolbar

Another way of representing numbers is as percentages. In the column G, we are going to enter the VAT ( IVA in spanish) of each country.

Starting on G2, enter random values between 0,10 and 0,25.

Select G2:G11 and click on the % button to give this numbers a percentage format. Notice that in order to write percentages you must write a number between 0 to 1. That is: 0,12 becomes 12%. Take care, the typical mistake here is to write 12 in the cell, which will become 1200% instead of 12%

The numbers can also be represented with a greater or lower amount of decimals using the buttons añadir y eliminar decimales

It's pointless (sin sentido, innecesario) to allow decimals on the column G, in the VAT is a whole number with no decimals in all european countries. Delete these decimals using the buttons añadir y eliminar decimales

If we apply the IVA (column G) to the trip price without VAT (column F), we will get the real price of the trip. Create in the column H a formula to work it out (calcularlo)

Select the cell H2
Place the cursor in the formula bar and write the formula =F2+(F2*G2). Important every formula must start with the = character.

Finally, spread (extender) the formula from H2 to H11 dragging (arrastrar) the black square down.

Save a back up copy with the name ejercicio1

EXERCISE 2. CAPITALS

Open your template and select the sheet 2.capitales. The goal in this exercise is to carry out a little test of Geography. The user must enter in the column C the name of the capital city of the country in the same row in the column B, and the sheet will assess (evaluar) if the answer was correct.

Click in the cell D2 and write this formula =Si(C2=”PARÍS”;“CORRECTO”; “FALSO”) Explanation: Si(), is a function, with the following syntax: SI(expression;value_if_true;value_if_false)

Expression is a condition which can be true or false. If expression is true, value_if_true is stored in the cell, otherwise, the value stored is value_if_false

In the row 2 (Francia), the condtional expression which must be true is C2=”PARÍS”. If the user enters PARÍS in C2 the condition is true and as a result the value CORRECTO is written in the cell D2 . In any other case, the written value in D2 will be FALSO

Fill the rest of cells from D3 to D11 with the suitable formulae to asses the user answers in C3 : C11. For example in D3 you should enter =Si(C3=”ROMA”;“CORRECTO”; “FALSO”)

Save a back up copy with the name ejercicio2

EXERCISE 3. DAYS LIVED

In this exercise we will carry out a sheet to work out (calcular) the number of days a person has lived so far,

Open the sheet 3 Dias vividos in your personal template.

In B2 write the sheet title ¿Do you want to know how many days have you lived so far?

Increase the font size and highlight the text using bold letters. The format bar is very similar to the one you learned to use in LibreOffice Writer

Write in A4 Enter your birth date in the highlighted cell

The dates can be entered writing day, month,and year separated by a slash (/), a dash (-), or a dot (.). For example: 17/12/15; 17-12-15 or 17.12.15.

The user must enter his birth date in E4. Format this cell as a date using Formato celdas ->Numero, In the pannel categoria select fecha

Highlight this cell with a thicker line borders using the menu Formato celdas→ Borde .

In G4 we are going to subtract the today's date with the date entered by the user. We will use the function HOY(), (in valencian AVUI()), which returns today's date.

Write in G4 =hoy(). (avui() if your LLiurex uses valencian. The result is a number meaning the number of days from 1/1/1900 to today. To turn it into a date, select G4 and click in the button fecha

• Select the cell E7 and write the formula =DIAS(G4;E4), which returns the numbers of days went by (pasados, transcurridos) between the dates in G4 (today) and E4 (the user's birdth date)

Select E7 and change the text color to red, and align it to the center using Formatear celdas. Enable the Separador de miles option, in the tab Numeros.

To end up the sheet write some clarifying instructions as shown in the image. Try to translate them to English, (of course)

Save a backup copy with the name ejercicio3

_E_XERCISE₄_{SUBTRACTION TABLE}

Open the exercises template in the sheet 4. Restar. Select a random cell and write =8+7 and press on the key enter. You have just written a mathematical expression using the operator +. There are many other mathematical operators in Calc

Operador / Significado

+ Suma

- Resta

* Producto

/ División

^ Potencia

% Porcentaje

< Menor que

> Mayor que

= Igual que

<> Distinto que

<= Menor o igual

>= Mayor o igual

Keep in mind that when it comes to (cuando se trata de) write mathematical expressions in Calc, multiplication and division are carried out before addition and subtraction. For example =2+5*4 result is 22, and not 28. You can also use brackets (paréntesis) to indicate the operators order. For example =(2+5)*4 result is 28 (and not 22)

In this exercise we are going to implement a subtraction table: the user will enter a number in D4 and the table will show the result of subtracting that number to all the numbers between -5 and 5 in the column F

Write TABLA DE RESTAR, in C1 as the sheet headline.
Select C1:D1, deploy the contextual menu, and select the option Unir celdas. This options will merge these three cells in a single one. There's also a button unir celdas in the toolbar

unir celdas

We are going to write two lines of text, starting in B2. To fit them increase the cell hight, clicking on the border between the files B and C and drag it down, (as shown in the image below)

In B2 write Escribe en la celda resaltada con borde grueso el número del que se desea obtener la tabla de restar. Then merge the cells B2:F2.
Select the merged cells and drop down the contextual menu
Formatea las Celdas→solapa alineación. Select the alignment settings shown in the image below. The goal is to align the text to the center, both vertically and horizontally

Highlight the cell D4 with an area and border color using the buttons color de fondo, and bordes.

Botón bordes.

Botón color de fondo

In the column B must appear the number entered by the user in D4. So write =D4 in B6 and then drag down the cell to B16 using the autofill handle, (the little black square)

This is the result, which is not exactly what we were expecting. We expected to find the D4 value (3) in all the cells in the range B6:B16.

In a SpreadSheet, by default, the references to cell are relative, that is, the reference is the distance from one cell to the referenced cell. Not to the position of the referenced cell, but the distance in horizontal and vertical cells between the in which the reference is entered and the referenced cell. If you put in B6 the reference =D4, this means that B6 must take the value of the cell placed two columns on the right (B is two columns right from D) and two cells up (6 is two rows down from 4)

When you drag down B6 to B7, B7 takes the value of the cell placed two rows right two rows up, that is D 5, a cell with no value, instead of D4, as shown in the image below

The solution is to fix the reference to D4 using the $ char. Write in B6 the reference =$D$4 Putting $ besides the column ($D) fix it while dragging it horizontally. Putting $ besides the Row ($4) fix it while dragging it vertically. This is called an absolute reference.
Put in B6 the reference =$D$4 , and again drag it down from B6 to B16. The result must be like this one:

Notice that there are reference mixtes like =$d4 o =d$4. The first one sticks on the column D while dragging, the second sticks on the row 4

In F6 insert the formula =B6-D6 and drag it down until F16. In this case the relative reference system plays in our favor, since we want to subtract the values from B and D in every row

The subtraction table is done. Try to change the number in D4, all the sheet is redrawn showing the subtraction table of that number

Save a backup copy called ejercicio4

_EXERCISE_{5 MULTIPLICA}_{TION TABLE}

Open the sheet 5. Multiplicación in your exercises template.

In the previous exercise you carried out a subtraction table. In this exercise you are going to make a multiplication table, using all the techniques learned in the exercise 4

This time the user will enter the number in the cell G4

Use the column D to place an absolute reference to G4. (Remember the absolute references uses $ to fix the reference to a cell)
Write in F6 the value 0 , and in F7 the value 1, and drag down to F16

Use the column H to write a formula in which columns F and D are multiplicated
Save a backup copy called ejercicio5

EXERCISE 6 MENTAL CALCULATION

In this activity the user must demonstrate his skills in mental calculation. The user must solve the equations and write the result in the column I. If the solution is correct a VERDADERO text will be shown in the column J. The message will be FALSO otherwise. In I7 we will place the function =CONTAR.SI(J1:J5;"VERDADERO") (In valencian language the function is COMPTARSI). This function counts the number of cells in the rage J1:J5 containing the text VERDADERO

Let's get started with the first row. Write in J1 =SI(I1=A1+C1*E1-G1;”VERDADERO”;”FALSO”). Remember, SI will write in J1 VERDADERO if the condition is true,and FALSO otherwise
Fill in the proper SI formulas in the cells J2,J3,J4, and J5
In I7 insert the formula: =CONTAR.SI(J1:J5;"VERDADERO") (in valencian language COMPTARSI) this function was explained at the beginning or this exercise
We also want some user feedback. If the number of true answers is bigger or equal to 3, in J7 must show "PRUEBA SUPERADA". While this goal is not achieved the message in J7 must be "SIGUE CALCULANDO". Write in J7 =SI(I7>=3;"PRUEBA SUPERADA";"SIGUE CALCULANDO")
Save a backup copy named ejercicio6

_E_XERCISE_{7 DEPARTMENTS}

Open the sheet 7, departamentos. We are going to make a table of the expenditure in different departments in a school.

Set up area and border line colors in the cells as shown in the image below. To set up the area color use the color de fondo, button or the menu option Formato →celdas→tab fondo,

To set up the border line in a cell and the border color, use the Bordes button, or the menu option Formatea celda→solapa bordes,
To set up a text color use the button color de texto
The result must be similar to the one shown below:

The rows 3 to 17 are reserved in order to allow the user to enter the expenditure of the departments ; in the row 18 there will be written the sum of expenditure in any department and in the row 19 you will calculate the percentage of the total expenditure

To calculate the sum of the expenditure in a department, (that is B3+B4+B5+B6…..+B17), in row 18 write the function SUMA. In B18 write the formula: =suma(B3:B17). Drag right the cell B18 to the G18, (using as before, the little black square)
In H18 we are going to enter the sum of all departments expenditure, (that is, B18+C18+E18+…...+G18). You must figure out (descubrir la solución) the correct formula taking into account how you solved a very similar problem in the previous paragraph.
In the row 19 we must work out the percentage of the school expenditure due to each department.
- Select the cells, B19:G19, and set up percentage format with two decimals on them. Use the buttons percentage and the increase/decrease decimals buttons
- Write in B19 the formula = =B18/$H$18, and drag it right until G19. In order to get a percentage it isn't necessary to multiply it per 100 since the percentage format makes automatically this multiplication. Notice also that we divide it per $H$18, (an absolute reference). If we divided it by H18 (a relative reference), the dragging right wouldn't work properly. It would write C18/I18 in C19, where I18 is an empty cell.
A last step to end up the sheet. Set up the money format in B3 : G18, and G19. Use the button money. In the image below is shown the expected result

Save a backup copy and name it ejercicio7

EXERCISE 8 Average Mark

Open the sheet 8 Nota media.

A teacher needs to calculate the average mark got by his students in a trimester. The teacher has assessed 4 task in the trimester and therefore he has 4 mark for each student. But every task must have a different weight in the average mark. For instance:

Mark 1 (N1) 30% de la nota final

Mark 2 (N2) 30%

Mark 3 (N3) 20%

Mark 4 (N4) 20%

The formula we must write in order to calculate the wighted average mark, is::

Let's implement all these data in the sheet:

In B3:E3 write the student's marks.
Set up %format in B5:E5 (remember, you can use the % button in the toolbar).
Enter in B5:E5 the marks weight over the total mark. Important: the sum of all them must be 100.
Enter in D7 the weighted mark formula shown above. Remember that as you have set percentage format in these cells it is not necessary to divide by 100 in order to get a percentage. That, is the weighted mark is =B3*B5+C3*C5+D3*D5+E3*E5, and is not =(B3*B5+C3*C5+D3*D5+E3*E5)/100, the formula show at the beginning of the exercise
Format the table as shown in the image above. Remember, you can change the borders colors using formato celda→bordes
Join the cells B7:C8 and D7:D8. Remember you can use the button unir celdas or the contextual menu option unir celdas.
Centered vertically and horizontally the data inside the cells using formato celda→alineación, or the align buttons in the toolbar menu (shown in the image below)

Save a back up copy named ejercicio8

EXERCISE 9 UNITS CONVERTER

Open the sheet 9 conversor de unidades in your exercises template.

We are going to make an spreadsheet to convert different units:

From centigrades degrees to Fahrenheit. GradosFahr. = (GradosCent*9/5)+32
From pounds to kilograms. Kilogramos= Libras *0,4536
From kilómeters to meters. Metros=Kilómetros *1000

Join the cells B1:E1 using the option unir celdas (o fusionar celdas) from the contextual menu
Join the cells BC and DE in the rows 4 to 8 as shown in the image
Format the sheet with the border and area colors shown in the image
The formulas to carry out the conversion must be written in D5, D7 y D9. For example, in order to convert Centigrades degrees in Farenheight you should write the formula =(B5*9/5)+32, where B5 is the cell in which the user has entered the centigrade degrees
Insert n D7 the formula to convert Libras in Kilograms
Insert n D9 the formula to convert Kilometers en meters

Save a backup copy named Ejercicio9

EXERCISE 10 SOLAR SYSTEM

Open the sheet 10.sistema solar in your exercises template.

We are going to make a sheet to work out your weight in the Sun, the Moon and all the planets in the solar system. In order to make the calculation we will consider Earth gravity as 1, and we will use the these planets gravity in relation to the one on Earth

Celestial body Gravity in surface (Earth = 1)

Sol Peso en la tierra *27,6

Luna Peso en la tierra *0,166

Mercurio Peso en la tierra *0,39

Venus Peso en la tierra *0,87

Tierra Peso en la tierra *1

Marte Peso en la tierra *0,38

Júpiter Peso en la tierra *2,55

Saturno Peso en la tierra *1,14

Urano Peso en la tierra *1,17

Neptuno Peso en la tierra *1,38

Plutón Peso en la tierra *0,40

Download from here the planet images

The user must enter his weight in E4
To work out the users weight in the Sun: In D11 write the formula=E4*27,6. Fill in the formulas for the rest of planets in D13, D17, D21, E4, E12, E13, E17 y E21.
Format the sheet as shown in the image. Download the link you will find in Moodle. Use Insertar→Imagen→desde archivo in order to insert the images in the sheet
To draw the blue arrow shown below select the menu Ver→Barras de herramientas →dibujo, there will appear a new toolbar and there will be an arrow button

Save a backup copy and call it ejercicio10

EXERCISE 11 Trimester Outcome

Open the sheet 11 Resultados de una evaluación solar in your exercises template.

We are going to make a table showing the final results of several student groups in a trimester. Using as input the number of students who passed all the subjects, those who failed in three or less subjects and those who failed in more than 3 subjects.

Create a table as shown in the image below. Use the borders and area buttons or formatea celda ->borders

• In A4, A6, A8, write the number of students in each group. In the cells with orange area enter the number of students who passed all the subjects, those who failed in <3 subjects and those who fail in >3 subject:

Set percentage format (use button %) in the white cells on the image.
Write a formula in cell B4, to divide the number of students with 0 failed subjects (B3) by the total number of students (A4)
In order to drag right from B4 to D4 change in the formula in B4, A4 by $A$4

• Fill the cells B6:E6 y B8:E8, using the procedure shown in the previous paragraph .

Add the rows needed to calculate the totals for students number and percentage

In A9 write a formula to add all the students in 1º ESO (that is A8+A6+A4)
Drag the formula from A9 to D9, to get the total number of students in any category (0 fails, <=3 fails, >3 fails)
In B10:D10 set up % format and write the formula to calculate the students percentage in each category for all 1º ESO

Save a back up copy and name it ejercicio11

EXERCISE 12 REPORT FOR INSPECTION

Open the sheet 12 informe inspección solar in your exercises template.

Task: Present the overall (global) academical results by courses in secondary school, representing the data in a columns chart:

Make a table as the one shown in the image above
Set % format in G5:H8
In G5 write =(C5+D5+E5+F5)/(B5+C5+D5+E5+F5) and drag until G8

In H5 write =100%-G5 and drag until H8.

Lets complement the table information with some columns graphics representing the marks in each classroom group. We will start making the graphic for 1º A

Select the cell range A4:F5. Select the menu option Insertar -> Gráfico...

This starts the Graphics Wizard (asistente de gráficos) window. Select Column graphic, type normal and click on Siguiente.
Accept de default options in Rango de datos, Series de datos and Elementos de gráficos. In the last window, Elementos de gráficos. Write Resultados 1ºA in Título, Calificación in Eje X and Nº alumnos en el Eje Y. The last step is to click in the Finalizar button.

Follow the same procedure to create the graphics for 1ºB. Select A4:F4 press the key Ctrl, and without releasing it select A6:F6. Then select Insertar -> Gráfico. Distribute the graphics in the window, Move them as shown in the image.
Follow the same procedure to create the graphics for 1ºC. This time using the Ctrl key select the ranges A4:F4 and A7:F7
Repeat the process with 1ºD

Create a comparative graphic of all 1ºESO. This time you must seelct A5:A8 and H5:H8

Save a backup copy and name it ejercicio11

EXERCISE 13. Repeaters

Open the sheet 13 repetidores from the exercises template

We are going to make a graphic from the data table shown below:

_Curso _{Nº repe}_t_i_dores

1º	8
2º	14
3º	18
4º	9

Create the graphic, now you are supposed to know how to do it. Select the range shown in the image

Now select the menu option Insertar > Gráfico to start the graphics wizzard (Asistente de gráficos):

The first step is to chose a type of graphic, Select Columna its variant normal

Click in the button Siguiente to move to the window 2, Rango de datos:

By default the option serie de datos en columnas is enabled. In this graphic this is correct since the numeric data we want to represent is in the second column(column G). Click on the button siguiente

In this window there are two elements
- El rango de datos: Represents the graphic vertical axis. It will be built from G3:G6
- Las categorias: Represents the horizontal axis. The data is in the range F3:F6.

The last step allows us to choose a title, the axes titles and enable a grid of horizontal and vertical lines. Use the image below to fill in this window

Click in finalizar the graphic to create.

Once created we can modify the graphic improving some format elements. Click in the graphic title to get it selected. Then place the cursor over the title and use the contextual menu to select the option formato de título
Change the size font to 12, the style to bold, and the color to red

ENGLISH TIP: In english the word Eje in singular is Axis, and in plural Axes

Add a relief effect (efecto de relieve) as shown in the image below.

Modify the vertical and horizontal axes title using the same procedure. Change to your liking the color font and size. Remember, you can use the contextual menu option formato título.

Now format the data columns as explain in the following steps. First click on any column to select all them, and deploy the contextual menu and select formato de series de datos. You can modify any of the following elements.

• Area: change the background color, set up a frame or an image as a background.

• Transparency: set up a transparency or a graduated shading (un color degradado).

• Borders: change the thick and the color of the column outline

For example:

To achieve this results we have selected a graduated shading (tipo cuadro) and a transparency

Insert a línea de valor medio. This is a line that marks the average value of the data series. To make it visible deploy the contextual menu (while selecting the columns) and select Insertar Línea de Valor Medio.
Insert a label showing every column value activating the contextual menu option Insertar etiqueta de datos.

Select again the contextual menu and select formato de etiquetas de datos. Change the label font, color,size. Turn 45º the labels

Select on the contextual menu the option formato línea de valor medio cambia and change the color and thickness of the line.

Double click in the X axis to open its format window. Change the font type, color, and size of the axis title. Add a shade effect ( tab Efecto de fuentes, option sombra).