DATA
MANIPULATI
ON
FUNCTIONS. FORMULAS. LOGIC. CALCULATIONS. DATA
VALIDATION
Eng. Rindah Luanga Ngwanyam
Learning
objectives
• Understand the difference between
formulas and functions
• Use arithmetic and logical
operations in Excel
• Apply common Excel functions (SUM,
AVERAGE, IF, etc.)
• Perform calculations using cell
references
• Build logical formulas with AND, OR,
NOT
What is a
formula?
• A formula is an expression that
calculates the value of a cell.
• Always starts with =
• Example: =A1 + B1
What is a
function?
• A function is a predefined
formula in Excel.
• Makes calculations easier
and faster.
• Example: =SUM(A1:A10)
• Structure:
=FUNCTION_NAME(argume
nts)
Basic
Operators
Addition
=A1 + B1
Subtraction
=A1 - B1
Multiplication
=A1 * B1
Division
=A1 / B1
Exponent
=A1 ^ 2
Exercise 1. Basic Formulas
• Enter the following
values:
⚬ A1 = 25, B1 = 10
• Write formulas to:
⚬ Add A1 and B1
⚬ Subtract B1 from A1
⚬ Multiply A1 and B1
⚬ Divide A1 by B1
⚬ Square the value in
A1
Cell
Referencing
Relative & Absolute
• Relative: =A1 + B1
• Absolute: =$A$1 + $B$1
• Mixed: =A$1 + $B1
• Useful when copying
formulas across rows or
columns
Exercise 2
Relative vs Absolute References
• In a table, enter:
⚬ A2:A6 = quantities
⚬ B1 = price per unit (e.g., 500)
• Write a formula in B2 to calculate total cost: Quantity
* Price
• Copy it down and observe result
• Now fix the price reference using absolute referencing
Exercise 3
• Given values in C1:C5 = [100, 200, 300,
400, 500]
• Find:
⚬ Sum of values
⚬ Average of values
⚬ Minimum value
⚬ Maximum value
⚬ Count of values
Exercise 4
• Enter the text Excel Functions in cell A1
• Apply the following:
• Extract the first 5 characters
• Count total characters
• Convert the text to uppercase
• Join the text with "Are Powerful"
Introduction to
logical
Functions
• Logical functions return
TRUE or FALSE
• Most common: IF(),
AND(), OR(), NOT()
The IF Function
• Syntax:
=IF(condition,
value_if_true,
value_if_false)
• Example:
=IF(A1>50, "Pass",
"Fail")
Exercise 4
• Enter student scores in cells A2:A6
• Create a formula to show:
• If score ≥ 50 → "Pass"
• Otherwise → "Fail"
Exercise 5
Create a table with:
• Column A: Age
• Column B: Score
Use:
• AND() to return "Eligible" if Age ≥ 18 AND
Score ≥ 60
• OR() to return "Consider" if Age ≥ 18 OR
Score ≥ 60
• NOT() to return "Not Eligible" if NOT(Score
≥ 50)
Nesting
Functions
• Combine multiple
functions in one
formula
Example:
• =IF(AND(A1>60,
B1>60), "Qualified",
"Not Qualified")
Exercise 5
Create a table with:
• Column A: Age
• Column B: Score
Use:
• AND() to return "Eligible" if Age ≥ 18 AND
Score ≥ 60
• OR() to return "Consider" if Age ≥ 18 OR
Score ≥ 60
• NOT() to return "Not Eligible" if NOT(Score
≥ 50)
Exercise 6
Exercise 7: Calculate Age
• Enter birthdate in A2
• Use TODAY() to get today’s dat
• Calculate age in years using:
⚬ =DATEDIF(A2, TODAY(), "Y")
✅ Try with different dates
Practical
Example
• Scenario: Assign grades based on scores.
• Formula:
• =IF(A1>=90, "A", IF(A1>=80, "B",
IF(A1>=70, "C", "Fail")))
Exercise 8
• Create a formula that divides A1 by
B1
• Try:
⚬ A1 = 100, B1 = 0 → observe
error
• Wrap it with IFERROR() to return
"Check value"
Case Study. Salary
Calculation
• Use: SUM, IF, and cell references to
compute net salary
• Columns: Basic Pay, Allowance,
Deductions, Tax
• Final formula: =Basic + Allowance -
Deductions - Tax
Case Study – Salary Calculation
Use: SUM, IF, and cell references to
compute net salary
Columns: Basic Pay, Allowance,
Deductions, Tax
Final formula: =Basic + Allowance -
Deductions - Tax
Input scores in
A2:A6
Create a grading
formula:
A ≥ 90 → A
80–89 → B
=IF(A2>=90,"A",IF(A2>=8
0,"B",IF(A2>=70,"C","Fail"
)))
Full Student Table
Create a table:
Columns: Name, Math, English, Average,
Status
Compute:
Average of scores
Status: "Pass" if average ≥ 50 else
"Fail"
Highest and Lowest subject scores
