FREQUENCY

 The FREQUENCY function in Excel is used to calculate how often values occur within a range of values, and it returns a vertical array of numbers. It is particularly useful for creating frequency distributions.

Here are 10 examples of how to use the FREQUENCY function, including sample data and formulas:

---

Example 1: Basic Frequency Distribution

Data:
A1:A10 = {5, 10, 15, 20, 25, 30, 35, 40, 45, 50}
Bins: B1:B5 = {10, 20, 30, 40, 50}

Formula:
Select C1:C6 (one more than the number of bins), enter:
=FREQUENCY(A1:A10, B1:B5)
Press Ctrl+Shift+Enter (array formula).

Result:
C1:C6 = {2, 2, 2, 2, 2, 0}
(Counts values ≤10, >10 & ≤20, >20 & ≤30, etc.)

---

Example 2: Frequency Distribution with Text Labels

Data:
A1:A10 = {12, 18, 22, 28, 35, 42, 47, 55, 60, 65}
Bins: B1:B4 = {20, 40, 60}

Formula:
Select C1:C5 , enter:
=FREQUENCY(A1:A10, B1:B4)
Press Ctrl+Shift+Enter.

Result:
C1:C5 = {2, 3, 4, 1}
(Counts values ≤20, >20 & ≤40, >40 & ≤60, >60)

---

Example 3: Frequency Distribution with Decimals

Data:
A1:A10 = {1.2, 2.5, 3.7, 4.1, 5.6, 6.8, 7.3, 8.9, 9.4, 10.0}
Bins: B1:B4 = {3, 6, 9}

Formula:
Select C1:C5 , enter:
=FREQUENCY(A1:A10, B1:B4)
Press Ctrl+Shift+Enter.

Result:
C1:C5 = {2, 3, 3, 2}
(Counts values ≤3, >3 & ≤6, >6 & ≤9, >9)

---

Example 4: Frequency Distribution with Negative Numbers

Data:
A1:A10 = {-5, -3, 0, 2, 4, 6, 8, 10, 12, 15}
Bins: B1:B4 = {0, 5, 10}

Formula:
Select C1:C5 , enter:
=FREQUENCY(A1:A10, B1:B4)
Press Ctrl+Shift+Enter.

Result:
C1:C5 = {3, 3, 3, 1}
(Counts values ≤0, >0 & ≤5, >5 & ≤10, >10)

---

Example 5: Frequency Distribution with Zero Bins

Data:
A1:A10 = {0, 0, 1, 2, 3, 4, 5, 6, 7, 8}
Bins: B1:B3 = {0, 5}

Formula:
Select C1:C4 , enter:
=FREQUENCY(A1:A10, B1:B3)
Press Ctrl+Shift+Enter.

Result:
C1:C4 = {2, 4, 4, 0}
(Counts values ≤0, >0 & ≤5, >5)

---

Example 6: Frequency Distribution with Non-Numeric Data

Data:
A1:A10 = {10, 20, "N/A", 30, 40, 50, 60, 70, 80, 90}
Bins: B1:B4 = {20, 40, 60}

Formula:
Select C1:C5 , enter:
=FREQUENCY(A1:A10, B1:B4)
Press Ctrl+Shift+Enter.

Result:
C1:C5 = {1, 2, 2, 4}
(Ignores non-numeric data and counts numeric values.)

---

Example 7: Frequency Distribution with Empty Bins

Data:
A1:A10 = {5, 10, 15, 20, 25, 30, 35, 40, 45, 50}
Bins: B1:B3 = {10, 30}

Formula:
Select C1:C4 , enter:
=FREQUENCY(A1:A10, B1:B3)
Press Ctrl+Shift+Enter.

Result:
C1:C4 = {2, 3, 5, 0}
(Counts values ≤10, >10 & ≤30, >30)

---

Example 8: Frequency Distribution with Large Data Set

Data:
A1:A100 = Random numbers between 1 and 100
Bins: B1:B10 = {10, 20, 30, 40, 50, 60, 70, 80, 90, 100}

Formula:
Select C1:C11 , enter:
=FREQUENCY(A1:A100, B1:B10)
Press Ctrl+Shift+Enter.

Result:
C1:C11 = Frequency counts for each bin.

---

Example 9: Frequency Distribution with Dynamic Bins

Data:
A1:A20 = Random numbers between 1 and 50
Bins: B1:B5 = {10, 20, 30, 40, 50}

Formula:
Select C1:C6 , enter:
=FREQUENCY(A1:A20, B1:B5)
Press Ctrl+Shift+Enter.

Result:
C1:C6 = Frequency counts for each bin.

---

Example 10: Frequency Distribution with Non-Contiguous Data

Data:
A1:A5 = {5, 10, 15}
A6:A10 = {20, 25, 30, 35, 40}
Bins: B1:B3 = {10, 20, 30}

Formula:
Select C1:C4 , enter:
=FREQUENCY((A1:A5, A6:A10), B1:B3)
Press Ctrl+Shift+Enter.

Result:
C1:C4 = {1, 2, 3, 2}
(Counts values ≤10, >10 & ≤20, >20 & ≤30, >30)

---

Key Notes:

1. Array Formula: Always press Ctrl+Shift+Enter after typing the formula.

2. Output Range: Select one more cell than the number of bins.

3. Non-Numeric Data: FREQUENCY ignores non-numeric values.