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
Average madhe blank aani zero ha farak khup students la kalat nahi – AVERAGE blank cells sodto pan 0 mojto. Cancelled order cha amount 0 takla tar AOV khali yeto. Average kadhtana nemka konta data ghetla pahije te aadhi tharva, aani condition (Delivered) AVERAGEIFS madhe taka.
Practice task
Find the average delivery time for each city, the fastest delivered order in Nashik (MINIFS), and the third-largest order value (LARGE).