In Year 2, at the Italian technical institutes, the first-two-years computer science syllabus is working (or about to work) on spreadsheets — references, functions, charts — before moving on to algorithms, flowcharts and the first programming language. The practical test at the PC on these two parts typically arrives between the beginning and the end of November, so six to ten weeks from now, with the test on algorithms straight afterwards. They are two different topics but linked: the spreadsheet teaches you to reason about data and formulas, algorithms teach you to reason about instructions — and whoever understands the first well arrives better prepared for the second.
Note on the function names: below I use the English names of the spreadsheet functions (SUM, IF, VLOOKUP…). In the Italian version of Excel or Google Sheets they have Italian names (SOMMA, SE, CERCA.VERT…) and arguments are separated by semicolons instead of commas; the logic is identical.
The concrete problem: why a table “breaks” when you drag it
What I see most often, when a student brings me an Excel or Google Sheets file that “doesn’t work”, is a table where a formula that is correct in the first row becomes wrong as soon as you drag it down. The culprit, almost always, is the difference between relative and absolute references, and it is exactly the point on which almost every Year 2 practical test turns.
Step-by-step procedure: references, functions, charts
1. Relative and absolute references: A1 versus $A$1
When you write a formula such as =B2*C2 in a cell and then drag it downwards or to the right, the spreadsheet does not copy the identical formula: it adapts it, shifting the references by the same number of rows or columns that you dragged. This behaviour is called relative referencing, and it is the default of every cell written without special symbols.
The $ symbol locks the part it is placed in front of, preventing it from adapting when dragged:
A1— relative reference: both the column and the row adapt when you drag;$A$1— absolute reference: both the column and the row stay fixed, alwaysA1in every cell where you drag the formula;A$1— mixed, row locked: the column adapts, the row always stays1;$A1— mixed, column locked: the column always staysA, the row adapts.
The classic case that breaks a whole table: you have a VAT rate in a fixed cell, for example B1, and you use it in a formula like =A2*B1 to calculate the total of row 2. If you drag that formula down without the $ symbol, at row 3 the spreadsheet will calculate =A3*B2, at row 4 =A4*B3, and so on: the reference to the rate “slides” away from the right cell. The correct formula to drag is =A2*$B$1: that way the reference to the price (A2) stays relative and adapts to each row, while the reference to the rate ($B$1) stays locked on that cell, always.
2. The functions you really need
In most Year 2 exercises the same functions always come back, and it is more useful to know how to use them well than to know a hundred by heart:
=SUM(A2:A10)— adds up a range of cells;=AVERAGE(A2:A10)— calculates the average of a range;=IF(B2>100,"yes","no")— returns one value or the other depending on a condition: it is the basic logical function, the one that introduces the concept of a condition in the spreadsheet too, even before seeing it in a programming language;=COUNTIF(B2:B10,">50")— counts how many cells in a range satisfy a condition;=SUMIF(B2:B10,">50",C2:C10)— adds up the values of a range, but only for the rows that satisfy a condition on another range;=VLOOKUP(A2,$D$2:$F$10,3,FALSE)— looks for a value in the first column of a table and returns the corresponding value in another column of the same row: here the absolute reference on the table ($D$2:$F$10) is not a detail, it is almost always mandatory, because otherwise the search range “slides” when you drag the formula exactly as in the rate example.
3. How to choose ranges
A trivial but frequent mistake is selecting a range that does not cover all the rows with data, or that covers too many (empty rows at the bottom of the table), distorting AVERAGE or COUNTIF. The practical rule: before writing the formula, look at where the data really ends (the last filled row), and use that range — not an arbitrary “generous” number chosen by eye.
4. Which chart to use for which data
In the practical test you almost always have to choose the right chart for a set of data, and here the commonest mistake is always using the pie chart because it “seems the most complete chart”:
- bar chart: when you have to compare different values across different categories (the sales of four products in a month) — it is the right choice in most cases;
- line chart: when you have to show the trend of a value over time (the temperature recorded every hour, sales month by month) — lines make the trend visible, which with bars is harder to see;
- pie chart: only when you have to show how a total is divided into parts (the percentages of votes in a survey) — and only if the categories are few, no more than 5-6, otherwise it becomes unreadable.
The pie chart is almost always the wrong choice when the data change over time or when there are many categories: it is the most “recognisable” chart but also the one most often used inappropriately.
From the flowchart to code
The second half of the syllabus changes tool completely, but does not change the way of reasoning: it is always a matter of taking some data, applying some rules, and producing a result — first with formulas in the sheet, now with instructions in a language.
1. The flowchart: the basic elements
A flowchart describes an algorithm with a few standard symbols:
- an oval for start and end;
- a rectangle for an instruction or an operation (assigning a value, doing a calculation);
- a parallelogram for input and output (reading a datum, printing a result);
- a diamond for a decision, with two exits (yes/no, true/false);
- an arrow to link the blocks and indicate the order of execution, which in a loop goes back to a block already passed.
2. The same algorithm, first as blocks then in code
Here I use Python because it is the most widely used language for starting out — if your school uses C++, the logical structure (variables, input, decision, loop, output) is identical: only the syntax changes.
3. Types and casting: the mistake the program doesn’t report
In Python (and in almost every language), the function that reads a datum from the keyboard always returns a text string, even if the user types a number. This produces the most common mistake of the first module:
age = input("How old are you? ")
double_age = age + age
print(double_age)If the user types 5, this code does not print 10: it prints 55, because age is the string "5", not the number 5, and the + symbol between two strings concatenates them (sticks them one after the other) instead of adding them. To get the number you have to explicitly convert the string into an integer with int() — this conversion is called casting:
age = int(input("How old are you? "))
double_age = age + age
print(double_age)Now, with age = 5, the program correctly prints 10. The rule to remember: every time you read a datum with input() and need to do a numerical calculation with it, it has to be converted with int() (whole numbers) or float() (numbers with a decimal point) before being used in an arithmetic operation.
The typical mistake to avoid
The costliest mistake of all is forgetting the casting after input() and discovering it only when the program produces an absurd result without reporting any error: Python does not stop, it performs the string concatenation anyway and returns a number that looks plausible but is wrong. Get into the habit of asking yourself, every time you write input(): “do I need the value I am reading as text or as a number?” — and if it is needed as a number, the casting is written on the same line, not added after something doesn’t add up.
Two worked examples
Exercise 1 — spreadsheet: a spending table with a calculated column.
You have a table with column A (product name), column B (unit price) and column C (quantity bought), from row 2 to row 6. In E1 there is the discount percentage for purchases over €100, for example 10%. You have to calculate in column D the row total, with the discount applied only if the total before the discount exceeds €100.
Procedure cell by cell. First we calculate the gross row total in a helper column (or directly inside the formula): =B2*C2 is the total before the discount for row 2. Then the final formula in D2 has to check whether that total exceeds 100 and, if so, apply the discount taken from E1 with an absolute reference (because there is only one discount, in a fixed cell, and the formula must be draggable down all the rows):
D2: =IF(B2*C2>100,B2*C2*(1-$E$1),B2*C2)Dragging this formula from D2 to D6, the references to B and C adapt to each row (relative), while $E$1 stays always pointing at the same discount cell (absolute). If at row 3 the gross total were €120 and the discount in E1 were 10%, the result would be 120 × (1 - 0.10) = €108; if at row 4 the gross total were €60, below the threshold, the result would stay €60 with no discount.
Exercise 2 — the same problem as an algorithm: flowchart and Python code.
The same problem, simplified to a single purchase: read the unit price and quantity, calculate the total, apply the 10% discount if the total exceeds €100, print the final total.
Flowchart, described in words: Start → Input unit price → Input quantity → Calculate total = price × quantity → Decision: total > 100? → (yes) Calculate total = total × 0.9 → (in both cases it merges into) Print total → End.
Python code:
price = float(input("Unit price: "))
quantity = int(input("Quantity: "))
total = price * quantity
if total > 100:
total = total * 0.9
print("Total to pay:", total)Checking the result. With price = 15 and quantity = 8, the gross total is . Since , the discount is applied: . The program prints Total to pay: 108.0 — the same result as exercise 1 with the numbers of row 3, obtained this time with an algorithm instead of a formula.
For computer science in the first two years of the technical track we do not yet have a dedicated simulation: in the high school simulations you will find those for maths and physics, the closest category for practising timed exercises — the correct answer is free, the guided solution unlocks with the first lesson.
Where this topic leads
The spreadsheet and the first algorithm are not two isolated parentheses: they are the foundations on which the whole rest of the course is built. If last year, in Year 1, you saw number systems and binary, here you are taking the next step: from how a computer represents data to how you write a program that processes it. And next year, in Year 3, code will become the main subject with nested loops and arrays — here we deliberately stopped at the first loop and the first decision, that basis is all needed to tackle the next jump well.
Computer science lessons are online, one-to-one, and often the block is not understanding “what” an IF does, but getting used to breaking a problem into small steps, one at a time. If you want a hand, the first chat is free → contact.
