MEDIAN

 Here are 10 examples of how to calculate the median in Excel, including sample data and formulas:

---

1. Basic Median Calculation

Data:
A1:A5 = {10, 20, 30, 40, 50}
Formula:
=MEDIAN(A1:A5)
Result:
30
Explanation:
The median of the numbers 10, 20, 30, 40, 50 is 30.

---

2. Median with Even Number of Data Points

Data:
A1:A6 = {10, 20, 30, 40, 50, 60}
Formula:
=MEDIAN(A1:A6)
Result:
35
Explanation:
The median is the average of the two middle numbers (30 and 40), which is 35.

---

3. Median with Text or Blank Cells

Data:
A1:A5 = {10, 20, "Text", 40, 50}
Formula:
=MEDIAN(A1:A5)
Result:
30
Explanation:
Excel ignores text and blank cells, so the median is calculated for {10, 20, 40, 50}.

---

4. Median with Logical Values

Data:
A1:A5 = {10, 20, TRUE, 40, FALSE}
Formula:
=MEDIAN(A1:A5)
Result:
20
Explanation:
Excel ignores logical values (TRUE/FALSE), so the median is calculated for {10, 20, 40}.

---

5. Median with Negative Numbers

Data:
A1:A5 = {-10, -20, -30, -40, -50}
Formula:
=MEDIAN(A1:A5)
Result:
-30
Explanation:
The median of negative numbers is calculated the same way as positive numbers.

---

6. Median with Decimals

Data:
A1:A5 = {1.5, 2.5, 3.5, 4.5, 5.5}
Formula:
=MEDIAN(A1:A5)
Result:
3.5
Explanation:
The median of decimal numbers is calculated the same way as whole numbers.

---

7. Median with Non-Adjacent Ranges

Data:
A1:A3 = {10, 20, 30}
B1:B3 = {40, 50, 60}
Formula:
=MEDIAN(A1:A3, B1:B3)
Result:
35
Explanation:
The median is calculated for the combined range {10, 20, 30, 40, 50, 60}.

---

8. Median with Conditions (Using Array Formula)

Data:
A1:A5 = {10, 20, 30, 40, 50}
B1:B5 = {"A", "B", "A", "B", "A"}
Formula:
=MEDIAN(IF(B1:B5="A", A1:A5))
Result:
30
Explanation:
The formula calculates the median for values in A1:A5 where the corresponding value in B1:B5 is "A". Use Ctrl+Shift+Enter for array formulas in older Excel versions.

---

9. Weighted Median

Data:
A1:A3 = {10, 20, 30}
B1:B3 = {2, 3, 1} (weights)
Formula:
=MEDIAN(REPT(A1:A3, B1:B3))
Result:
20
Explanation:
The formula repeats each value according to its weight and calculates the median. This requires a helper column or VBA for dynamic arrays.

---

10. Median for Filtered Data

Data:
A1:A5 = {10, 20, 30, 40, 50}
Filter: Hide row 3 (30)
Formula:
=SUBTOTAL(101, A1:A5)
Result:
30
Explanation:
The SUBTOTAL function with code 101 calculates the median for visible cells only.