Largest and Smallest Deals with MAXIFS and MINIFS
Excel version: MAXIFS and MINIFS need Excel 2019 or Microsoft 365.
Your task: In G2:G5, find Dana's largest deal, her smallest, her largest in Q2, and the gap between her extremes.
Full instructions, scenario and goal
Scenario
A sales director reviews the deal list of her team. Each row has the salesperson, the quarter and the deal value. Before a one-to-one with Dana, she wants to see Dana's largest and smallest deals, her biggest deal in the second quarter, and how far apart her extremes are.
Your task
- In G2, find the value of Dana's largest deal.
- In G3, find the value of Dana's smallest deal.
- In G4, find Dana's largest deal in quarter Q2 only (two conditions).
- In G5, find the difference between Dana's largest and smallest deals.
- Use the full list of deals (rows 2 to 13), then select Check Answer.
You are done when
- Only Dana's deals are considered, and in G4 only her Q2 deals.
- G5 is calculated from the results above it, not typed.
What you will practise
Find the largest or smallest value among only the rows that meet one or more conditions.
The idea in 30 seconds
MAX and MIN look at every number in a range. When you need the largest or smallest value for one salesperson, product or quarter, you need a version that applies a condition first: MAXIFS and MINIFS. They list the range of numbers, then one or more pairs of a range to test and a condition. A row is used only if it meets every condition.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell G2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
MAX and MIN look at every number in a range. When you need the largest or smallest value for one salesperson, product or quarter, you need a version that applies a condition first: MAXIFS and MINIFS. They list the range of numbers, then one or more pairs of a range to test and a condition. A row is used only if it meets every condition.
These functions answer questions that otherwise need filtering by hand: what is this rep's best month, the cheapest order from a supplier, or the largest refund in a region. The result updates when rows are added, as long as the ranges cover them. If no row matches, MAXIFS and MINIFS return 0, which can look like a real value, so check that your conditions really match something.
Skills you will practise
- MAXIFS
- MINIFS
- Criteria
- Conditional extremes
Topic guide: Excel SUMIF, COUNTIF and SUMIFS Practice
Complete first: Total Revenue for One Region
Common mistakes to avoid
- Using MAX or MIN on the whole column
- They ignore whose deal it is, so the answer belongs to whoever is largest in the team.
- Forgetting a condition
- In G4, testing only the rep returns her best deal of the year, not of Q2.