3.4 AVERAGE, AVERAGEIF(S), MINIFS and MAXIFS
| Function | Syntax | Example | Result |
|---|---|---|---|
| AVERAGE | =AVERAGE(range) |
=AVERAGE(H2:H11) |
12.8 mins |
| AVERAGEIF | =AVERAGEIF(range, criteria, [average_range]) |
=AVERAGEIF(C2:C11,"Pune",H2:H11) |
11 mins |
| AVERAGEIFS | =AVERAGEIFS(avg_range, crit_range1, crit1, …) |
=AVERAGEIFS(G2:G11,D2:D11,"Blinkit") |
333.29 |
| MINIFS (Excel 2019+) | =MINIFS(min_range, crit_range1, crit1, …) |
=MINIFS(H2:H11,I2:I11,"Delivered") |
8 mins |
| MAXIFS (Excel 2019+) | =MAXIFS(max_range, crit_range1, crit1, …) |
=MAXIFS(G2:G11,E2:E11,"Fruits") |
270 |
Other useful statistics: MEDIAN, MODE.SNGL, STDEV.S, LARGE(range,k), SMALL(range,k). Example: second-highest order =LARGE(G2:G11,2) → 270.
Worked example – AOV (Average Order Value) for delivered orders: =AVERAGEIFS(G2:G11,I2:I11,"Delivered") → 2,215 ÷ 7 = ₹316.43.
Ravindra Bagale's Tip
Many students don't understand the difference between blank and zero in an average – AVERAGE skips blank cells but counts 0. If you enter 0 as the amount of a cancelled order, the AOV drops. Before calculating an average, decide exactly which data should be included, and put the condition (Delivered) in AVERAGEIFS.
Ravindra Bagale's Tip – मराठी
Average मध्ये blank आणि zero यातला फरक बऱ्याच students ना कळत नाही – AVERAGE blank cells सोडतो पण 0 मोजतो. Cancelled order चा amount 0 टाकला तर AOV खाली येतो. Average काढताना नेमका कोणता data घेतला पाहिजे ते आधी ठरवा, आणि condition (Delivered) AVERAGEIFS मध्ये टाका.
Ravindra Bagale's Tip – हिंदी
Average में blank और zero का फ़र्क बहुत से students नहीं समझते – AVERAGE blank cells छोड़ देता है पर 0 गिनता है. Cancelled order का amount 0 डाल दिया तो AOV नीचे आ जाता है. Average निकालने से पहले तय करो कि ठीक-ठीक कौन सा data लेना है, और condition (Delivered) AVERAGEIFS में डालो.
Practice task
Find the average delivery time for each city, the fastest delivered order in Nashik (MINIFS), and the third-largest order value (LARGE).