Histogram Binning: Why Excel “Misses” data points in the Frequency function.

When Excel’s FREQUENCY function appears to miss data points, the problem is rarely a calculation bug. Almost always, it stems from how Excel evaluates bin boundaries: FREQUENCY uses an upper-inclusive (less than or equal to) rule, not a lower-inclusive one. Additionally, FREQUENCY always returns one more value than the number of bins provided to capture overflow. In older versions of Excel, failing to enter the formula as a multi-cell array across $N+1$ rows hides those overflow counts entirely.

Quick Answer

FREQUENCY(data_array, bins_array) groups values where each bin captures numbers greater than the previous bin and less than or equal to the current bin ($(\text{bin}_{k-1}, \text{bin}_k]$). If values match the maximum bin or exceed it, they fall into an extra overflow bin. Always ensure your output range has one more cell than your bins_array, verify your bins are sorted, and check that source numbers are not stored as text.

---

The Mathematical Rules of Excel Binning

To understand where missing numbers go, you must understand how FREQUENCY sorts inputs into bins:

Bin Array:    [ 10,  20,  30 ]

Bucket 1:     Values <= 10
Bucket 2:     Values > 10 and <= 20
Bucket 3:     Values > 20 and <= 30
Bucket 4:     Values > 30               <-- The "Hidden" Overflow Bucket

Each bin captures data using the condition:

$\text{Previous Bin} < x \le \text{Current Bin}$

If a user expects a bin labeled 10-20 to mean $10 \le x < 20$, any value exactly equal to $20$ will be placed in that bin, while a value equal to $10$ is pushed down into the <= 10 bin. This boundary logic often makes analysts think Excel skipped their minimum values.

---

The Top 4 Causes of “Missing” Data Points

When the sum of your frequency buckets does not equal =COUNT(data_array), work through these four diagnostic checkpoints.

[FREQUENCY Sum != Total Data Count]
          │
          ├──> Is the output range missing the final overflow cell?
          │         └──> Modern Excel: Formula spills automatically.
          │         └──> Legacy Excel: Re-enter with Ctrl+Shift+Enter across N+1 cells.
          │
          ├──> Are numeric values stored as text?
          │         └──> Test with ISNUMBER. Convert text numbers to real values.
          │
          ├──> Floating-point precision error at bin boundary?
          │         └──> e.g., 20.0000000000001 falls into >20 bin instead of <=20.
          │
          └──> Are your bins unsorted?
                    └──> FREQUENCY requires bins in strictly ascending order.

1. The Truncated N+1 Overflow Array (Legacy Excel Trap)

The FREQUENCY function returns a vertical array with a length equal to:

$\text{Rows in Output} = \text{ROWS}(\text{bins\_array}) + 1$

The final cell contains the count of all values greater than the largest bin value.

• In Microsoft 365 and Excel 2021+: The formula spills dynamically. If you supply 4 bins, it automatically spills 5 rows.
• In Excel 2019 and older: You must manually highlight the output range before typing the formula. If you provide a 4-bin range (D2:D5) and only select 4 cells for the formula output (E2:E5), Excel silently drops the 5th overflow row. Any data point greater than the maximum bin value disappears from your report.

The Fix: In legacy Excel, select $N+1$ vertical cells, press F2 to edit the formula bar, and press Ctrl + Shift + Enter.

2. Numbers Stored as Text or Empty Strings

FREQUENCY strictly ignores text labels, empty strings returned by formulas (such as ""), and numbers formatted as text.

If an ERP export formats the number 15 as a text string '15, FREQUENCY skips it entirely without returning an error.

Run this verification check next to your raw data in column A:

=SUMPRODUCT(--NOT(ISNUMBER(A2:A100)))

If this formula returns any value greater than 0, your column contains non-numeric entries. Use VALUE() or the Data > Text to Columns wizard to convert text-stored numbers into true numeric values.

3. Floating-Point Precision Mismatches

Excel stores floating-point numbers according to IEEE 754 standards. Calculations that should evaluate to a clean integer (like 10.0) can carry minute binary tails (such as 10.000000000000002).

If your bin maximum is set to 10, and a calculated data point equals 10.000000000000002:

• Because 10.000000000000002 > 10, the value is pushed into the next higher bin.
• Visually, the cell displays 10.00, giving the false impression that Excel failed to count a value that met the bin boundary.

The Fix: Wrap upstream calculated data in the ROUND function:

=ROUND(SourceFormula, 4)

4. Unsorted Bin Arrays

bins_array must be sorted in strictly ascending order (e.g., 10, 20, 30, 40).

If your bins are unsorted (e.g., 30, 10, 40, 20), FREQUENCY evaluates the cutoff points in the physical order they appear in the sheet. Values will be bucketed based on the internal order of your references, producing nonsensical distribution curves that look like random omissions.

---

Step-by-Step Diagnostic Verification

To guarantee that every record in your dataset is accounted for, build this self-auditing binning table:

       A              B              C
1   Raw Data     Bin Ceiling     Frequency
2      4             10          =FREQUENCY(A2:A10, B2:B4)
3     10             20          ...
4     15             30          ...
5     22                         (Spilled Overflow Cell)
6     35

1. Verify Total Item Count:
   In an adjacent cell, check the sum of the spilled frequency array against the raw numeric count: =SUM(C2#) = COUNT(A2:A10)
2. If TRUE: All data points are categorized. If your intermediate bins appear empty, your bin thresholds need adjustment to match the data’s range.
3. If FALSE: Non-numeric cells, blanks, or unhandled errors are present in column A.

To troubleshoot general array behavior and dynamic range outputs, refer to Troubleshooting #SPILL! and #CALC! Errors: The Dynamic Array Diagnostic Guide. For setting up valid data bounds across broader statistical models, consult Excel for Science & Engineering: Diagnostic Maps for Stats, Matrices, and Solvers.