Dirt spoils the number
An average of five grades is clear. An average of five grades, an empty row, n/a and 2 (with a space) is a lottery.
Before you trust SUM, clean up. You cannot make a nice chart from a dirty table. A filter on Apple / apple / apple makes three flavours.
Clean data means four things:
- Empty rows in the middle — delete the whole row, not just the cell. A hole tears the filter and the range.
- Text in a number column —
absent,n/a,?out. Either a number, or empty. - An extra space —
2looks like a two and behaves like text. Rewrite it. - Two headings — then the filter does not know where the table starts.
Do not add “by eye” across the whole column B:B when at the bottom you also have the word Average or a label. The range B2:B6 is safer than “everything in the column”. =SUMA(B:B) can add the total too and you get a double. Or #HODNOTA!, when text falls into the column.
What n/a does to the average
Do not just read it with your eyes. Make some dirt and wipe it off again.
On Znamky in B2:B6 have numbers 2, 1, 3, 2, 1. Average in B8: =PRŮMĚR(B2:B6) or =AVERAGE(B2:B6). It should come out 1.8.
-
In B3 on purpose write
n/ainstead of the one. Look at B8. -
In Sheets the average often skips text and counts the rest. The number changes (now from four grades, not five) and pretends it is fine. In Excel
#HODNOTA!/#VALUE!can pop up. Both situations mean: in the number column there is a sentence. -
Put the number
1back in B3. The average must return to 1.8. If the error stays, the range in the formula is wrong or you overwrote B8. -
Now insert an empty row between two grades: right-click the row number 4 → Insert 1 row. Maths is at the top, English moved down, a hole in the middle.
-
Click B8. Does the range still say
B2:B6? After inserting a row it may have shifted, or now it reaches into the hole and misses the last grade. Fix it to the real grades. Then delete the empty row whole (not just the cells).
See the difference? Dirt does not have to shout. Sometimes it just quietly changes the average.
A space you cannot see
On Poznamky in A1 write 2 and after the two put a space. It looks like a two.
In B1 write =A1+1. Often you get #HODNOTA!. The sheet is adding text and a number.
In C1 write =SUMA(A1) or =SUM(A1). SUM often treats text as zero. Result 0 next to a two is worse than an error — it does not look like a problem.
Fix: click A1, delete, write 2 again, Enter. No space. B1 shows 3.
The same trick works with 2. with a full stop, 2, with an extra comma, or two in words. A grade column = numbers only.
When you are not sure if a cell is a number, write next to it =A1*1. If the same number comes out, it is a number. If #HODNOTA!, it is text. Then rewrite it.
Write the same word the same way
A survey can break a chart without a single formula error.
| Answer | Count from the filter |
|---|---|
| apple | 5 |
| Apple | 2 |
| apple | 1 |
| appley | 3 |
Those are four columns in the chart. In the class there is one flavour. Unify the spelling before you draw. Pick one shape: apple. Filter, find the others, rewrite.
A space at the end (apple ) is a fifth flavour. You cannot see it. The filter shows it as another checkbox. Rewrite it.
Capital and small letters: Sheets often treats them as different values in a filter. Write everything small, or everything with a capital at the start. Do not mix.
A wrong range looks like a clean table
The table can be tidy and the formula can still lie.
=SUMA(B2:B8) when grades end in B6 and B7 is empty and B8 is the average: the sum takes the average too. The number is higher. Nobody shouts. Do not make a chart from that — but then the label Total does not match.
=PRŮMĚR(B2:B10) when B7:B10 has nothing: empty cells the average in Sheets often skips. The result fits by luck. You add a note here and the average drops.
Write the range according to the last data row. After inserting or deleting a row, click the formula and check B2:B?. That is cleaning too.
Mini clean-up on three sheets
-
Znamky: in column B only numbers. Non/a, no hole between subjects. The average reachesB2:B6(or your last row), notB:B. -
Kapesne: in columns B–D numbers. Rewrite50 Kčby hand to50and currency format. Do not take the total into the range of days. -
Survey: one question, one spelling of the answers. Delete empty rows in the middle. Only then counts and a chart.
📊 Survey — clean data
The same answer twice differently (Apple / apple / apple ) pretends in the chart to be three things. Before you insert a column chart, turn on the filter on the answer column. How many different checkboxes do you see? That many bars will be in the chart. Unify them to one shape, turn the filter off (check all), only then draw.
Now you 💪
First dirt as a test, then a real clean-up.
First: on Znamky in one grade write n/a. Look what it does to the average. Put the number back. Insert an empty row, check the range, delete it.
Second: go through your three sheets. No n/a in a column you are adding. No hole in the middle. No apple with a space. Only then SUM and a chart.
When SUM throws #HODNOTA!, look for text. When the total is “somehow weird”, check whether the range also takes the label Total or a second total.
Check: The Sorter can filter, KDYŽ and clean up. In a number column there are numbers. In a word column there is one spelling. You cannot make a nice chart from dirty data.
What to take from this lesson
- Clean up before the formula, not after it.
n/aand a space quietly break the average. - Text does not belong in a number column. Write the range
B2:B6, not the wholeB:B. - Write the same word the same way — for the filter and for the chart.
© 2026 Ing. Martin Polak / AlgoRhino · Content usage terms