Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 3, Virtual 2
Conditional Aggregation Practice
Five business questions, no tutor, one formula each. Then the same skill goes to work on your pod's Verdant Foods data.
By the end of this class you can
- Turn a business question into the right function, ranges and criteria
- Check an answer with a filter before trusting it
- Write one sentence that says what a number means for the business
- Start the Week 3 pod task with a Revenue column
In Live 2 the questions came with the function attached. Real questions never do. A manager does not say "please use COUNTIFS"; she says "how many corporate sales did Abuja make in March?" Today you practise the translation.
What you do in this class
- Learn the six-step method in Part 1 on a worked example.
- Answer the five questions in Part 2 on your own TechPlug file. Open each answer only after you have one.
- Write a one-sentence meaning for each answer (Part 3).
- Open the Week 3 pod task and start Verdant Foods (Part 4).
Hand in: Five formulas with their answers in a summary area beside the data, and five one-sentence meanings.
Part 1: From Question to Formula in Six Steps
| Step | Ask yourself | For "How much did Aisha Musa sell in Laptops?" |
|---|---|---|
| 1. Measure | Total, count or average? | Total: SUMIFS |
| 2. Which column holds it? | The thing being added, counted or averaged | Revenue, M |
| 3. Conditions | Every "only" or "for" in the question | Only Aisha Musa; only Laptops |
| 4. Which columns hold them? | One range per condition | Staff_Name, I; Category, E |
| 5. Write it | Function, then pairs | SUMIFS(M, I, "Aisha Musa", E, "Laptops") |
| 6. Check it | Filter the same conditions and read the status bar Sum | Filter I and E, select K |
The status bar Sum, 23,690,000, matches the formula. Two independent routes, one answer: now you can trust it. Clear the filters before the next question.
Picture translating a request at a market stall: "the red ones, only the big ones, how many?" becomes COUNTIFS before you have finished listening.
Part 2: Five Questions
Put each answer in the summary area beside the data, as in Live 2: a short label in column O and the formula in column R, from row 2 down. Use the full ranges, row 2 to row 1,051. Open an answer only when you have your own.
Q1. What was the total revenue from Accessories in the Kano branch? Hint: Total, one category, one branch.
Q2. How many wholesale sales did Seun Olatunji make? Hint: Count, two conditions.
Q3. What is the average value of a retail sale? Hint: Average, one condition.
Q4. Which branch made more from Smartphones: Abuja or Ibadan? Hint: Two formulas, one per branch, then compare.
Q5. What share of the quarter's revenue came from March? Hint: A SUMIFS over a date range divided by the SUM of the whole column. Format the cell as a percentage.
Answer to Q1.
N2,359,000. Formula: =SUMIFS(M2:M1051,E2:E1051,"Accessories",J2:J1051,"Kano")
Answer to Q2.
47 sales. Formula: =COUNTIFS(I2:I1051,"Seun Olatunji",K2:K1051,"Wholesale")
Answer to Q3.
N1,109,769.68. Formula: =AVERAGEIF(K2:K1051,"Retail",M2:M1051)
Answer to Q4.
Ibadan: Abuja N17,855,000 against Ibadan N37,087,000. Formula: =SUMIFS(M2:M1051,E2:E1051,"Smartphones",J2:J1051,"Abuja") and =SUMIFS(M2:M1051,E2:E1051,"Smartphones",J2:J1051,"Ibadan")
Answer to Q5.
33.9%. Formula: =SUMIFS(M2:M1051,B2:B1051,">="&DATE(2024,3,1),B2:B1051,"<="&DATE(2024,3,31))/SUM(M2:M1051)
A SUMIFS that returns 0 almost always has criteria that match nothing: a misspelt name, an extra space, or ranges of different lengths. Filter the same conditions; if the filter finds rows, your formula is wrong, not the data.
Part 3: What Does the Number Mean?
A number on its own is information. One sentence that says what it means for the business turns it into insight, which is what your pod task asks for next to every answer. A good meaning sentence does three things: it compares the number to something, it says why it matters, and it is short enough to read aloud in one breath.
| Weak | Strong |
|---|---|
| "March revenue was 33.9%." | "March brought in 33.9% of the quarter's revenue, more than an even third, but per day it was within 2% of January, so there is no sign of a slowdown." |
| "Abuja made N17,855,000 from smartphones." | "Ibadan sells more smartphones by value than Abuja, so it should be first in line when phone stock is short." |
Write a strong meaning sentence for your Q2 and Q3 answers. Read each aloud: if you run out of breath, cut it.
Picture handing a number to a busy manager in a corridor. She has ten seconds. The number alone gets a nod. The number plus "so we should..." gets a decision.
Part 4: Over to Verdant Foods
Open your pod's workbook and go to the Cleaned Data tab. Quantity is G, Unit_Price is H and Quantity_Flag is K. The first job is a Revenue column in L, and it has to respect the flag: a transaction with no Quantity has no known revenue, and must not be counted as 0 or guessed.
The Week 3 pod task page has the full brief, the formula and the eight business questions. Read it now and agree in your pod who answers which questions.
Checkpoint
Q1. What are the six steps from a question to a formula?
Measure, which column holds it, the conditions, which columns hold them, write it, check it.
Q2. How do you check a SUMIFS answer without another formula?
Filter the same conditions, select the column being added, and read Sum in the status bar.
Q3. What makes a meaning sentence strong?
It compares, says why it matters, and is short.
Tick Before You Move On
- ☐ I can translate a question into a function, ranges and criteria
- ☐ I answered all five questions and checked at least two with filters
- ☐ I wrote a strong meaning sentence for each answer
- ☐ I have read the Week 3 pod task and my pod has split the questions
Quick Recap
Six steps: measure, column, conditions, condition columns, write, check. Two routes to the same number, a formula and a filter, are how you earn trust in it. A meaning sentence compares, explains and fits in one breath.
Next: Pod Task Week 3. The Revenue column for Verdant Foods and the eight business questions, with formulas visible and a one-sentence meaning for each.