Typical Salary: AVERAGE, MEDIAN, LARGE and SMALL
Your task: In D2:D5, find the average and median salary, the 3rd highest, and the 2nd lowest.
Full instructions, scenario and goal
Scenario
An HR analyst is preparing a salary summary for a small department. Eight employees earn between 36,900 and 45,100, but the head of department earns far more. The analyst wants to show how much that one salary distorts the average, and to list the third-highest and second-lowest salaries.
Your task
- In D2, calculate the average salary of the nine employees.
- In D3, calculate the median salary.
- In D4, find the third-highest salary.
- In D5, find the second-lowest salary.
- Use the salary range B2:B10 in every formula, then select Check Answer.
You are done when
- The average and the median both read the whole salary list.
- The ranked values come from functions that sort for you, not from reading the table by eye.
What you will practise
Choose between the average and the median when data has an outlier, and pick the nth largest or smallest value with LARGE and SMALL.
The idea in 30 seconds
The average adds everything up and divides by the count, so one extreme value pulls it a long way. The median is the middle value once the numbers are sorted (or the mean of the two middle ones when there is an even count), so an extreme value barely moves it. When data is skewed, such as salaries, house prices or response times, the median is usually a fairer description of what is typical.
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 D2. 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
The average adds everything up and divides by the count, so one extreme value pulls it a long way. The median is the middle value once the numbers are sorted (or the mean of the two middle ones when there is an even count), so an extreme value barely moves it. When data is skewed, such as salaries, house prices or response times, the median is usually a fairer description of what is typical.
LARGE(range, k) returns the kth largest number: k = 1 is the maximum, k = 2 the runner-up, k = 3 the third. SMALL(range, k) does the same from the bottom. They sort the values for you, so you never have to rearrange the data. All of these functions ignore empty cells and text in the range.
Skills you will practise
- MEDIAN
- LARGE
- SMALL
- AVERAGE
- Outliers
Complete first: Total, Average and Highest Monthly Spend
Common mistakes to avoid
- Reporting the average of skewed data as typical
- One very large value drags the average up. The median ignores how large the extreme values are.
- Counting positions from the wrong end
- LARGE counts from the top and SMALL from the bottom. LARGE(range,2) is the runner-up, not the second-lowest.