Chapter 02 · 7 min · Basic
What are they actually asking
Did the promo work?
You cannot compute that. There is no column called worked. Before a single formula you have to turn the request into something a sheet can answer — which rows count as the promo, what you are measuring about them, what you are dividing by, and over what stretch of time.
That is not paperwork. On the ten orders below, two completely defensible readings of that one sentence disagree about whether the promo helped or hurt. Same file, same functions, opposite verdicts.
The grid
Ten orders in A1:D11 — order_id, status (completed, refunded or cancelled), amount_myr, and used_promo. Every amount is a real number here; nothing is broken. The difficulty is entirely in deciding which rows belong. Work in column F.
Start with the population
The promo is not a fact about the world, it is a set of rows. Name it first: the orders where used_promo is yes. COUNTIFS takes a range and what to match in it, and tells you how big that set is.
Do this before deciding what to measure. A metric whose population you have not stated is a metric nobody can check — and how many rows is that is the first thing anyone senior will ask you.
Count the yes rows in column D by eye, then run it.
Scroll to see all 6 columns →
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | order_id | status | amount_myr | used_promo | ||
| 2 | 2001 | completed | 120.00 | yes | =COUNTIFS(D2:D11,"yes") | |
| 3 | 2002 | completed | 45.50 | yes | ||
| 4 | 2003 | refunded | 210.00 | yes | ||
| 5 | 2004 | completed | 63.00 | no | ||
| 6 | 2005 | cancelled | 88.00 | yes | ||
| 7 | 2006 | completed | 32.75 | yes | ||
| 8 | 2007 | completed | 150.00 | no | ||
| 9 | 2008 | refunded | 27.40 | yes | ||
| 10 | 2009 | completed | 91.20 | yes | ||
| 11 | 2010 | completed | 54.00 | no |
Basic
The rest of this chapter is Basic
The Excel tutorial and Spreadsheet Fundamentals are free here, forever. This track is the paid half: what to actually do with a spreadsheet once somebody sends you one, from the first look to a summary you can defend.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Reading one: every order that used it
- 03Reading two: only the ones that stuck
- 04The same file, the opposite answer
- 05Write the four things down