1️⃣ What Does “Aggregate Data” Mean?
To aggregate data means:
To summarize a large set of numbers into a single meaningful value.
Instead of looking at 50 sales numbers, you may want:
- The smallest sale
- The largest sale
- The average sale
This is where MIN, MAX, and AVERAGE help.
2️⃣ Example Dataset
Imagine this is your Excel sheet:
| Employee | Sales | |----------|-------| | John | 5000 | | Mary | 7200 | | David | 6100 | | Anna | 4500 | | James | 8000 |
Assume:
- Employee names are in column A
- Sales are in column B
- Data range is
B2:B6
3️⃣ MIN Function (Minimum)
🔹 What It Does
Returns the smallest number in a range.
🔹 Syntax
=MIN(range)
🔹 Example
=MIN(B2:B6)
Result:
| Calculation | Result | |----------------|--------| | Minimum Sales | 4500 |
✔ The lowest sales value is 4500 (Anna).
4️⃣ MAX Function (Maximum)
🔹 What It Does
Returns the largest number in a range.
🔹 Syntax
=MAX(range)
🔹 Example
=MAX(B2:B6)
Result:
| Calculation | Result | |----------------|--------| | Maximum Sales | 8000 |
✔ The highest sales value is 8000 (James).
5️⃣ AVERAGE Function
🔹 What It Does
Returns the mean (average) value.
It adds all numbers and divides by how many there are.
🔹 Syntax
=AVERAGE(range)
🔹 Example
=AVERAGE(B2:B6)
Manual Calculation:
(5000 + 7200 + 6100 + 4500 + 8000) ÷ 5 = 6160
Result:
| Calculation | Result | |----------------|--------| | Average Sales | 6160 |
✔ The average sales amount is 6160.
6️⃣ Combining MIN, MAX, and AVERAGE
You can create a summary section in Excel like this:
| Metric | Formula | Result | |-----------------|--------------------|--------| | Minimum Sales | =MIN(B2:B6) | 4500 | | Maximum Sales | =MAX(B2:B6) | 8000 | | Average Sales | =AVERAGE(B2:B6) | 6160 |
This is called a summary table.
7️⃣ Using MIN, MAX, AVERAGE with Conditions (Extra Knowledge)
Sometimes you only want to calculate based on a condition.
Example: Only sales above 5000.
Using AVERAGEIF
=AVERAGEIF(B2:B6, ">5000")
Using MINIFS (Excel 2016+)
=MINIFS(B2:B6, B2:B6, ">5000")
Using MAXIFS
=MAXIFS(B2:B6, B2:B6, ">5000")
8️⃣ Real-Life Business Example
Imagine:
| Month | Expenses | |---------|----------| | Jan | 1200 | | Feb | 1500 | | Mar | 900 | | Apr | 2000 | | May | 1100 |
You want:
- Lowest expense month
- Highest expense month
- Average monthly expense
Formulas:
=MIN(B2:B6) =MAX(B2:B6) =AVERAGE(B2:B6)
Results:
| Metric | Value | |----------------------|-------| | Lowest Expense | 900 | | Highest Expense | 2000 | | Average Expense | 1340 |