SUM and AVERAGE · Martinとコード

A function is a ready-made calculation

=B2+B3+B4+B5+B6 works. With five grades you can watch it. With twenty it becomes a tangle. You forget B17. That is why functions exist.

A function has a name and brackets. Into the brackets you put what it should look at.

A colon means “from–to”. B2:B6 is B2, B3, B4, B5 and B6. Five cells. You do not have to list them.

=SUM(B2:B6)
=AVERAGE(B2:B6)

Czech Sheets / Excel sometimes wants SUMA and PRŮMĚR. If the function “does not exist” and the cell shouts #NAME?, try the other language. It is not a mistake in your head — it is the account language. English AVERAGE a Czech Excel often does not know. Sheets sometimes can do both. If not, switch the formula language, or use the version the spreadsheet suggests.

💡

After = start typing SUM. The spreadsheet suggests a list. With the arrow pick and Tab fills the bracket. Then with the mouse select range B2 to B6 — the addresses write themselves. Enter. You do not have to tap B2:B6 letter by letter.

In Excel on a computer on the Home tab is the AutoSum button (Σ). It clicks under the numbers and offers SUM. Average is in the drop-down next to sigma. In Sheets you find sigma at the top too. Now we learn to write it by hand. The button is a shortcut, not different maths.

Count it on paper first

So you know if the spreadsheet lies. Take five grades:

Cell	Subject	Grade
B2	Maths	1
B3	Czech	2
B4	English	1
B5	Science	3
B6	History	2

Sum: 1+2+1+3+2 = 9.
Average: 9 ÷ 5 = 1.8.

If the formula in B8 shows 9, you summed instead of averaged. If it shows 1.8, a hit. If it shows 2, it rounds to whole numbers — do not worry now, it is enough to see 1.8 or 1.80. If it shows 0, the range looks outside the grades (empty cells). If #DIV/0!, there is no number in the range.

Grades for real

On sheet Grades have at least five subjects in column A and five grades in column B. Numbers, on the right. Row 1 still Subject and Grade.
Click the first empty row under the last grade. With five grades that is row 7. In A7 type Total.
In B7 type =SUM(B2:B6). Enter. You must see 9 if you have grades 1, 2, 1, 3, 2. Other grades → another sum. That is fine. With grades the sum is more a check than a useful number. It watches that the function lives.
In A8 type Average. In B8 type =AVERAGE(B2:B6). Enter. With the sample five grades 1.8.
Click B8. In the formula bar there must be a function, not a hard 1.8. Click B7. There SUM.

If you have six subjects, the range is B2:B7 and put the labels in A8 and A9. The function must look at all grades and at no label. If you accidentally take the total into the average too (B2:B7 while the total is in B7), the average goes mad.

⚠️

The range must not reach itself. If you put =SUM(B2:B7) into B7, the spreadsheet adds the cell where the total lives. You get error #REF! or a circular reference. Always add above yourself, not yourself.

What you will see when it fits — and when not

It fits: B7 a number (the total), B8 a smaller number around 1 to 5 (the average). Both on the right. The formula bar starts with =SUM / =AVERAGE.

#NAME?: wrong function language, a typo (AVERGE with one E). Delete, let the hint after = help.

#DIV/0!: an average from an empty range. Grades are not in B2:B6. Look from which row the data starts.

Average 9: in B8 you have SUM. Swap the functions.

The average does not move when you change B3: the range does not take that cell. For example you have B2:B2 or B4:B6. Fix to B2:B6 (or to your last row).

🏫 Grade average — the formula

The average of 1, 2, 1, 3, 2 is 1.8. This number is the core of the project.

Change B5 from 3 to 1. The total jumps from 9 to 7. The average from 1.8 to 1.4. Put the three back if you want the original exercise.

When you have your own grades, leave labels Total and Average in column A. In a month otherwise you do not know what is 1.8 and what is 9.

Do not make a mini dashboard from it yet. Today two functions under each other. A chart and a maximum come later.

Second exercise: an average from pocket money

On Pocket type seven numbers into B2 to B8 — what came in on each day. For example: 50, 0, 20, 0, 0, 100, 0.

Under them, in B9, =SUM(B2:B8). With that example 170.
In B10 =AVERAGE(B2:B8). ≈ 24.29. A zero on Tuesday belongs in the average. An average of income for the week counts days when nothing came in too. If you want an average only from days with money, that is not this yet. Today it is enough: the function looks at seven cells.

Labels in A9 Total, A10 Average. The same habit as with grades.

Now you 💪

First grades, then movement.

Under the grades is Total and Average with a label in column A.
Click the average. The formula bar shows AVERAGE and a range like B2:B6.
Change one grade. For example B3 from 2 to 1. The average moves. The total too. Put the grade back, or leave the new one — it is yours.
If the average does not move, the formula does not look at that cell. Fix the range.
Second check: the sum of five sample grades 1, 2, 1, 3, 2 is 9. If you have other grades, add them on your fingers and compare with B7.

✅

Check: Under the grades is an average, not a pencil on paper. You change a grade → the average recalculates. The function looks at a range from:to, not five pluses in a row.

What to take from this lesson

SUM adds a range. AVERAGE averages it. If one does not exist, try the other language (SUMA / PRŮMĚR).
B2:B6 = from–to. A colon, not a semicolon. (A semicolon separates other things in a function — you do not need that yet.)
A label next to the result, so you know what the number means.
A function must not add the cell it lives in.
The average of 1, 2, 1, 3, 2 is 1.8. If you get 9, you have SUM there.