Average Order Value with AVERAGEIF and AVERAGEIFS
Your task: In F2:F4, find the average order value for East, for East online orders, and for orders of 100 or more.
Full instructions, scenario and goal
Scenario
An online retailer keeps a list of recent orders with the sales region, the channel (online or in store) and the order value. The regional manager wants average order values, not totals, to compare how much customers spend per order in different situations.
Your task
- In F2, find the average order value for the East region.
- In F3, find the average order value for East orders placed Online.
- In F4, find the average value of orders that are 100 or more.
- Use the full order range (rows 2 to 11), then select Check Answer.
You are done when
- Each average includes only the rows that match its condition(s).
- The formulas cover the whole order list and follow changes to it.
What you will practise
Average only the rows that meet one or several conditions, and choose AVERAGEIF or AVERAGEIFS accordingly.
The idea in 30 seconds
AVERAGEIF averages the numbers in a range, but only for the rows where a condition is true. You give it the range to test, the condition (for example "East", or ">=100" for a comparison) and, when the numbers to average are in a different column, the range of numbers. If you leave that last range out, Excel averages the cells that were tested.
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 F2. 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
AVERAGEIF averages the numbers in a range, but only for the rows where a condition is true. You give it the range to test, the condition (for example "East", or ">=100" for a comparison) and, when the numbers to average are in a different column, the range of numbers. If you leave that last range out, Excel averages the cells that were tested.
AVERAGEIFS handles several conditions at once and puts the numbers to average first: the range to average, then each pair of range and condition. A row counts only when it meets every condition. This is different from dividing a conditional total by the number of all rows, which dilutes the result with rows that do not match. If nothing matches, the result is a #DIV/0! error, because an average of nothing is undefined.
Skills you will practise
- AVERAGEIF
- AVERAGEIFS
- Criteria
- Conditional average
Topic guide: Excel SUMIF, COUNTIF and SUMIFS Practice
Complete first: Total Revenue for One Region
Common mistakes to avoid
- Dividing by all rows
- A conditional total divided by every order dilutes the average. Divide only by the matching rows, or let AVERAGEIF do it.
- Argument order in AVERAGEIFS
- AVERAGEIFS starts with the range to average, unlike AVERAGEIF, where it comes last.