Descriptive Statistics in Excel: Mean, Median, Mode, Standard Deviation

Descriptive statistics help summarize data using central tendency and variability measures like mean, median, mode, and standard deviation. Here’s an explanation of each, with examples.

---

1. Mean (Average)

The mean is the sum of all values divided by the total number of values.

Example:

Dataset:

Values
10
20
30
40
50

Formula in Excel:

1. Enter the values into cells, e.g., A1:A5.
2. Use the formula: =AVERAGE(A1:A5)
3. Result:
   The mean is 30 (i.e., (10+20+30+40+50)/5).

---

2. Median

The median is the middle value of a sorted dataset. If the number of values is even, the median is the average of the two middle values.

Example:

Dataset:

Values
10
20
30
40
50

Formula in Excel:

1. Use the formula: =MEDIAN(A1:A5)
2. Result:
   The median is 30 (the middle value).

---

3. Mode

The mode is the most frequently occurring value in a dataset.

Example:

Dataset:

Values
10
20
20
30
40

Formula in Excel:

1. Use the formula: =MODE.SNGL(A1:A5)
2. Result:
   The mode is 20 (it appears twice).

---

4. Standard Deviation

Standard deviation measures the spread of data from the mean. A lower standard deviation indicates data is closer to the mean, while a higher value indicates more spread.

Example:

Dataset:

Values
10
20
30
40
50

Formula in Excel:

1. Use the formula for a sample dataset: =STDEV.S(A1:A5)
2. For a population dataset, use: =STDEV.P(A1:A5)
3. Result:
   The standard deviation (sample) is approximately 15.81.

---

Practical Use Cases in Data Analysis

1. Mean: Determine the average sales, revenue, or expenses.
2. Median: Identify the middle value for skewed datasets (e.g., housing prices).
3. Mode: Find the most common product sold or survey response.
4. Standard Deviation: Assess consistency, such as in delivery times or test scores.

Real-World Dataset Example: Analyzing Sales Data

Dataset:

Region	Sales ($)
North	1000
East	2000
West	1500
South	3000
Central	2500

---

Step-by-Step Analysis:

1. Mean (Average Sales)

• Formula: =AVERAGE(B2:B6)
• Steps:
  1. Select a blank cell (e.g., C2).
  2. Type the formula =AVERAGE(B2:B6) and press Enter.
• Result:
  The mean sales value is $2000.

---

2. Median (Middle Value of Sales)

• Formula: =MEDIAN(B2:B6)
• Steps:
  1. Select a blank cell (e.g., C3).
  2. Type the formula =MEDIAN(B2:B6) and press Enter.
• Result:
  The median sales value is $2000.

---

3. Mode (Most Frequent Sales Value)

• Formula: =MODE.SNGL(B2:B6)
• Steps:
  1. Select a blank cell (e.g., C4).
  2. Type the formula =MODE.SNGL(B2:B6) and press Enter.
• Result:
  In this dataset, no value repeats, so the result will be an error (N/A).

---

4. Standard Deviation (Spread of Sales Data)

• Formula for a Sample: =STDEV.S(B2:B6)
• Steps:
  1. Select a blank cell (e.g., C5).
  2. Type the formula =STDEV.S(B2:B6) and press Enter.
• Result:
  The standard deviation of sales is approximately 707.11.

---

Insights:

• Mean ($2000): The average sales across regions.
• Median ($2000): Half of the regions have sales above $2000, and half below.
• Mode (N/A): No repeating values in the sales data.
• Standard Deviation ($707.11): Sales vary widely from the mean, indicating inconsistency.

---