Excel Data Analysis Practice: RANK, MEDIAN, SUMPRODUCT and More
Once a sheet can calculate, the next question is what the numbers say. Who sold the most, and who came second? Is the average a fair picture, or is one outlier pulling it? What is the average price when some products sell far more than others? How many different customers placed these orders, rather than how many orders?
These exercises work through those questions in order. You compare the average with the median, pick the nth largest or smallest value, rank values and see how ties behave, weight an average with SUMPRODUCT, and use UNIQUE and FILTER to count distinct customers. Each is built on a small business dataset and checked on other data too, so the formula has to answer the question, not only today's numbers.
What these functions are for
- Decide whether the average or the median describes typical data when it contains an outlier.
- Find the top three, or the second smallest, without sorting the data.
- Rank a list and understand what happens to tied values.
- Compute a weighted average, such as an average price weighted by the quantity sold.
- Count distinct customers or items, and filter a list by a condition with the dynamic array functions.
Tips before you start
- Look at the data before choosing between average and median. One very large value makes the average a poor description of the rest.
- LARGE and SMALL count from the top and the bottom. Check which end the nth value comes from before you trust the result.
- Lock the list you rank against, so every row is compared with the same range.
- In SUMPRODUCT, every range must have the same number of rows, or the formula returns an error.
- Count customers, not orders: remove the repeats first, then count what is left.
Skills covered
- AVERAGE
- LARGE
- Absolute references
- Business analysis
- Distinct count
- Dynamic arrays
- FILTER
- MEDIAN
- Outliers
- RANK
- ROWS
- SMALL
- SUMPRODUCT
- Ties
Common mistakes to watch for
- Reporting the average of skewed data as typical
- A single extreme value moves the average a long way and the median hardly at all. When a few values dominate, the median is the fairer summary.
- Not locking the list when ranking
- If the range shifts as the formula is copied down, each row is ranked against a different list and the positions stop making sense.
- Using AVERAGE for a weighted average
- A plain average treats every row as equal. When quantities differ, multiply each value by its weight, add the results and divide by the total weight.
- Ranges of different sizes
- SUMPRODUCT needs its ranges to line up one to one. A range that is one row longer or shorter fails.
- Counting orders instead of customers
- A customer with five orders appears five times. Take the unique list first, then count it, to get the number of customers.
Practice exercises, in a sensible order
Typical Salary: AVERAGE, MEDIAN, LARGE and SMALL
Choose between the average and the median when data has an outlier, and pick the nth largest or smallest value with LARGE and SMALL.
Sales Leaderboard with RANK
Rank values against a fixed list, understand how tied values are ranked, and compare first and second place.
Weighted Average Price with SUMPRODUCT
Multiply two columns and add the results in one formula with SUMPRODUCT, and use it to compute a weighted average.
Count Distinct Customers with UNIQUE and FILTER
Remove duplicates with UNIQUE, filter a list with FILTER, and count the result with ROWS to get a single number.