Chapter 4: Basic Formulas and Functions
Basic Formulas and Functions.
This is where Excel becomes truly powerful — it starts doing calculations automatically for you! I’ll explain everything very slowly, step by step, like a patient teacher sitting right next to you at your computer. We’ll use lots of real-life examples, clear step-by-step instructions, and plenty of practice tips. By the end of this chapter, you’ll be confidently writing formulas, using common functions, and understanding how Excel thinks.
Let’s begin!
1. Understanding Formulas and Operators (+, -, *, /)
A formula is an instruction that tells Excel to calculate something.
- Every formula starts with an equal sign (=)
- After =, you write what you want Excel to do.
Basic arithmetic operators:
| Operator | Meaning | Example in Excel | Result |
|---|---|---|---|
| + | Addition | =10+5 | 15 |
| – | Subtraction | =100-25 | 75 |
| ***** | Multiplication | =8*7 | 56 |
| / | Division | =100/4 | 25 |
| ^ | Exponent (power) | =2^3 | 8 |
| % | Percentage | =50% | 0.5 |
Real-life example – Simple calculation:
- Click cell A1 → type 100 (price)
- Click cell A2 → type 18 (GST %)
- Click cell A3 → type =A1*A2/100 → Press Enter → Excel shows 18 (GST amount)
- Click cell A4 → type =A1+A3 → Press Enter → Excel shows 118 (total including GST)
Important rule: Always start with = If you forget the =, Excel thinks you’re just typing text!
2. Common Functions: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA
Functions are pre-built formulas that do common tasks quickly.
How to write a function: =FUNCTION_NAME(arguments)
Most useful basic functions:
| Function | What it does | Example | Result (based on data below) |
|---|---|---|---|
| SUM | Adds up a range of numbers | =SUM(B2:B10) | Total of all values |
| AVERAGE | Finds the average | =AVERAGE(B2:B10) | Average value |
| MIN | Finds the smallest number | =MIN(B2:B10) | Smallest value |
| MAX | Finds the largest number | =MAX(B2:B10) | Largest value |
| COUNT | Counts how many numbers are in range | =COUNT(B2:B10) | Number of numeric cells |
| COUNTA | Counts how many non-empty cells | =COUNTA(A2:A10) | Number of filled cells |
Real-life sales data example (type this in your sheet):
| A | B | |
|---|---|---|
| 1 | Item | Sales |
| 2 | Pen | 250 |
| 3 | Notebook | 1200 |
| 4 | Eraser | 80 |
| 5 | Pencil | 450 |
| 6 | Marker | 320 |
| 7 | Total | |
| 8 | Average | |
| 9 | Highest | |
| 10 | Lowest |
Now let’s add formulas:
- In B7 → type =SUM(B2:B6) → Enter → shows 2300 (total sales)
- In B8 → type =AVERAGE(B2:B6) → Enter → shows 460
- In B9 → type =MAX(B2:B6) → Enter → shows 1200
- In B10 → type =MIN(B2:B6) → Enter → shows 80
3. AutoSum – The Fastest Way to Add SUM, AVERAGE, etc.
AutoSum is a button that writes common functions for you automatically!
Where: Home tab → Editing group → AutoSum button (looks like Σ)
How to use:
- Click the cell where you want the result (e.g., B7)
- Click AutoSum button
- Excel guesses the range (B2:B6) → a moving dashed line appears
- If correct, just press Enter → Excel writes =SUM(B2:B6) for you!
AutoSum also has a dropdown:
- Click the small arrow next to Σ → you can choose Average, Count Numbers, Max, Min, More Functions…
Pro tip: Select your data first (B2:B6) → then click AutoSum → it automatically puts the total below!
4. Basic Error Handling (Common Mistakes & Fixes)
Excel shows special codes when something goes wrong:
| Error | What it means | How to fix |
|---|---|---|
| #DIV/0! | You tried to divide by zero | Check denominator isn’t zero or empty |
| #VALUE! | Wrong data type (e.g., text in a number calculation) | Make sure all cells in formula are numbers |
| #NAME? | Excel doesn’t recognize the function name | Check spelling (SUM not sum, AVERAGE not avg) |
| #REF! | Cell reference is invalid (you deleted a cell) | Undo or fix the range |
| ###### | Column is too narrow to show the number | Double-click column border to auto-fit |
Quick fix tip: Click the yellow warning icon next to the cell → Excel often suggests how to fix it!
5. Order of Operations (PEMDAS / BODMAS)
Excel follows the same math rules you learned in school:
PEMDAS (USA) or BODMAS (India/UK) – same thing!
P – Parentheses (brackets) first E / B – Exponents / Brackets (powers) D / O – Division and Multiplication (left to right) A / S – Addition and Subtraction (left to right)
Real example: What is =10+20*3 ?
- Wrong thinking: 10+20=30 → 30*3=90
- Correct: Multiplication first → 20*3=60 → then 10+60=70
To control order – use parentheses:
- =**(10+20)3* → 30*3 = 90
Another example:
- =100-50/2+102 → Division & Multiplication first: 50/2=25, 102=20 → Then: 100 – 25 + 20 = 95
Tip: When in doubt, always use ( ) to make it clear!
Quick Summary Table – Chapter 4
| Feature | How to Use / Shortcut | Example |
|---|---|---|
| Start a formula | Always begin with = | =A1+B1 |
| Basic operators | + – * / ^ % | =A1*1.18 (price + 18% GST) |
| SUM | =SUM(range) or AutoSum | =SUM(B2:B10) |
| AVERAGE, MIN, MAX | =AVERAGE(range), etc. | =AVERAGE(B2:B10) |
| COUNT / COUNTA | Count numbers / count non-empty | =COUNTA(A2:A100) |
| AutoSum | Click Σ button | Fastest way for totals |
| Order of operations | PEMDAS/BODMAS – use ( ) when needed | =(A1+B1)*C1 |
| Common errors | #DIV/0!, #VALUE!, #NAME? | Check spelling, data types, ranges |
Homework / Practice (Do This Today!)
Use your existing practice file (or create a new sheet called “Chapter4”):
- Create the sales table I showed above (Items in A2:A6, Sales in B2:B6)
- In B7 → use AutoSum to get total sales
- In B8 → type =AVERAGE(B2:B6)
- In B9 → =MAX(B2:B6)
- In B10 → =MIN(B2:B6)
- In B11 → =COUNT(B2:B6) (should show 5)
- In B12 → =COUNTA(A2:A6) (should show 5)
- Add a new row (insert row 7):
- A7 → Highlighter
- B7 → 650
- Watch how the SUM in B8 automatically updates! (this is called dynamic range)
- In C2 → type =B2*1.18 (price including 18% GST) → drag down
- Try a mistake: In D2 type =B2/0 → see #DIV/0! error → fix it by changing to /2
