Mada za sehemu hiiManaging spreadsheet and data presentationMada 4
- Managing components, worksheets and formatting
- Implementing conditions and data
- Implementing functions and formulas
- Implementing data visualisation with charts and tables

A formula in Excel is a calculation you create to work with data in your spreadsheet. It always begins with an equal sign (=) and can use numbers, cell references, and mathematical operators to perform calculations. A function is a pre-built formula that performs a specific task, like adding numbers or finding an average. Together, formulas and functions help you analyze data quickly and accurately.
A simple formula performs basic mathematical operations using numbers and operators.
Operators used in Excel:
- Addition: + (plus sign)
- Subtraction: - (minus sign)
- Multiplication: * (asterisk)
- Division: / (forward slash)
- Exponentiation: ^ (caret)
How to create a simple formula:
- Click the cell where you want the result to appear.
- Type the equal sign (=).
- Enter the calculation using numbers or cell references.
- Press Enter.
Examples:
| Formula | Description | Result |
|---|---|---|
| =A2+A3 | Adds values in cells A2 and A3 | Sum of both cells |
| =A2-A3 | Subtracts A3 from A2 | Difference |
| =A2*A3 | Multiplies A2 by A3 | Product |
| =A2/A3 | Divides A2 by A3 | Quotient |
| =A2^A3 | Raises A2 to the power of A3 | Exponential |
Note: Excel follows the order of operations — multiplication and division are calculated before addition and subtraction.
AutoSum is a quick way to add numbers in a column or row without typing a formula.
Steps to use AutoSum:
- Click the cell where you want the total (below a column or right of a row).
- Go to the Home tab.
- Click AutoSum (Σ symbol).
- Press Enter.
Excel automatically selects the range of numbers and creates a =SUM formula. For example, =SUM(B2:B6) adds all values from B2 to B6.
You can also type =SUM(B2:B6) manually to get the same result.
Instead of typing the same formula repeatedly, you can copy it to other cells. Excel automatically adjusts the cell references.
Example:
If you have =SUM(B2:B6) in cell B7 and copy it to C7, the formula becomes =SUM(C2:C6).
The SUMIF function adds numbers that meet a specific condition.
Syntax:
SUMIF(range,criteria,[sum_range])
- Range: The cells to check against the condition.
- Criteria: The condition that must be met.
- Sum_range: (Optional) The cells to sum. If omitted, the Range is summed.
Example:
To add only values greater than 5 in cells B2:B25:
=SUMIF(B2:B25,">5")
The AVERAGE function calculates the arithmetic mean of numbers.
Syntax:
AVERAGE(number1,[number2],...)
Example:
To find the average of values in cells B2 through B6:
=AVERAGE(B2:B6)
This is equivalent to =SUM(B2:B6)/5 but much simpler.
The AVERAGEIF function calculates the average of cells that meet one condition.
Syntax:
AVERAGEIF(range,criteria,[average_range])
Example:
If you have subject names in B3:B15 and scores in C3:C15, to find the average math score:
=AVERAGEIF(B3:B15,"math",C3:C15)
You can also reference a cell containing the criteria:
=AVERAGEIF(B3:B15,F3,C3:C15)
The AVERAGEIFS function calculates the average of cells that meet multiple conditions.
Syntax:
AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)
Example:
To find the average score for "math" subject where the score is greater than 0:
=AVERAGEIFS(C3:C15,B3:B15,"math",C3:C15,">0")
This function allows you to apply two or more conditions at the same time.
Scenario: A shopkeeper in Arusha has a spreadsheet recording daily sales for different products over one week. The data is organized as follows:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Mon | Tue | Wed | Thu | Fri | Total |
| 2 | Rice (kg) | 50000 | 45000 | 60000 | 55000 | 52000 | |
| 3 | Beans (kg) | 30000 | 35000 | 32000 | 28000 | 40000 | |
| 4 | Sugar (kg) | 25000 | 20000 | 30000 | 28000 | 22000 |
Tasks:
- Calculate the total sales for each product in column G.
- Calculate the average daily sales for rice in cell B6.
- Calculate the average sales for rice only using AVERAGEIF.
Solutions:
-
Total sales for rice (cell G2):
- Using AutoSum: Click G2 → Home → AutoSum → Press Enter
- Or type: =SUM(B2:F2)
- Result: 50000+45000+60000+55000+52000 = 262,000 TZS
-
Average daily sales for rice (cell B6):
- Type: =AVERAGE(B2:F2)
- Result: 52,400 TZS
-
Average sales using AVERAGEIF:
- Assuming product names are in A2:A4 and totals are in G2:G4
- Type: =AVERAGEIF(A2:A4, "Rice (kg)", G2:G4)
- Result: 262,000 TZS
A small shop owner in Dar es Salaam can use formulas to track daily sales of items like rice, cooking oil, and soap. By using the SUM function, they can calculate total earnings each day, and AVERAGE to see the typical daily sales. This helps them understand which items sell best and plan their weekly orders from the wholesaler, ensuring they never run out of popular products while avoiding excess stock that may expire.
Jinsi utakavyotathminiwaTathmini ya mchakato · Tathmini ya matokeo · Tathmini ya maarifa
Tathmini ya mchakato
The student should be able to:
- Use basic andadvanced spreadsheet functions correctly (e.g., SUM, AVERAGE, VLOOKUP, IF).
- Create complex formulas combining multiple functions.
- Apply cell referencing methods (absolute, relative, mixed).
Tathmini ya matokeo
Appropriate functions and formulas applied to automate calculations for producing accurate and reliable results.
Tathmini ya maarifa
Methods used: The trainee should explain how to:
- Use various categories of functions (e.g., text, logical, lookup) for different tasks.
- Combine multiple functions within a single formula to solve complex problems.
Principles: The student should explain the principle of:
- Formula design for scalability and reusability.
- Logical operations and their application in decision-making functions.
Theories: The trainee should explain: The trainee should explain:
- Data analysis and how functions enhance insights.
Detailed knowledge about:
- Common errors in formulas and how to resolve them.
- Advanced functions like INDEX, MATCH, and ARRAYFORM ULA.
Swali
What symbol must every formula in Microsoft Excel begin with?
Ingia ili kuwasilisha jibu lako na lihesabiwe katika umahiri wako.
Ingia ili kufanya mazoeziMwalimu
Umekwama? Niulize chochote kuhusu mada hii.
Ingia ili kumuuliza Mwalimu wa AI wa Sonza kuhusu swali hili.
Ingia ili kuulizaMajadiliano
Hakuna maswali bado