Average one rep's orders
What is Maya's average order, rounded to a whole number?
Formula: =ROUND(AVERAGEIF(A2:A7,"Maya",C2:C7),0) → 513
Hands-on Excel exercises
Excel Test for a Job Interview
Prepare for a live Excel exercise in an interview: a small sales sheet, a conditional total, worked answers, and tips for explaining your steps.
A spreadsheet exercise in an interview is usually small: a few dozen rows, a request that sounds like real work, and an interviewer who cares as much about how you think as about the final number. You might be asked for one rep's total above a threshold, an average for a group, or a lookup that must not break when a name is missing. The clock is short, so the winning habit is calm structure rather than speed.
Practice in three beats. First, restate the request in your own words and say what the answer should roughly be. Second, build the smallest formula that works, usually with SUMIFS, COUNTIFS, AVERAGEIF, or a lookup. Third, check it against something you can see, such as two rows you add by hand. Saying those steps aloud feels awkward at first, but it is exactly what interviewers hope to hear.
The sheet below is a short order list for three reps. Your task is a conditional total with two criteria, then three worked answers add an average, a safe lookup for a rep with no orders, and a count. Try them without looking, then compare. If you want an unscripted rehearsal, the Excel interview questions page lists common spoken questions with short answers.
Your turn
Your turn: in F2, total the orders for Maya that are above 500.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Order amount | |||
| 2 | Maya | East | 620 | |||
| 3 | Omar | West | 450 | |||
| 4 | Maya | East | 380 | |||
| 5 | Lena | North | 910 | |||
| 6 | Maya | West | 540 | |||
| 7 | Omar | East | 700 | |||
| 8 |
Not yet solved.
Answer: =SUMIFS(C2:C7,A2:A7,"Maya",C2:C7,">500")
Practice under a time limit
Try the free Sample mock exam with 4 tasks in 8 minutes, or browse the level and interview mock exams included with Pro + Exams.
Related exercises
Open a task in its lesson.
- In C7, calculate how many items were bought in total.
- In C2:C8, write "Pass" if the score is at least the passing score (F1), otherwise "Fail".
- In F2, calculate the total of Emma's deals in Boston.
- In G2:G4, look up the item price.
- In G2:G6, turn the text in column A into numbers (some have spaces at the edges).
- Build a second PivotTable: the average deal amount by city, with the result in F8.
- In F10:F13, find the prepayment discount from the scale in F2:G5 (approximate match on the number of months).
- Fix D3: the average price of coffee.
Worked answers
Try the spreadsheet first, then compare your formula and result.
Look up a rep with no orders
Return the first order amount for Zed, or the text No orders if Zed is not in the list.
Formula: =IFERROR(VLOOKUP("Zed",A2:C7,3,FALSE),"No orders") → No orders
Count large orders for one rep
How many of Omar's orders are above 500?
Formula: =COUNTIFS(A2:A7,"Omar",C2:C7,">500") → 1
Frequently asked questions
How much time do interview exercises usually allow?
It varies, from a few minutes to half an hour. Practice with a short limit, and use the free Sample mock exam (4 tasks in 8 minutes) to feel the pressure.
Should I talk while I work?
If the interviewer is watching, yes. Name the inputs, the expected size of the answer, and your check. Silence makes a correct answer harder to appreciate.
What if I forget a function name?
Say so, describe what you need, and use another route. A correct alternative built from smaller pieces beats a stalled screen.
Which shortcuts matter most?
Moving between ranges, filling formulas down, and toggling absolute references. Being fluent with those saves more time than knowing rare functions.