Formula kid — finale · Martinとコード

Formula kid = the spreadsheet thinks for you

Twelve lessons: equals, SUM, average, copying a formula, MIN, MAX, COUNT, dollars, references between sheets, errors, pocket money, colours by rules. The finale = everything together in workbook Tables with Martin.

Then pick up the Formula kid certificate ➗ on 修了証.

💡

A certificate makes sense when you can really change a grade and the average recalculates itself. Show a parent. You are not a “lesson clicker” — you are someone who can do a formula.

Big project: three sheets + a dashboard

Grades: B3+ grades. Under them Total =SUM(...), Average =AVERAGE(...), Best =MIN(...), Worst =MAX(...). The range fits. Conditional formatting for ≥3.
Pocket: D2 =B2-C2 dragged to D8. B10–B12 totals and the week difference. Negative D in red.
Survey: column B votes. Total votes =SUM(...), Number of answers =COUNT(...).
Dashboard: B3 =AVERAGE(Grades!...), B4 =SUM(Pocket!B2:B8) or the week difference, B5 =SUM(Survey!...). No number copied by hand that another sheet already counts.
Change one grade, one spend, one vote. The Dashboard and summaries must move.

⚠️

Go through the whole workbook for errors. No #DIV/0!, #REF!, #VALUE! in the version you hand in. If there is, fix data or a range — do not delete the formula.

A live test — a teacher simulation

Add a subject and a grade on Grades. Average and MIN/MAX OK?
Delete the content of one cell in a formula — an error? Fix it.
Open the Dashboard. Do three numbers match the sheets?
Sharing: the teacher Viewer. You still Editor.

🏫 Grade average

One sheet, two columns: subject and grade. At the bottom a sum and an average.

💰 Pocket money

Rows = days, columns = income / spend / left.

📊 Class survey

One question, answers in one column, later a chart.

🏆 Mini dashboard

Three numbers at the top and one chart. Numbers as a link, not copied by hand.

What next — Chart maker

A chart from the survey, number format, print. Formula kid gave logic. Chart maker gives a picture. The mini dashboard will get a chart — three numbers you already have as links.

Now you 💪

Final Formula kid checklist — all YES:

Grades: an average in a formula, not a pencil. MIN/MAX under the data.
Pocket: the whole of column D with a formula. Totals under the week.
Survey: SUM of votes.
Dashboard: three live links via Sheet!.
A change of data = a recalculation everywhere.
Conditional formatting on grades or the pocket.
No error cell in the finished workbook.
Cell kid look still fits (headings, borders).

✅

Formula kid 完了. You can do equals, functions, copying, references between sheets and fixing errors. 修了証 ➗ Formula kid on 修了証. Next level: Chart maker — from numbers we will make a chart.

What to take from this finale

The whole workbook counts itself — grades, pocket, survey, dashboard.
One data change goes through all formulas.
修了証 excel-vzoreckar after 12 lessons.
You fix errors at the data source.
Chart maker will pick up with a chart on the survey and dashboard.