Using Minimum, Maximum, Averages to aggregate data

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  |