14. Spreadsheets
Exercises
The basics of spreadsheets
The interface of a spreadsheet program is usually a two-dimensional table consisting of vertical columns and horizontal rows. One box in a table is called a cell. The most well-known spreadsheet programs are MS Excel and Quattro Pro.
Each cell has an address based on a column and a row. For example, the address C4 refers to a cell in the fourth row of the third column, i.e. column C.
Three types of information can be entered in the cells:
- text
- numbers
- calculation formulas
Examples
Example 1
The prices and quantities of the products have been entered in the table below. The line-by-line prices for cells D2, D3, D4 and D5 have been calculated based on their formulas.
A | B | C | D | |
---|---|---|---|---|
1 | Product | Price per unit | Amount | Total |
2 | apple juice | 2 QAR | 4 | 8 QAR |
3 | bread | 6 QAR | 2 | 12 QAR |
4 | chocolate pudding | 2,40 QAR | 5 | 12 QAR |
5 | comic book | 14 QAR | 1 | 14 QAR |
6 | ||||
7 | Total price | 46 QAR | ||
8 |
For example, the result of cell D2, 8 QAR, is obtained by the following formula:
Correspondingly, the result of cell D3, 12 QAR, is obtained using the following formula:
=B3*C3Finally, the total price of purchases in cell D7 is calculated in the table as follows:
=D2+D3+D4+D5The most commonly used calculations are made into ready-made calculation formulas or functions. The total price of the products can be calculated using the ready-made SUM function. Once the type of formula has been selected, use the mouse to delimit the numbers in the table whose sum you want to calculate. The program handles the rest automatically. You can select the type of formula from the menu or enter it in cell D7 as follows:
=SUM(D2:D5).Example 2
The areas of rectangles have been calculated in the table using different dimensions for their bases and heights.
A | B | C | |
---|---|---|---|
1 | base | altitude | surface area |
2 | 2 | 5 | 10 |
3 | 3 | 7 | 21 |
4 | 5 | 12 | 60 |
5 | 8 | 9 | 72 |
6 | 10 | 17 | 170 |
7 |
For example, the value of cell C2 (10) is obtained with the following formula:
=A2*B2The value of cell C3 (21) is obtained using the following formula:
=A3*B3
Note! The formula does not necessarily have to be written separately for each cell, but can be copied from cell C2 to other cells. This can be achieved by moving the mouse cursor is to the right bottom corner of the cell, which makes it turn into a plus sign. The plus sign can then be dragged over other cells, which copies the formula of the first cell to other cells.