Course outline / Statistical functions
Lesson 44 of 75 · Intermediate · 4 exercises
Conditional averages and extremes
AVERAGEIFS, MAXIFS and MINIFS with several conditions.
What you'll practise
- Average with two conditions
- Biggest order by one rep
- Smallest order in a region
- Average over a threshold in one region
Average with two conditions
AVERAGEIFS averages the values that meet every condition. The range to average comes first, then pairs of range and condition.
In cell F2, average the amounts in D2:D6 for the East region. Use AVERAGEIFS even though there is one condition.
Biggest order by one rep
In cell F2, find Ana's largest order: the biggest amount in D2:D6 where C2:C6 is "Ana".
Smallest order in a region
In cell F2, find the smallest West order: the lowest amount in D2:D6 where B2:B6 is "West".
Average over a threshold in one region
In cell F2, average the East amounts in D2:D6 that are over 200.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.