Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
AVERAGEIF
The five Lagos orders average 52.8.
Try this lesson free →Example
AVERAGEIF has the same shape as SUMIF, but averages instead of adding:
=AVERAGEIF(C2:C9,"Lagos",F2:F9)| Order | Day | Location | Product | Qty | Revenue | Report | Answer | |
|---|---|---|---|---|---|---|---|---|
| O101 | Mon | Lagos | Earbuds | 2 | 70 | Avg Lagos order | ||
| O102 | Mon | Abuja | Charger | 1 | 15 | Avg Earbuds order | ||
| O103 | Tue | Lagos | Phone case | 3 | 24 | Avg Abuja order | ||
| O104 | Wed | Lagos | Earbuds | 1 | 35 | Avg Earbuds qty | ||
| O105 | Wed | Abuja | Earbuds | 4 | 140 | Avg Friday order | ||
| O106 | Thu | Lagos | Charger | 2 | 30 | Avg Charger order | ||
| O107 | Fri | Abuja | Phone case | 5 | 40 | |||
| O108 | Fri | Lagos | Earbuds | 3 | 105 |
Key points
AVERAGEIFAverages only the rows that match a condition.
C2:C9,"Lagos"Same as SUMIF: the range to check, then what to look for.
F2:F9Last, the range to average.
Put together: =AVERAGEIF(C2:C9,"Lagos",F2:F9).
Practice
1. In I3, find the average Earbuds order value.
=AVERAGEIF(D2:D9,"Earbuds",F2:F9)2. In I4, find the average Abuja order value.
=AVERAGEIF(C2:C9,"Abuja",F2:F9)3. In I5, find the average quantity in an Earbuds order. Average the Qty column this time.
=AVERAGEIF(D2:D9,"Earbuds",E2:E9)4. In I6, find the average Friday order value.
=AVERAGEIF(B2:B9,"Fri",F2:F9)5. In I7, find the average Charger order value.
=AVERAGEIF(D2:D9,"Charger",F2:F9)Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.
Try this lesson free →