This is a Clilstore unit. You can .
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
Start LibreOffice.org Calc selecting the menu Aplicaciones→Libreoffice→ Hoja de cálculo. Open your personal template.
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.
Every column is named with a letter (A, B, C,..., AA, AB, AC,....)
Every row is numbered(1, 2, 3, 4,...).
Every cell is name by its column and row. 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 the active cell. Write into it Francia. Notice that as you write in the cell the text is also shown in the formula bar (look at image below). The text can also been written or modified 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 until 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 through Europe. The traveler is going to visit a different country the first day in every month, starting in January.
Select D2 and write 01/01/2016. Then select D3 and write 01/02/2016
Select D2:D3 (this means from D2 to D3), and click in the autofill handle (remember, the little black square on the bottom right corner and drag it down to D11
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.
Write No en 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 (números normales), as money, as percentage, 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
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)
Selecct the cell H2
Place the cursor in the formula bar and write the formula =F2+(F2*G2). Important every formula must start with a = character. Don't forget it.
Last, 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 cities of the countries in column B, and the sheet will assess (evaluar) if the answer was correct.
Click in the cell D2 and write =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), 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”)
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 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.
Write in G4 =hoy().
• 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)
Save a backup copy with the name ejercicio3
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
< 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
Write TABLA DE RESTAR, in C1 as the sheet headline.
Select C1:D1, deploy the contextual menu, and select the option Unir celdas. This joins these three cells in one. There's also a button unir celdas in the toolbar
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 join the cells B2:F2.
Select the joined cells and deploy the contextual menu
Formatea las Celdas→solapa alineación. Select the alignment set up 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.
In the column B must appear the number entered by the user in D4. 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 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
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 r 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
EXERCISE 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
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 graphics:
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º repetidores
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
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).
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
Select all the data except the title
Click on the button diagrama
Select circle 3D
No change needed in intervalo de datos, either in series de datos
Put Superficie de los continentes, as the title and Millones de Km2 as the subtitle
The result should be like this:
Double click in the graphic title. Change the font size and color to your liking
Double click on the graphig area, and set a mármol bitmap and a lineal graduated shade as background color
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
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
Select the whole table
Click in the button diagramas
Select gráficos de líneas, type sólo líneas
In intervalo de datos , select series de datos en filas, primera fila como etiquetas, primera columna como etiquetas
Press siguiente until the window títulos del gráfico in the wizzard. Set the title Necesidad de agua en el mundo (m3/hab)
The result must be something like this:
Modify this graphic as you did in the previous exercices. The result must be something similar to this
This was the last Calc exercise
Upload the file with all the exercises to Moodle
Short url: http://multidict.net/cs/4617