Learning Objectives
After completing this lesson, students will be able to:
- Understand formulas and functions.
- Differentiate between formulas and functions.
- Perform arithmetic calculations.
- Use common spreadsheet functions.
- Create a marksheet using formulas.
Prerequisite Knowledge
- Opening LibreOffice Calc.
- Creating a worksheet.
- Rows, columns and cell references.
- Entering data into cells.
Formula
A Formula is an expression used to perform calculations using numbers, operators and cell references.
Every formula begins with an = (equals) sign.
Examples
| Formula | Purpose |
|---|---|
=10+5 |
Add two numbers |
=25-8 |
Subtract numbers |
=15*6 |
Multiply numbers |
=50/5 |
Divide numbers |
=B2+C2+D2 |
Add values stored in cells |
Arithmetic Operators
| Operator | Meaning | Example |
|---|---|---|
| + | Addition | =5+4 |
| - | Subtraction | =8-3 |
| * | Multiplication | =6*5 |
| / | Division | =24/6 |
| ^ | Power | =5^2 |
Function
A Function is a predefined formula that performs a specific calculation automatically.
Functions make calculations faster, easier and more accurate.
Common Spreadsheet Functions
| Function | Purpose | Example |
|---|---|---|
| SUM() | Adds numbers | =SUM(B2:E2) |
| AVERAGE() | Calculates average | =AVERAGE(B2:E2) |
| MAX() | Finds highest value | =MAX(B2:E2) |
| MIN() | Finds lowest value | =MIN(B2:E2) |
| COUNT() | Counts numeric values | =COUNT(B2:E2) |
Student Marksheet
| Name | English | Maths | Science | Computer | Total |
|---|---|---|---|---|---|
| Aarav | 85 | 92 | 88 | 90 | ? |
| Diya | 79 | 84 | 91 | 86 | ? |
| Rohan | 95 | 89 | 94 | 96 | ? |
Applying Formulas
| Task | Formula |
|---|---|
| Total Marks | =SUM(B2:E2) |
| Average Marks | =AVERAGE(B2:E2) |
| Highest Marks | =MAX(B2:E2) |
| Lowest Marks | =MIN(B2:E2) |
| Number of Subjects | =COUNT(B2:E2) |
Using AutoFill
Instead of typing the same formula repeatedly, drag the Fill Handle (small square at the bottom-right corner of the selected cell) to automatically copy the formula to other rows.
AutoFill saves time and reduces typing errors.
Classroom Activity
- Create the student marksheet.
- Enter marks of five students.
- Calculate Total using SUM().
- Calculate Average using AVERAGE().
- Find Highest Marks using MAX().
- Find Lowest Marks using MIN().
- Use AutoFill to copy formulas.
Practical Activity
Create the following columns:
| Name | English | Maths | Science | Computer | Total | Average | Highest | Lowest |
|---|
Use appropriate functions to complete all calculated columns.
Points to Remember
- Every formula starts with =.
- Functions are predefined formulas.
- Cell references are used inside formulas.
- Use AutoFill to copy formulas quickly.
- Functions improve accuracy and save time.
Quick Check
- What is a formula?
- What is a function?
- Which function calculates the total?
- Which function finds the highest value?
- Which function counts numerical values?
- Why should formulas begin with "="?
Homework
- Create a marksheet of 10 students.
- Enter marks in five subjects.
- Calculate:
- Total
- Average
- Highest Marks
- Lowest Marks
- Number of Subjects
- Save the file as Student_Marksheet.ods.
Assessment
| Assessment Method | Description |
|---|---|
| Oral Questioning | Formula, Function and AutoFill concepts. |
| Worksheet | Complete the marksheet using formulas. |
| Practical Observation | Teacher checks correct use of functions and AutoFill. |
Summary
In this lesson, you learned the difference between formulas and functions, used arithmetic operators, applied the SUM(), AVERAGE(), MAX(), MIN() and COUNT() functions, and used AutoFill to efficiently calculate values in a student marksheet.