Find the typical closing price and the most common bedroom count in last month's sales
MEDIAN finds the middle value no matter how far the extremes stretch, and MODE answers a completely different question — not what's typical, but what repeats — off the same kind of column.
Task:
You handle market reports for a real estate office. Last month's closings in one neighborhood include a five-bedroom estate that sold for far more than anything else nearby, and averaging it in with the other four sales would overstate what a typical buyer actually paid there — so the report calls for the median price instead. It also wants the bedroom count that shows up most often among homes that sold, since that tells buyers what's actually on the market at that price point. In B7, use MEDIAN to find the typical closing price from B2:B6. In B8, use MODE to find the most common bedroom count from C2:C6.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Address | Price | Bedrooms |
| 2 | 123 Oak St | 285000 | 3 |
| 3 | 47 Elm Ct | 310000 | 3 |
| 4 | 8 Birch Ln | 295000 | 4 |
| 5 | 912 Maple Dr | 1150000 | 5 |
| 6 | 56 Pine Way | 300000 | 3 |
| 7 | Median Price | ||
| 8 | Most Common Bedroom Count |
What this exercise teachesMay contain the answer
Sorted, the five prices are 285000, 295000, 300000, 310000 and 1150000, and MEDIAN(B2:B6) takes the one sitting in the middle of that order, 300000, rather than adding all five and dividing by five — which would land north of 460000 purely because one estate sold for nearly four times anything else on the street. MODE(C2:C6) is not looking for a middle value at all; it is counting repeats, and three of the five homes that sold had 3 bedrooms against one each at 4 and 5, so 3 is what MODE returns. The two functions start from the same kind of column and answer unrelated questions — MEDIAN would still return 300000 if that outlier sale had gone for ten million instead of 1150000, and MODE would still return 3 even if every price in the sheet doubled overnight.