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

UNIT 5 CALC

 

 

 

 

 

 

SPREADSHEET EXERCICES

LIBREOFFICE CALC

 

 

 

4 ESO - CLIL

 

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

 

 

 

 

 

 

 

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

 

EXERCISE 1. PAÍSES

 

 

 

 



 



The gridded space is known as Work area. Each rectangle in the grid is called 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 enter data.

 

 

 



Open the sheet 1.Países

 



 

 

 

 

 

 

 

 

 

 

 

 



 

 

 

 

 

 

 

 

 

 

 

 

 

Result expected:

 



As you can see, we can enter in a cell different data types: Text, numbers, dates. There are other data types too: Select E2. Our traveler will write in this column if 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.

 



One data type, can also be represented in different ways. For instance, numbers , can be represented as regular numbers (números normales), as money, as percentage, and in scientific notation

 





 

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.



 

 

 

 




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 every country the VAT is a whole number with no decimals Delete this decimals using these buttons

 

 



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)

 



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

 

 

 



 

 

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 cities of the countries in column B, and the sheet will assess (evaluar) if the answer was correct.

 

 

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), expression 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 will be FALSO

 

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

 

 

 

 

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 until today.

 

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 until today?

Increase the font size and highlight the text using bold letters. The format bar is about the same the one you learned to use in the LibreOffice Write unit

 

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 thicker borders using the menu Formato celdas→ Borde .

 

 

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

 

 

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 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 user clarifying instructions as shown in the image. Try to translate them to English, (of course)

 

 

 

 

 

 

 

EXERCISE 4 SUBTRACTION TABLE

 

 

Open the exercises template in the sheet 4. Restar. Select a random cell and write =8+7 and tap 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 use also 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

 

 

 



 

 



 

 



 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Botón bordes.


Botón color de fondo

 

 



 



 

 



 

 



 

 



 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

EXERCISE 5 MULTIPLICATION 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

 



 

 

 

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

 

 

 

 

 

 

 

EXERCISE 7 DEPARTMENTS

 

   

 

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

 



 

 

 

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



 

 

 

 

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:

 

 

 

 

 

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:

 

 

 

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

 

 



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.

 

 

 



 

 

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:



 

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

 

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 graphics:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 



 

 

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

 

 

 

 

 

 

 

 

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º repetidores

8

14

18

9

 

 

 

 

 

 

 

 

 

 

 

 

 

a

 

 

 

 

 

 

 

Click in finalizar the graphic to create.

 

 

 

 

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.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

 

Format the axis Y in the same way you did in the previous step. Change the axis scale from 10 to 5 using the option intervalo principal

 

 

 

 

Here the result so far:

 

 

 

 

The Área de datos is the square space between the two axes. Double click on any place in the area de datos to get is set up window

 

 

 

Change the Borde, Área and Transparencia. Set a 70% transparency, red background color and a pattern (whatever you like)

 

 

 

 

 

 

The result expected is:

 

 

 

Modify again the area de datos. Set the the options shown in the picture below

 

 

 

This must be the final result :

 

 

 

Save a back up copy and name it ejercicio13

 

EXERCISE 14 CONTINENTS

 

Open the sheet 14 continentes in your exercises template.

 

 

The sheet contains the following table

 

Continentes Millones Km2

Asia

44

América

42

Europa

10

África

30

Oceanía

9

Antártida

14

 

 

 




 

 



 



 

Put Superficie de los continentes, as the title and Millones de Km2 as the subtitle

 



 

The result should be like this:

 



 

 

 

 

 


 

The result should be like this:

 

 



Save a backup copy and name it ejercicio14

 

EJERCICIO 15. Oceanos

 

Open the sheet 15 Oceanos in your template

 

Océanos Millones Km2

Atlántico 84

Ártico 14

Antártico 24

Índico 74

Pacífico 165

 

Make a sector diagram (tipo "círculos") from this table. Try to achieve the following objectives:

 

Títle and legend properly formated as seen in the previous exercise.

In each sector there must be a label with the represented value.

Set up an attractive background using a bitmap.

 

 

Save a backup copy and name it as ejercicio15

 

EXERCISE 16 WATER

 

Open the sheet 16 Agua in the exercise file

 

We are going to make a lines graphic from the table below

 

Necesidad agua en el mundo (m3/hab)

1.900 1.980 2.015

Hogar 11,50 29,09 71,43

Agricultura 201,25 469,93 600,00

Industria 17,25140,98 328,5

 

 





 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 



 

 


This was the last Calc exercise

 

Upload the file with all the exercises to Moodle

 

 

Short url:   http://multidict.net/cs/4617