3
$\begingroup$

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.$$

$\endgroup$
3
  • 1
    $\begingroup$ Does "not more than 10% different" mean that max<=1.1min, or min>=0.9max, or (max-min)<=0.1(max+min)/2? $\endgroup$ – Stephan Kolassa Nov 21 '14 at 11:39
  • $\begingroup$ Sorry, I should have been clearer. I mean the latter (Max-Min)<=0.1(Max+Min)/2 $\endgroup$ – Nat Aes Nov 21 '14 at 11:44
  • $\begingroup$ Please insert the formula you presented in the comment into the main body of the question. It will make the question clear (and people don't always read through comments). $\endgroup$ – Alecos Papadopoulos Nov 21 '14 at 12:23
2
$\begingroup$

Problem statement

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

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

Using $2\lambda$ 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.

Spreadsheet

This is what the formulas look like:

Formulas

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.)

$\endgroup$
5
  • $\begingroup$ Many thanks for this thorough answer. It's really helpful, although I have a couple of follow up questions: (1) Can a similar methodology be employed for an unsorted dataset? My dataset in unsorted and it won't be simple to sort. (2) Perhaps I'm missing something, but the end result in the example is a dataset between 62-75 ie numbers that are more than 10% apart. $\endgroup$ – Nat Aes Nov 24 '14 at 12:31
  • $\begingroup$ Because the illustration has $\lambda=0.10$, the relative range of values will be $2\lambda=20\%$. To obtain a $10\%$ range, fill the "Lambda" cell with the value $0.05$ rather than $0.10$. ou can always sort data in Excel, but if you choose not to do that you can accomplish the same thing by means of the RANK function and its relatives or by careful application of COUNTIF. To count the number of data values between two limits $l\le u$, let $n_u$ count those less than or equal to $u$ and $n_l$ count those greater than or equal to $l$; the number $n_u+n_l-n$ is what you want. $\endgroup$ – whuber Nov 24 '14 at 15:28
  • $\begingroup$ Thanks, the 0.05 lambda works. I'm not sure what you mean by using the RANK function of careful application of COUNTIF. Would you mind please elaborating on how I would do this? $\endgroup$ – Nat Aes Nov 24 '14 at 16:20
  • $\begingroup$ Just emulate the example in the answer: it shows how to count values below; the modification to count values above is straightforward. $\endgroup$ – whuber Nov 24 '14 at 16:22
  • $\begingroup$ Sorry, I'm not as proficient in this as I would like to be. Would I simply amend the Count Below formula to COUNTIF($A$2:$A$100, "<=" & B2)? $\endgroup$ – Nat Aes Nov 24 '14 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.