# Solved – Range of Most Common Values

I have a few numbers such as:

24
65
62
63
39
52
88
21
65
65
62
75

Using Excel, I am looking to identify a range of numbers whose maximum and minimum are not more than 10% different from one another, and subsequently find the range that contains the most numbers (i.e. the modal range).

The 10% difference would be defined by \$\$(max-min) le 0.1(max+min)/2.\$\$

Contents

### Problem statement

Let the \$n\$ numbers be sorted so they can be written

\$\$x_1 le x_2 le cdots le x_n.\$\$

Using \$2lambda\$ as a general name for the 10% value, we seek an interval of the form \$[x_t, x_{t+k}]\$ where

\$\$x_{t+k} – x_t le lambda left(x_t + x_{t+k}right)\$\$

and \$k\$ is as large as possible.

### Solution

That criterion is algebraically equivalent to

\$\$x_{t+k} le frac{1+lambda}{1-lambda}x_t.\$\$

Thus, all one has to do is compute the multiples of the data \$mu x_t\$ for \$mu = frac{1+lambda}{1-lambda}\$ and count how many lie within each interval of the form \$[x_t, mu x_t]\$.

### Excel implementation

Arrange the data in a column and sort them in ascending order. To illustrate, I put them in column `A` beginning at the second row.

In a parallel column (such as column `B`), multiply the values by \$mu\$.

In another parallel column, count the intervals using `COUNTIF`. The expressions in the example look like

``=COUNTIF(A2:A\$100, "<=" & B2) =COUNTIF(A3:A\$100, "<=" & B3) ... =COUNTIF(A13:A\$100, "<=" & B13) ``

Find the largest value(s) in this column: they are next to the desired intervals. This is what the formulas look like: `Data` is the range `A2:A13` containing the sorted values. `Indicator` is the range under the heading "Mode"; its non-blank values show where the modal intervals begin. `Idx` (short for "Index", which is a reserved word for Excel), `Lambda`, and `Mu` are cells just to the right of the corresponding names.

(I apologize that the illustrated value for \$lambda\$ is twice that requested in the question.)

Rate this post