The last questions in a spreadsheet screen are where interviewers separate people who know the functions from people who think about the data. Each one below hides a trap that a quick answer falls into:
- a lookup that needs two keys, where VLOOKUP stops at the first match;
- a running total that must restart for each customer;
- ties, where the "second highest" value is the highest one again;
- duplicates, where counting matches isn't the same as counting customers.
Talk through the trap before you type: "Two orders tie at the top, so I'll remove duplicates first." Then check your answer against a case you can work out by hand. Any approach that gives the right result is accepted, and each answer is checked on changed data too.