Spreadsheet Applications II - SSS 1 Note

SPREADSHEET APPLICATIONS II

Subject: Digital Technologies

Class: SSS 1

Term: Third Term

Week: 3

Topic: Spreadsheet Applications II

Duration: 80 Minutes (Double Period)

Behavioural Objectives

By the end of the lesson, students should be able to:

1. Apply common spreadsheet functions.
2. Create simple charts from spreadsheet data.
3. Analyse data using spreadsheet tools.
4. Organise and interpret simple datasets.

1. Meaning of Spreadsheet Functions

Definition: A spreadsheet function is a predefined formula that performs a particular calculation using supplied values or cell references.

Functions help users calculate totals, averages, highest values and lowest values without having to write long formulas manually. For example, a teacher who wants to calculate the average score of a class can use the AVERAGE function.

Common Spreadsheet Functions

Function	Purpose	Example
SUM	Adds values in selected cells.	=SUM(B2:B6)
AVERAGE	Calculates the arithmetic mean of values.	=AVERAGE(B2:B6)
MAX	Returns the highest value.	=MAX(B2:B6)
MIN	Returns the lowest value.	=MIN(B2:B6)
COUNT	Counts cells containing numbers.	=COUNT(B2:B6)

Understanding Cell Ranges

A cell range is a group of selected cells. In the reference B2:B6, the colon indicates the range from cell B2 through B6. For example, if cells B2 to B6 contain 10, 15, 20, 25 and 30:

• =SUM(B2:B6) returns 100.
• =AVERAGE(B2:B6) returns 20.
• =MAX(B2:B6) returns 30.
• =MIN(B2:B6) returns 10.
• =COUNT(B2:B6) returns 5.

A Cell Range in a Spreadsheet

The range B2:B6 covers every cell from B2 down to B6. Functions work on the whole range at once.

Remember: In most spreadsheet applications, a formula or function begins with =. Function names and separators may vary slightly depending on the application and regional settings.

2. Creating Simple Charts

Definition: A chart is a graphical representation of data that makes information easier to compare, understand and interpret.

Charts turn values in a spreadsheet into visual displays. They can help users identify differences, patterns and trends.

Types of Charts

Chart Type	Description	Suitable Use
Column chart	Uses vertical bars to represent values.	Comparing scores across subjects.
Bar chart	Uses horizontal bars to represent values.	Comparing quantities among categories.
Pie chart	Uses slices of a circle to show proportions of a whole.	Showing how a budget is divided.
Line chart	Uses points connected by lines.	Showing changes over time.

Four Common Chart Types

Choose the chart that suits the data: column and bar to compare, pie for parts of a whole, line for change over time.

Steps for Creating a Simple Chart

1. Enter the data in a spreadsheet and arrange it in a clear table.
2. Select the data, including the headings.
3. Open the Insert menu or tab.
4. Choose the chart option.
5. Select a suitable chart type, such as a column, bar, pie or line chart.
6. Add or edit the chart title and labels where necessary.
7. Check that the chart represents the data correctly.

Good practice: Select a chart type that suits the information. A column chart is useful for comparing subject scores, while a line chart is often suitable for showing changes in scores over several tests.

3. Analysing Data with a Spreadsheet

Definition: Data analysis is the process of examining data to discover useful information, identify patterns, compare values and draw conclusions.

Spreadsheet tools make it easier to analyse data by performing calculations and presenting results in tables and charts.

Ways to Analyse Spreadsheet Data

• Calculate totals: Use SUM to find the total of several values.
• Calculate averages: Use AVERAGE to find the mean of a set of numbers.
• Identify extremes: Use MAX and MIN to find the highest and lowest values.
• Count numerical entries: Use COUNT to determine how many cells in a range contain numbers.
• Compare categories: Use tables or charts to compare values.
• Identify patterns: Examine values across categories or time periods to notice increases, decreases or repeated trends.

Example: Analysing Students' Scores

Suppose five students obtain the following scores in a class test:

Student	Score
Ada	60
Bola	75
Chika	80
David	55
Emeka	80

If the scores are stored in cells B2 to B6, the following results can be obtained:

Analysis	Formula	Result
Total score	=SUM(B2:B6)	350
Average score	=AVERAGE(B2:B6)	70
Highest score	=MAX(B2:B6)	80
Lowest score	=MIN(B2:B6)	55
Number of scores	=COUNT(B2:B6)	5

Interpreting the Results

• The total score of the five students is 350.
• The average score is 70.
• The highest score is 80, achieved by Chika and Emeka.
• The lowest score is 55, achieved by David.
• There are five numerical scores in the selected range.

Important distinction: Calculating results is part of data analysis, but interpretation means explaining what those results tell us. For example, the average score of 70 indicates the class's mean performance in the test.

4. Organising and Interpreting Datasets

Definition: A dataset is a collection of related data organised for storage, examination or analysis.

A dataset may contain students' scores, daily sales, household expenses, attendance records or weather observations.

How to Organise a Dataset

1. Give each column a clear heading.
2. Enter each record in a separate row.
3. Keep similar types of information in the same column.
4. Use consistent formats for dates, numbers and other values.
5. Check entries for missing values, spelling mistakes and incorrect data.
6. Arrange the data in a clear table before analysing it.

Example of an Organised Dataset

Student	ICT Score	Mathematics Score	English Score
Ada	75	68	80
Bola	60	72	65
Chika	85	90	78
David	70	55	72

Anatomy of an Organised Dataset

Each column holds one kind of information and each row holds one complete record.

This dataset is organised because each row contains one student's record and each column contains a particular category of information.

Interpreting a Dataset

To interpret a dataset, examine the values and explain what they show. Using the table above, students can identify:

• The student with the highest ICT score.
• The student with the lowest Mathematics score.
• Differences between students' scores in each subject.
• Subjects in which a student performs better or needs improvement.

Note: Interpretations should be based on the data available. Do not make conclusions that the recorded values cannot support.

5. Practical Classroom Activity

Students should use Microsoft Excel, Google Sheets or LibreOffice Calc to complete the following activities.

Activity A: Enter and Organise Data

1. Create a spreadsheet with the headings Student and Score.
2. Enter the following records: Ada — 60, Bola — 75, Chika — 80, David — 55 and Emeka — 80.
3. Ensure that each student appears in a separate row.

Activity B: Apply Spreadsheet Functions

1. Use SUM to calculate the total score.
2. Use AVERAGE to calculate the average score.
3. Use MAX to identify the highest score.
4. Use MIN to identify the lowest score.
5. Use COUNT to count the numerical scores.

Activity C: Create a Chart and Interpret the Data

1. Select the student names and their scores.
2. Insert a column chart or bar chart.
3. Give the chart the title Students' Test Scores.
4. Identify the students with the highest and lowest scores.
5. Write two statements describing what the data shows.

Expected results: Total = 350; Average = 70; Highest = 80; Lowest = 55; Count = 5. Students should also produce a chart that correctly represents the five scores.

Expected Chart for Activity C

A column chart of the five scores. The dashed line marks the average of 70, so it is easy to see who scored above or below it.

Summary

• Spreadsheet functions are predefined formulas used to perform calculations.
• SUM, AVERAGE, MAX, MIN and COUNT are common spreadsheet functions.
• Charts present data visually and make comparisons easier.
• Column, bar, pie and line charts serve different purposes.
• Data analysis involves examining values to find useful information and patterns.
• Organising a dataset makes it easier to understand and analyse.
• Interpreting data means explaining the conclusions supported by the results.

Evaluation Questions

1. What is a spreadsheet function? Name four common functions.
2. Write the formula for calculating the average of cells B2 to B6.
3. State three types of charts and one suitable use for each.
4. Explain two ways a spreadsheet can be used to analyse data.
5. What is a dataset? State three ways of organising data correctly.
6. Five students score 60, 75, 80, 55 and 80. Find the total, average, highest and lowest scores.
7. Explain the difference between analysing data and interpreting data.

Assignment

1. Explain the uses of SUM, AVERAGE, MAX, MIN and COUNT, giving one example formula for each.
2. Differentiate between column charts, bar charts, pie charts and line charts.
3. Create a sample dataset containing five students and their scores in two subjects. Explain how you would organise the data.
4. Describe how to create a chart from spreadsheet data and state two conclusions that could be drawn from the chart.

Interactive Quiz

Choose the correct answer for each question and submit your answers to see your score.

1. Which function adds values in a selected range?

A. MIN B. SUM C. COUNT D. MAX

2. Which function calculates the arithmetic mean of a set of numbers?

A. MAX B. SUM C. COUNT D. AVERAGE

3. Which function returns the highest value in a range?

A. MAX B. MIN C. SUM D. COUNT

4. Which chart is commonly suitable for showing changes over time?

A. Pie chart B. Picture chart only C. Line chart D. Flowchart

5. Which chart can be used to compare students' scores across subjects?

A. Column chart B. Flowchart C. Network diagram D. Circuit diagram

6. What does the formula =MIN(B2:B6) calculate?

A. The total B. The average C. The number of numeric cells D. The lowest value

7. What is a dataset?

A. A type of keyboard B. A collection of related data C. A spreadsheet formula only D. A computer monitor

8. Which practice helps keep a dataset organised?

A. Putting unrelated information in random cells B. Leaving all columns without headings C. Using clear headings and consistent data formats D. Mixing every record into one cell

9. What is data interpretation?

A. Explaining what the data and results show B. Deleting all numerical values C. Changing a spreadsheet's name only D. Entering random information

10. If scores are 10, 20 and 30, what is their average?

A. 10 B. 15 C. 30 D. 20