The Case, five steps guide
Most Excel tests for a job end with a case: a messy export, a few steps, and a question someone actually wants answered. Here it is "which supplier is late most often?", on 48 deliveries with stray spaces, a " Ltd" here and there, deliveries that haven't arrived and a grace period on a second sheet.
Read the question before the data
"Late most often" is a share: late deliveries out of the deliveries that arrived. The supplier with the most late deliveries can also be the one that delivers most, so a count gives a different, wrong answer. Decide what the final number is before you write the first formula.
Step by step
- Days late.
=IF(D2="", "", MAX(0, D2-C2)). An empty received date means the delivery hasn't come, which is not the same as on time. MAX stops early deliveries from going negative. See days between dates. - Clean names.
=TRIM(SUBSTITUTE(B2, " Ltd", "")). A count on the export column misses "Kestrel Foods " with a space and "Brightline Print Ltd". See TRIM. - Grace days.
=XLOOKUP(I2, Terms!$A$2:$A$6, Terms!$B$2:$B$6). The Terms sheet lists the suppliers in another order, so pointing at the row next door gives someone else's grace. See another sheet. - Counts and share. Arrived:
=COUNTIFS($G$2:$G$49, I2, $D$2:$D$49, "<>"). Late:=COUNTIFS($G$2:$G$49, I2, $F$2:$F$49, ">"&J2). Share:=L2/K2. The">"&J2joins the operator to the number in J2; a delivery exactly at the grace limit isn't late. - The answer.
=INDEX($I$2:$I$6, MATCH(MAX($M$2:$M$6), $M$2:$M$6, 0)), or=XLOOKUP(MAX(M2:M6), M2:M6, I2:I6).
Why helper columns
One long formula could do steps 1 to 4 at once. Helper columns are easier to check: you can look at F and G row by row, filter them, and see which step is wrong when the total looks odd. Whoever opens the file after you can follow it too.
Check it by hand once
Filter the clean supplier column to one name and the days late to more than its grace, and compare the row count with your formula. On a test, that minute is worth more than the time it costs.
Where it goes wrong
- Counting deliveries that haven't arrived. They make every share smaller.
- Counting the export column. The rows with spaces or " Ltd" drop out.
- >= instead of >. Deliveries exactly at the grace limit count as late.
- Ignoring the grace days. A supplier with three days' grace looks worst.
- The most late deliveries instead of the highest share. The question is how often.
FAQ
- What is an Excel case study test?
- A longer task at the end of many job tests: a data set with problems in it, several steps, and a question to answer from the result. It checks whether you can pick the tools and check your own work, not just write one formula.
- How do I count late deliveries by supplier in Excel?
- Work out days late in a helper column, then count with two conditions: =COUNTIFS(supplier_range, name, days_late_range, ">"&grace). Clean the supplier names with TRIM first.
- How do I calculate the percentage of late deliveries?
- Divide the late deliveries by the deliveries that arrived, not by every order, and format the result as a percentage.
- How do I return the name next to the highest value?
- Use =INDEX(names, MATCH(MAX(values), values, 0)), or =XLOOKUP(MAX(values), values, names).
