CODEWITHSUNDEEP
excelexcel-formula
user3822366
user3822366

Reputation: 21

Excel - Find the biggest gap in a set of numbers?

I have a series of numbers
0,1,99,5,5,98,9
They are unsorted and will remain that way.
I cannot use macros.
I want the answer 89 from a formula or an array formula.
89 is the biggest gap (between 9 and 98) in this series when sorted.
I want a formula, no vba, and no sorting my column or row.

I need a formula that sorts the list and subtracts one cell relative to the sorted list and gives the largest difference of the list of differences it creates.

so the list becomes 0,1,5,5,9,98,99
subtracts the current from the previous (na,1,4,0,4,89,1)
and gives me the max 89.

My list is a column of 7 rows.

Upvotes: 0

Views: 8715

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

This formula must be array-entered. In the formula RNG refers to the range where you have entered your numbers, e.g. A1:A7

=MAX(LARGE(RNG,ROW(INDIRECT("1:"&-1+COUNT(RNG))))-
LARGE(RNG,ROW(INDIRECT("2:"&COUNT(RNG)))))

To array-enter a formula, after entering the formula into the cell or formula bar, hold down ctrl-shift while hitting enter. If you did this correctly, Excel will place braces {...} around the formula.

You can see how the formula works by using the Evaluate Formula option on the Formula Auditing tab of the Formulas ribbon.

In brief, the formula works by creating two arrays, sorted in order of size. The "K" value of the LARGE function is an array created by the ROW(INDIRECT sequence. The first returns 

{1;2;3;4;5;6}

and the second returns

{2;3;4;5;6;7}

The two arrays of values returned would then be:

{99;98;9;5;5;1}
{98;9;5;5;1;0}

Subtracting one from the other results an array of the differences, and we find the MAX.

Upvotes: 4

Gary's Student
Gary's Student

Reputation: 96753

Place the values in A1 thru A7 in any order!

In B1 enter:

=RANK(A1,$A$1:$A$7,0)+COUNTIF($A$1:$A1,A1)-1

and copy down thru B7

In C1 enter:

=INDEX($A$1:$A$7,MATCH(ROW(),B$1:B$7,0))

and copy down thru C7

In D2 enter:

=C1-C2

and copy down thru C7

Finally in E1 enter:

=MAX(C:C)

Column B represents the order of the values in column A if they were sorted. Column C contains the values of column A in sorted order. Column D are the differences and E1 gives the desired answer. Here is an example:

demo

Upvotes: 1

Hugh
Hugh

Reputation: 16090

MAX(A:A) - LARGE(A:A,2) gives the difference between the largest and second-largest value if your numbers are in column A. Don't put this formula in column A.

Upvotes: 1

Related Questions