4. Spreadsheet

We will now learn how to use a spreadsheet. Everything will be here very logical and will not only exercise aour computer skills but also reasoning skills. Learning to use a spreadsheet is important because it can be used in many situations. For example in planning family expenses or to work with grades in a class and in many business or scientific contexts.

We will use this document and we will be trying to fill it with formulas.

Generating enumeration

To insert a column on the left right-click the header of the first column and choose insert a column to the left. Type in LP.

Next I type in 1 into the first cell. We will generate the rest of the enumeration automatically — we just have to drag the first cell by the black square in the right lower corner to the end of the list of countries. Dragging can do different things in a spreadsheet, for example it can copy cells. In our case the spreadsheet guesses that we want to generate enumeration.

Visibility of columns when scrolling

We would like to freeze two first columns, which means that they will be visible no matter how far to the right we scroll our sheet. It is often comfortable to know to which country belongs the data we see after scrolling.

I select the first column which can be scrolled (in this case the third one) and click Window -> Freeze.

Obviously similar thing can be done to the rows or to the rows and columns simultaneously.

Simple formulas

Let as calculate the population density in Albania. In the suitable cell we insert =. This character causes that the spreadsheet now awaits a formula. By the way, if I would like to insert a text beginning with = I should precede = with an apostrophe (this is a rule for all texts which can mean something different for the spreadsheet). The formula I need should divide the number of people in Albania by the Albanian area, which mean dividing cell D20 by C20. So I inert D20/C20. Instead of inserting manually the addresses of the cells I can also click them. And done.

Coping/dragging formulas

Now we would like to copy the formula to other countries. I can copy it right away or as before drag it by its lower right corner. Notice that it will not only be copied but also the addresses of the cells in it will be changed automatically. Nice, isn’t it?

Using functions in formulas

Besides the simple arithmetic operations as +, – , *, / in our formulas we can also use predefined functions. That is how we will calculate the average population of a country. Start with = and then type in AVERAGE or choose it from the list of all functions by clicking on the button with fx symbol on the right. In parentheses we give the function its argument which is the scope of cells which should be used to calculate the average. You can select this scope using the mouse or type it in in the following format: the address of the first call in the scope : the address of the last cell. Press enter or click OK, and we are done.

Function IF

In the yellow column we have to put information of whether the density is small or large depending on whether it is less or greater from the average.

We will use the IF function. As before we start with a formula for Albania. Type in = and type in or choose function IF. It takes three arguments, the first one is a condition. In our case we have to check whether the population of Albania is greater that the average which we have just calculated. We type in E20>D68 (because E20 is the address of the cell with the population of Albania and D68 is the address of the cell with the average). The arguments of a function should be separated by a semicolon, so we insert a semicolon. The second argument is the value which will appear if the first argument will be true. In our case it is the word big. We use quotation mark around it to tell the spreadsheet that it is a word and not another function. After the next semicolon we have to insert a value which will appear if the condition in the first argument is false. In our case it is the word small, again in quotation marks.

Using dollar sign

Now we would like to copy our formula to the other countries. But if we do it straight away it will give bad results, since the spreadsheet will not only change the addresses of cells with the density values (that is ok) but also the address of the cell with average value (that is bad). We have to tell it not to change the second. That is what the dollar sign $ is for. Insert $ before the number of row in the address of the cell with the average in the formula. Instead of D68 we will have D$68. And now we can drag it. This row number will not be changed. Great.

If we would like to drag things horizontally we can also block the column letter by putting $ before it. To block whole address put $ in front of the letter and in front of the number.

Function VLOOKUP

Next column is quite a complex task. We are supposed to state the size of a country using the table prepared beneath. In this table we will be searching for suitable text. We will use the function VLOOKUP.

Let us start with Albania. I type inn = and choose VLOOKUP. This function needs four parameters. The first one is the value we will be searching for, in our case it is the area of Albania, meaning the cell C20. After a semicolon goes the second parameter — the range of cells forming the array in which we will be searching for an answer. In our case it is range C71:D76. The spreadsheet always assumes that the first column of this scope is a column in which the value has to be searched for. On the other hand the next parameter defines in which column of the range the needed answer can be found. In our case it is the only other column, the second one. The last argument decides the type of searching. If the array is sorted and we would like to find the last entry lower than the value we search for, we have to type in 1. On the other hand in the case of unsorted array and if we would like to find only precise value, type in 0.

Albania is done — we need to drag the formula to generate formulas for the rest of the countries, but before that we have to think about the dollar signs. The range defining the array we search in should not change in the cells below, so we put in dollar signs in their addresses in formula: C$71:D$76 — and we drag the formula afterwards.

There is an analogous function HLOOKUP which search for values horizontally instead of vertically.

Functions MAX and MIN

Those functions can be used similarly to the AVERAGE function (as in the case of AVERAGE the argument is the range) and we use them to calculate minimal and maximal population of all the countries.

Naming cells

Instead of using always cell’s address we can name a cell an use its name instead. We will give a name to a cell with maximal population, because we will use it again in a while. To do this, select this cell and type in a name (ex. Maxpop) in a text field on the left on your toolbar instead of its address.

Function LOOKUP

Function VLOOKUP has one major flaw. The values we search for need to be in a column left to the column with answers we expect to choose from. That is not the case when we would like to find the country with maximal population, which we have just calculated. The column with the names of the countries is to the left from the column with their population.

In this case we have to use function LOOKUP, which accepts three arguments. The first one, as before, is the value we are looking for. In our case it is the cell we have named Maxpop. After a semicolon we give a range in which we will be looking for this value in. In our case it is the column with population, D20:D66. And finally after the last semicolon we give the range with answers. In our case this is the column with names of the countries, B20:B66.

Function COUNTIF

Let us calculate the number of international organizations of a given country. We simply have to count the occurrences of the word yes in four cells in the row of the given country. As usually we start with Albania. We will use COUNTIF function. It takes to parameters. The first one is the range in which we will be counting. In our case it is the range H20:K20. The second is the value we will be counting. In our case it is the word “yes”. Remember about the citation marks. Done.

Now we can drag the formula to the other countries, because the range should be changing in the formulas below.

Function SUMIF

To calculate population of the whole UE countries we have to sum up populations of countries but only those which are in UE. We will use SUMIF function. Type in = and choose SUMIF. It goes with three parameters. The first is the range H20:H66. After a semicolon we should give a condition to be checked. In our case it is the word “yes”. Finally the last argument is the range to be summed up in the places where the condition is true. In our case it is the column with population of the countries, D20:D66. And we are done!

Dates

It may be somehow surprising but dates in a spreadsheet are kept as number of days between the given date and 30.12.1899. We can see it by changing formatting of a cell from date to number. That is why we can simply subtract two dates and we will get the number of days between them.

There is also function NOW(), which do not need any arguments, and gives current date (and time). Therefore to calculate the number of years of Austria in UW we need to subtract the date of its accession from NOW() and this value (use parenthesis) divide by 365.

Nested functions

But after dragging the formula to the rest of the countries we see that there is a problem. Even the countries which are not in the UE have their years in UE calculated as if they were in UE from 30.12.1899. We need to do something with it. We will use IF function to check whether a country is in UE and use the already created formula as one of its arguments. Let us modify the formula for Austria. The first argument in IF if the condition. In our case H22=”yes”. After a semicolon we put the value which should appear if the condition is true, the formula we have created, (TERAZ()-M22)/365. The third argument is the value which should appear otherwise. In our case it should be an empty cell, so we type in an empty text “”.

Ready — we can drag the formula to the rest of the countries.

Formatting cells

We have mentioned it when talking about dates, that it is possible to change formatting of a cell. It changes not the value but only the way in which the cell is shown. In our case we would like to see the value rounded up to integer number of years. Select the column and click Format -> Cells -> Numbers and decrease the number of decimal digits to zero. Similarly we deal with the column with density. This time we decrease the number of decimal digits to two.

Remember that this changes only the view, and not the value. If you want to round up the value use the function ROUND.

Chart

Let us now create the chart. Click Insert -> Chart. The first stage is to choose the desired type of the chart. We need XY (scatter). Choose it and click next.

The second stage is choosing the range, from which we will get data. We will need three columns — the one with the names of the countries, the one with areas and the one with population. Click on the button to choose them from the sheet and select this range. I select that the data series are in columns and that the first column are labels and proceed to next.

In this stage we define data series. I delete all the series defined automatically because they do not make any sense — we will have only one data series with will consist of points related to each country. Therefore I add new data series and give it title Countries. X data is the column with countries’ areas and Y data is the population column. After selecting them click next.

The next step allows us to change some details. The chart name and the axis’ captions. I have only one series of data so I will nod need any legend.

Move the created chart to any free space.

Formatting a chart

The chart is still not very readable. We can fix it slightly. Start by stretching it slightly vertically.

Secondly, the most of countries are cramped on the left side of the chart. We can change the axis type to logarithmic (they will increase not by 10 but 10 times at each step). Click twice on the chart. The bar on the top of the window will change. Click Format -> Axis -> X axis and choose logarithmic scale. Repeat this for Y axis.

But the smallest countries have disappeared because Y axis starts with 1 million, but for example Vatican has only 0.001 million citizens. Click once more Format -> Axis -> Y axis and set manually minimal value to 0.001.

I can also add labels to the data points (some will obviously be unreadable. Click Insert -> Data labels an choose category.

There are also many other options we can use.

Sorting

To sort data we have to first select a range (select the whole range with data about the countries). Then click Data -> Sort and choose the column we would like to sort by. In our case it is area, column C. We would like the data to be sorted in ascending order.

Filters

Filters are used to hide those rows which do not satisfy a given condition (they do not delete those rows, only hide them). Select the whole range with data. Click Data->Filter -> Standard filter. I choose column I (data about membership in NATO) and value yes. Click ok.

To show the hidden rows you can select the surrounding rows and right-click on them and select Show.

Formatting the page and generating a pdf document

Clicking View -> Page breakup preview, you can see how the sheet will be divided into pages when generating a pdf document or printing.

We would like to fit all the date to a single page. Click Format -> Page and in the tab Sheet I choose the scale to be set to fit on number of pages and set the number of pages to 1.

Generate a .pdf as in the case of text processor — there is an appropriate button in the toolbar.

Assignment

Assignment.