TECH CLUBBOZOMA INNOVATION HUB

Session 20 • Page 1

Learner Full Name:

School:

Class:

Workstation Device:

Station #:

Session 20: Functions, Ranges & Visual Charts

In Session 19, you multiplied single cells together using =B2*C2. But what if your school store sells 100 different items? Typing =D2+D3+D4+D5...+D101 would take 20 minutes! Today, you will learn how to command the spreadsheet to calculate entire columns in a single breath using Functions and Ranges.

1. What is a "Range"? (The Power of the Colon :)

A Range is a block of connected cells. In spreadsheet language, we write a range using the first cell address, a colon (:), and the last cell address.

The colon simply means "THROUGH TO":

Figure 20.0: Anatomy of the SUM function. The colon (:) tells the computer to add every cell from D2 to D5.

2. Formula vs. Function: What is the Difference?

FORMULA (Manual Math)

A math equation you write by hand using basic symbols: =B2*C2 or =A1+A2+A3.

Best for simple calculations between 2 or 3 cells.

FUNCTION (Preset Magic Word)

A built-in preset shortcut word provided by the spreadsheet to crunch massive lists of numbers: =SUM(D2:D5).

Best for adding or averaging 5, 50, or 5,000 cells at once!

The "Core 4" Statistical Functions

Session 20 • Page 2

1. Meet the Big Four Built-in Functions

In school, business, and science, these four functions do most everyday data work:

Function Name	What It Does	How to Write It in a Cell
=SUM(...)	Adds every single number in the range together to calculate a Grand Total.	`=SUM(D2:D5)`
=AVERAGE(...)	Adds the numbers and divides by the count to find the mean (typical) score.	`=AVERAGE(C2:C5)`
=MAX(...)	Scans the entire list and displays the highest (maximum) value.	`=MAX(B2:B5)`
=MIN(...)	Scans the entire list and displays the lowest (minimum) value.	`=MIN(C2:C5)`

Figure 20.1: The Core 4 functions operating on the exact same range of exam scores.

2. Check Your Thinking

Look at the numbers: 10, 20, 30, 40 located in cells A1:A4.

A. What is the result of =SUM(A1:A4)?

B. What is the result of =MAX(A1:A4)?

C. What is the result of =AVERAGE(A1:A4)?

Turning Numbers into Pictures (Charts)

Session 20 • Page 3

1. Why Do We Use Charts?

Human beings are visual thinkers. When a chief, headteacher, or market board inspects a spreadsheet with 500 rows of raw numbers, their eyes get tired. But when you convert that data into a Chart, patterns, trends, and comparisons leap out in one second!

2. The Three Classic School Chart Types

📊 COLUMN / BAR CHART

Uses vertical or horizontal bars to compare separate categories side by side.

Best for: Comparing sales of different items, attendance across classrooms, or harvest yields per village.

🥧 PIE CHART (Slices of a Cake)

A circle sliced into wedges showing parts of a 100% whole.

Best for: School budget spending (food, books, sports) or waste composition (plastics vs. organic food scraps).

📈 LINE CHART

Joins points with a line to show change over time. Best for: Rainfall or school attendance month by month.

Figure 20.2: Anatomy of a Column Chart. Data Labels on top of each bar display exact quantities.

The Golden Rule of Chart Selection

Session 20 • Page 4

1. What Cells Do I Highlight Before Making a Chart?

A chart is only as smart as the cells you feed into it. The computer does not guess: you must highlight the exact labels and numbers you want to visualize.

🚨 THE #1 BEGINNER CHART MISTAKE:

NEVER INCLUDE THE "GRAND TOTAL" ROW IN YOUR CHART!

If you include the Total row, the Total bar will be huge (e.g. 50 items) and will dwarf all your individual products, making them look like tiny flat slivers!

Figure 20.3: Data selection rule for charts. Always select the headers and items, but stop before the Total row!

2. Troubleshooting Chart Issues

Problem Observed	Why It Happened	Immediate Fix
My chart is completely blank!	You highlighted empty white cells before clicking Insert Chart.	Delete chart (Del), highlight your actual data table, and re-insert.
The bars have no labels under them.	You only selected Column B (numbers) and forgot to select Column A (item names).	Select both columns: from A1 across to B5!

Using Google Sheets or LibreOffice Calc? The buttons look a little different but do the same job.

Guided Hands-On Lab: Totals & Charts

Session 20 • Page 5

1. Lab Mission: Add Grand Totals, Stats & a Visual Chart

Follow these 6 steps inside your existing spreadsheet file from Session 19:

1

Open Your File: Launch Excel, Calc, or Sheets → Open Stationery_Store_Ama.xlsx from your BIH_Practice folder.

2

Calculate Grand Total using =SUM:
• Click inside Cell A6, type: GRAND TOTAL.
• Click inside Cell D6, type: =SUM(D2:D5) and tap Enter.
Observation: D6 displays 162.50!

3

Calculate Statistics:
• In Cell A7, type: Average Price → in C7, type: =AVERAGE(C2:C5)
• In Cell A8, type: Highest Price → in C8, type: =MAX(C2:C5)
• In Cell A9, type: Lowest Price → in C9, type: =MIN(C2:C5)
• Select C2:D9 → Home → Number format → 2 decimal places (so 3.875 shows as 3.88).

4

Select Data for the Chart:
Click in Cell A1, hold down the left mouse button, and drag across and down to Cell B5.
Check: Cells A1 through B5 are highlighted blue/green. (Notice you left Row 6 OUT!).

5

Insert the Chart:
Click the Insert tab on the Ribbon → click Column / Bar Chart → select 2D Clustered Column.
A bar chart appears on your sheet!

6

Add Title & Test Dynamic Magic:
Double-click the default chart title and rename it: Stationery Stock Quantities.
Now change Cell B2 (Books) from 10 to 35.
Watch: The Grand Total updates AND the blue chart bar grows taller instantly! Save: Ctrl + S.

2. Visual Target (What Your Finished Sheet Should Look Like)

1	Item Description	Quantity	Price (GHS)	Line Total
2	Exercise Books	10	8.00	80.00
3	Ballpoint Pens	20	2.00	40.00
4	Wooden Rulers	5	4.00	20.00
5	Pencil Erasers	15	1.50	22.50
6	GRAND TOTAL			162.50
7	Average Price		3.88
8	Highest Price		8.00
9	Lowest Price		1.50

Session 20 Review & Verification

Session 20 • Page 6

1. Match the Function to its Job

Draw a line connecting each spreadsheet function to its correct mathematical job:

=SUM(B2:B10)
=AVERAGE(B2:B10)
=MAX(B2:B10)
=MIN(B2:B10)
D2:D20

• Finds the smallest / lowest value in the range.

• Adds up all numbers in the range to give a grand total.

• Represents a range of cells from D2 through to D20.

• Finds the largest / highest value in the range.

• Calculates the typical mean score of the range.

2. Written Review Questions

Question 1: Why should you NEVER include the "Grand Total" row when highlighting data to build a Column chart?

Question 2: What does the colon symbol (:) in the formula =SUM(A1:A50) stand for?

Question 3: Which type of chart is shaped like a circular cake and is best for showing parts of a 100% whole budget?

3. Self-Assessment & Mentor Sign-Off

Tick the column that reflects your confidence at the end of this session:

Spreadsheet Functions & Charting Competency	With Help	Almost Alone	All By Myself!
1. I can write a range of cells using colon notation (e.g. D2:D5).
2. I can calculate grand totals using =SUM and averages using =AVERAGE.
3. I can find the highest and lowest numbers using =MAX and =MIN.
4. I can select items and quantities (leaving out Total) to create a Column Chart.

Mentor Signature: _____________________________

Date Completed: ____________________