Class 1
1. Relative and absolute references.
Task 1A: Generate the following multiplication results using a single formula over the whole result (grey) range:
1 2 3 4 5 6 7 8 9 10
1 1 2 3 4 5 6 7 8 9 10
2 2 4 6 8 10 12 14 16 18 20
3 3 6 9 12 15 18 21 24 27 30
4 4 8 12 16 20 24 28 32 36 40
5 5 10 15 20 25 30 35 40 45 50
6 6 12 18 24 30 36 42 48 54 60
7 7 14 21 28 35 42 49 56 63 70
8 8 16 24 32 40 48 56 64 72 80
9 9 18 27 36 45 54 63 72 81 90
10 10 20 30 40 50 60 70 80 90 100
Task 1B: Based on the above results, generate the following summation results using a single formula over the whole result (grey) range:
55 111 168 226 285 345 406 468 531 595
55 112 171 232 295 360 427 496 567 640
55 113 174 238 305 375 448 524 603 685
55 114 177 244 315 390 469 552 639 730
55 115 180 250 325 405 490 580 675 775
55 116 183 256 335 420 511 608 711 820
55 117 186 262 345 435 532 636 747 865
55 118 189 268 355 450 553 664 783 910
55 119 192 274 365 465 574 692 819 955
55 120 195 280 375 480 595 720 855 1000
The summation is based on the following scheme:
1 2 3 4 5 6 7 8 9 10
2 4 6 8 10 12 14 16 18 20
3 6 9 12 15 18 21 24 27 30
4 8 12 16 20 24 28 32 36 40
5 10 15 20 25 30 35 40 45 50
6 12 18 24 30 36 42 48 54 60
7 14 21 28 35 42 49 56 63 70
8 16 24 32 40 48 56 64 72 80
9 18 27 36 45 54 63 72 81 90
10 20 30 40 50 60 70 80 90 100
Hints: use the keyboard: Arrows, Ctrl+arrows (navigation), Shift+arrows, Ctrl+Shift+arrows (selection), Ctrl+C, Ctrl+V (copy & paste), F4 (changing the reference type).
2. Date and time
Task 3A. Type the TODAY and NOW functions. Format the cells as numbers with 3 decimals. How are the date and time encoded?
Task 3B. Type “1” in a given cell, and format it as a date.
Task 3C. How many days (hours, minutes) is it since you were born?
Task 3D. How many minutes (seconds) is it now since the beginning of this class (8 a.m.)?
3. The rounding problem
Task 2A. Prepare a column of 10 random values ranged between 0 and 100 (using the RAND function). Format them in such a way that 2 decimals are visible. In the next column calculate the rounded (2 decimals) numbers using the ROUND function. Calculate the totals in both columns and press F9 several times. Why are the totals different at times, even though the arguments look literally the same (as in the example below)?
81.33 81.33 57.62 57.62 90.35 90.35 81.73 81.73 6.18 6.18 68.98 68.98 31.24 31.24 89.95 89.95 5.82 5.82 49.31 49.31 423.57 423.56
Task 2B. Try the “Precision as shown” option. What is the effect? Now turn this option off again.
