Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 3, Live 1
How Formulas Work, the Basic Functions, and IF, IFS, AND, OR
This week the data starts answering questions. First you learn exactly how Excel reads a formula and build the Revenue column the workbook is missing, then the six functions every report uses, then how to make Excel label each sale by a rule.
By the end of this class you can
- Explain the parts of a formula and the order Excel calculates them in
- Build a Revenue column, and see what a blank quantity does to it
- Use relative and absolute ($) references, and see what goes wrong without $
- Calculate business totals with SUM, AVERAGE, COUNT, COUNTA, MIN and MAX
- Label sales with IF and nested IF, then more cleanly with IFS
- Test two conditions at once with AND and OR
Start from your cleaned workbook from Week 2, or from TechPlug_Sales_Data_Cleaned.csv in the downloads: 1,050 sales in columns A to L, names clean, Quantity still blank on 25 sales. Notice something missing: there is a Quantity and a Unit_Price, but no column that says what each sale was worth. That is the first thing we build.
1. How Excel Reads a Formula
Definition
A formula is an instruction in a cell that tells Excel to calculate something. It always starts with an equals sign. The cell shows the answer; the formula bar shows the instruction. When any cell the formula uses changes, the answer updates by itself.
Real-Life Scenario
A calculator gives you an answer and forgets how it got there. A recipe card keeps the method: change "4 people" to "6 people" and every quantity is recalculated. A typed number is a calculator answer. A formula is a recipe card.
The Parts of a Formula
| Part | Example | What it is |
|---|---|---|
| Equals sign | = | Tells Excel this is a formula, not text |
| Cell reference | F2, M2:M1051 | Use whatever is in that cell or range |
| Operator | + - * / ^ | Add, subtract, multiply, divide, raise to a power |
| Function | SUM( ) | A built-in calculation with a name, taking inputs in brackets |
| Constant | 1000000, "High" | A fixed value typed into the formula. Text always goes in double quotes |
| Comparison | > < = >= <= <> | Asks a question that is TRUE or FALSE. <> means "not equal to" |
Order of Operations
Excel follows the same order you learnt in school maths (BODMAS): brackets first, then powers, then multiply and divide, then add and subtract. Predict these two before you look:
Without brackets, 3 x 4 happens first and 2 is added after: 14. With brackets, 2 + 3 happens first: 20. When in doubt, add brackets. They never hurt, and they make your intention obvious to the next reader.
Your First Real Formula: Revenue
What is a sale worth? Its Quantity times its Unit_Price. In M1 type Revenue, in M2 type the formula, press Enter, and double-click the fill handle:
Every row has a value. Look at rows 22 and 33: Quantity is blank, and Revenue says 0. In a calculation Excel treats an empty cell as 0, and 0 times any price is 0. Those sales did happen; we just do not know how many units were sold. Saying they were worth nothing is wrong, and in section 2 you will see exactly what those 25 zeros do to the averages. Section 3 fixes it.
Relative and Absolute References
When you filled Revenue down, Excel moved every reference with it: F2*G2 in row 2 became F3*G3 in row 3. That is a relative reference, and it is what you want almost every time. But sometimes every row must use the same one cell, such as a rate. A $ in front of the column letter and row number locks it: $B$2 stays $B$2 in every row. That is an absolute reference.
Add a sheet called Settings and type the staff discount, 5%, in B2. We want each unit price with the staff discount taken off. First, without the $:
Row 2 is right: 380,000 less 5% is 361,000. Every row after it is the full price. Click M3 and read the formula bar: it says Settings!B3, which is empty. Filling down moved the reference off the rate. Now with the $:
Every row now has the discount. G2 still moves (G3, G4...) because each row needs its own price; $B$2 stays put because every row needs the same rate. A quick way to add the dollars: click the reference in the formula bar and press F4. Change M back to the Revenue formula before you go on.
Picture a recipe card stuck to the fridge with a note: "salt: see the jar on the top shelf". The amounts change with the number of guests, but the salt always comes from the same jar. The guests are relative; the jar is absolute.
2. SUM, AVERAGE, COUNT, COUNTA, MIN and MAX
Definition
These six functions each take a range and return one number that describes it. They answer the first questions any manager asks: how much in total, how much on average, how many, the smallest and the biggest.
Real-Life Scenario
A class's test results. The teacher wants the total marks (SUM), the class average (AVERAGE), how many students wrote it (COUNT), how many names are on the list (COUNTA), and the lowest and highest score (MIN and MAX). Six questions, one column of numbers.
The Six Functions
| Function | Answers | Counts or uses |
|---|---|---|
| SUM(range) | What is the total? | Numbers only |
| AVERAGE(range) | What is the typical value? | Numbers only; empty cells and text are ignored, but a 0 counts |
| COUNT(range) | How many numbers are there? | Cells holding a number (or date) |
| COUNTA(range) | How many cells are filled? | Any cell that is not empty: text, numbers, anything |
| MIN(range) | What is the smallest? | Numbers only |
| MAX(range) | What is the largest? | Numbers only |
We put the answers in a small summary block in columns O and P, away from the data. Each formula covers rows 2 to 1,051. Revenue in M is still =F2*G2 for now.
Function 1 and 2: SUM and AVERAGE
TechPlug sold N1,067,343,000 from January to March 2024, and the average sale was N1,016,517.14. Hold on to that average.
Function 3 and 4: COUNT and COUNTA
COUNT on Quantity says 1,025; COUNTA on Transaction_ID says 1,050. The 25 missing are the blank quantities. But COUNT on Revenue says 1,050: every Revenue cell holds a number, because the blanks became 0. That is the problem. AVERAGE divided the total by 1,050 sales, including 25 sales that were counted as worth nothing. The average is too low.
COUNT on a text column, such as =COUNT(A2:A1051), returns 0, because Transaction_IDs are text. Use COUNTA to count records, and COUNT to count numbers. Comparing the two on a number column is a fast blank detector.
Function 5 and 6: MIN and MAX
The smallest "sale" is N0: not a real sale, one of the blanks. The largest is N6,990,000. MIN and MAX are quick validity checks, and here MIN has just caught the same problem COUNT did.
Add two rows to your summary block: the total number of units sold (SUM of Quantity) and the highest unit price (MAX of Unit_Price).
Check your answer
Units: =SUM(F2:F1051) gives 4,108. Highest price: =MAX(G2:G1051) gives 699,000 (the iPhone 14).
Picture the teacher's mark sheet with six numbers at the bottom in red pen. If absent students were written in as 0, the average would look worse than the class really did. A blank and a zero are not the same thing.
3. IF and Nested IF
Definition
IF asks a yes-or-no question and gives one answer if it is true and another if it is false:
IF(test, value if true, value if false)
The test is a comparison, such as F2>5, or a function that answers TRUE or FALSE, such as ISBLANK(F2). The two values can be text in quotes, numbers, or other formulas.
Real-Life Scenario
A bouncer at a club door: "If you are on the list, come in; otherwise, go home." One question, two outcomes, the same rule for every person in the queue.
Example 1: Fixing Revenue
Revenue should be worked out only when Quantity is there, and left empty when it is not. In IF words: if F2 is blank, write nothing (two double quotes, ""), otherwise multiply.
The total is the same, N1,067,343,000, because adding 0 never changed it. But AVERAGE now divides by the 1,025 sales whose value is known, and the average sale rises to N1,041,310.24. This is the Revenue column you keep. Every lesson from here uses it.
Example 2: High or Low, and the Trap
TechPlug wants every sale labelled High if its revenue is above N1,000,000 and Low otherwise.
Rows 22 and 33 have no Revenue, yet IF called them High. Revenue there is empty text, "", and in a comparison Excel treats any text as bigger than any number. So all 25 sales with no quantity were labelled High, with no warning. (Had Revenue still been =F2*G2, they would have been 0 and labelled Low: wrong the other way.) An empty value is not big or small; it is unknown.
Example 3: Nested IF, Catching the Blanks First
An IF can sit inside another IF. The outer one checks for an empty Revenue first; only if there is a value does the inner one decide High or Low. M2="" asks "is M2 empty text?".
Now 25 say Missing, 369 High and 656 Low. Read a nested IF from the left: the first test that is TRUE wins, and the rest are never looked at.
Example 4: Three Bands, and Why the Order Matters
Nested IF gives as many outcomes as you need. Large for N2,000,000 and above, Medium for N500,000 and above, Small for the rest. Check the biggest band first:
In the right order there are 160 Large sales. In the wrong order there are none: a sale of N4,194,000 is above 500,000, so the Medium test says yes and Excel never reaches the Large test. Test from the most demanding condition to the least.
Picture the bouncer with a second bouncer behind him: the first checks the list, and only people who pass reach the second, who checks the dress code. Whoever stands first decides who the second ever sees.
4. IFS
Definition
IFS takes pairs of test and result, in order, and returns the result of the first test that is TRUE. It does the same job as a nested IF without the pile of brackets:
IFS(test 1, result 1, test 2, result 2, ...)
IFS has no "otherwise". If no test is true it returns #N/A, so the last pair is usually TRUE, "result": TRUE is always true, so it catches everything left.
Real-Life Scenario
A school grading table on the wall: 70 and above is A, 60 and above is B, 50 and above is C, otherwise F. You read down it and stop at the first line that fits.
Exactly the same answer as the nested IF, row for row, and easier to read and change. Use IFS when you have Excel 2019 or newer; on older versions, nested IF does the same job.
Leave off the final TRUE,"Small" and every sale under N500,000 shows #N/A. And put the M2="" pair first, for the reason in section 3: empty text would otherwise pass the Large test.
5. AND and OR
Definition
AND returns TRUE only when every condition inside it is true. OR returns TRUE when at least one is. On their own they answer TRUE or FALSE; inside an IF, they let one test check two things.
Real-Life Scenario
A loan officer: "You get the loan if you earn over N500,000 AND have no unpaid loans" (both must be true). A bus conductor: "You ride free if you are a child OR a soldier in uniform" (either one is enough).
AND: Both Conditions
TechPlug's corporate team wants its bulk corporate sales: a Corporate customer buying 8 units or more.
48 sales qualify. A Corporate sale of 6 units fails the second condition, and a Retail sale of 10 fails the first: AND needs both. The "" means "write nothing", which keeps the column easy to read.
OR: Either Condition
205 sales came from one of the two Lagos branches. This only works because Branch was cleaned in Week 2: " Lagos Island" with spaces would not equal "Lagos Island".
Overall Example: Every Idea From Today in One Column
Read it pair by pair: blanks first, then the AND inside the OR, then everything else. Result: 115 sales to follow up today, 25 missing, 910 normal. Every part of it was taught today. Delete column N when you finish: Week 3 Virtual 1 builds the columns you keep.
Practical Exercises
Exercise 1: Very Easy
Write =COUNTA(K2:K1051). Why is it 1,050, when Customer_Type had 15 blanks in Week 1?
Exercise 2: Simple Application
Label each sale Bulk if Quantity is 5 or more, and Single otherwise. What does it say on the blank quantities, and is that right?
Exercise 3: Combination
Label each sale "Paid later" if Payment_Method is Credit and Customer_Type is Wholesale, and leave every other row empty.
Exercise 4: A Small Real-World Problem
Write an IFS that gives each sale a payment group: Instant for POS or Transfer, Cash for Cash, Owed for Credit.
Answers to the exercises
Exercise 1: Step 5 of the Cleaning Process filled the 15 blanks with Unknown. Exercise 2: =IF(F2>=5,"Bulk","Single"); a blank counts as 0, so the 25 blanks say Single, which is not known. Catch them first with IF(ISBLANK(F2),"Missing",...). Exercise 3: =IF(AND(L2="Credit",K2="Wholesale"),"Paid later",""). Exercise 4: =IFS(OR(L2="POS",L2="Transfer"),"Instant",L2="Cash","Cash",L2="Credit","Owed").
Checkpoint
Q1. What does =10+10/2 return, and why?
15. Division happens before addition.
Q2. You fill =G2*(1-Settings!B2) down and only row 2 is right. What is wrong?
The rate reference moved as it was filled. Lock it: Settings!$B$2.
Q3. Why does =F2*G2 give the wrong average sale?
A blank Quantity counts as 0, so 25 unknown sales are averaged in as worth nothing.
Q4. Why did IF label the empty Revenue cells High?
They hold empty text, and Excel treats any text as bigger than any number in a comparison.
Q5. Why does testing Medium before Large give zero Large sales?
Every Large sale is also at least 500,000, so the first test catches it and IF stops there.
Q6. What does IFS return if no test is true and there is no TRUE at the end?
#N/A.
Tick Before You Move On
- ☐ I can name the parts of a formula and predict the order Excel calculates in
- ☐ Revenue is in column M as =IF(ISBLANK(F2),"",F2*G2), filled to row 1,051
- ☐ I can lock a reference with $ (or F4), and I know when it is needed
- ☐ I can use SUM, AVERAGE, COUNT, COUNTA, MIN and MAX on the right range
- ☐ I know a blank becomes 0 in a calculation, and empty text beats any number in a comparison
- ☐ I can write IF, catch blanks with a nested IF, and order bands correctly
- ☐ I can write the same logic with IFS, and combine conditions with AND and OR
Quick Recap
A formula starts with =, follows BODMAS, and updates when its inputs change. Revenue is Quantity times Unit_Price, left empty where Quantity is unknown. Relative references move when filled; $ locks a reference that every row must share. SUM, AVERAGE, COUNT, COUNTA, MIN and MAX describe a whole column in one number each. IF chooses between two answers; nested IF and IFS between many, first true test wins. Catch empty values first: a blank is 0 in a sum, and empty text is "bigger" than any number.
Final Scenario
TechPlug's finance team reports an average sale of N1,016,517. The sales team says their average is higher.
You put COUNT on Quantity next to COUNTA on Transaction_ID: 1,025 against 1,050. Finance used =F2*G2, so 25 sales with unknown quantities went into the average as worth nothing. With Revenue left empty on those rows, the average is N1,041,310.
Both teams were right about what they could see. The difference was one IF.
The two counts disagreeing was the whole answer.
Next, Week 3, Virtual 1: you add Size and Zone columns on your own and make the numbers readable at a glance with conditional formatting.