lowest of two closest numbers

Thanks, Biff

Ironic...
If I'd not accounted for blanks and text, somebody would've given me a
newspaper over the snout for that! ...Probably YOU!vbg

***********
Regards,
Ron

XL2003, WinXP


"T. Valko" wrote:

Nice one, Ron!

Here's a slightly shorter version although it doesn't account for empty
cells within the range:

=INDEX(SMALL(A1:A10,ROW(A1:INDEX(A:A,COUNT(A1:A10)-1))),MATCH(MIN(SMALL(A1:A10,ROW(A2:A10))-SMALL(A1:A10,ROW(A1:A9))),SMALL(A1:A10,ROW(A2:A10) )-SMALL(A1:A10,ROW(A1:A9)),0))

--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
If the numbers are not in any particular order....

With A1:A10 containing: {3; 312; 50; 215; 619; 101; 200; 1000; 475; 812}

This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just
ENTER...
-Sorts the range in ascending order
-Calculates the differences between those values
-Returns the smallest value of the pair with the smallest difference
=INDEX(SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT (A1:A10)-1))),MATCH(SMALL(SMALL($A$1:$A$10,ROW($A$1:INDEX($ A:$A,COUNT(A1:A10)-1))+1)-SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))),1),SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUN T(A1:A10)-1))+1)-SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))),0))

Since text wrap will probably impact the display, here's that formula in
sections:
=INDEX(SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT (A1:A10)-1))),
MATCH(SMALL(SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A, COUNT(A1:A10)-1))+1)
-SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))),1),
SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))+1)
-SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))),0))

Using my sample data....
the smallest difference is between 200 and 215.
The formula returns: 200

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Susie (SFAngelgirl)" Susie
wrote in message
...
excel 2003
In comparing a range of numbers; I need to find the two closest numbers
and
have the lowest value returned. Anybody?

Susie - SFAngelgirl

Thanks so much for getting back to us on this. I'm glad you got something
you could work with.


***********
Regards,
Ron

XL2003, WinXP


"Susie (SFAngelgirl)" wrote:

Hello Ron,
Thank you kindly for your answers, yes, everytime I look at it, something
different comes up as well. I do not know anything about Array fromulas so I
decided to go with Bernard's answer, oops, something was not quite right I
got the two lowest numbers, but it returned the higher of the two - I must
have mistyped something. So I tried the first answer you gave me, and so
far so good. Very very grateful, thanks for your time.

"Ron Coderre" wrote:

If the numbers are not in any particular order....

With A1:A10 containing: {3; 312; 50; 215; 619; 101; 200; 1000; 475; 812}

This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just
ENTER...
-Sorts the range in ascending order
-Calculates the differences between those values
-Returns the smallest value of the pair with the smallest difference
=INDEX(SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT (A1:A10)-1))),MATCH(SMALL(SMALL($A$1:$A$10,ROW($A$1:INDEX($ A:$A,COUNT(A1:A10)-1))+1)-SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))),1),SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUN T(A1:A10)-1))+1)-SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))),0))

Since text wrap will probably impact the display, here's that formula in
sections:
=INDEX(SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT (A1:A10)-1))),
MATCH(SMALL(SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A, COUNT(A1:A10)-1))+1)
-SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))),1),
SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))+1)
-SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))),0))

Using my sample data....
the smallest difference is between 200 and 215.
The formula returns: 200

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Susie (SFAngelgirl)" Susie wrote
in message ...
excel 2003
In comparing a range of numbers; I need to find the two closest numbers
and
have the lowest value returned. Anybody?

Susie - SFAngelgirl

No you can get much closer than that you are right, except I never have two
same numbers. what else you got?

"Peo Sjoblom" wrote:

What if 2 numbers are the same, you can't get closer than that?


--


Regards,


Peo Sjoblom



"Susie (SFAngelgirl)" Susie wrote
in message ...
excel 2003
In comparing a range of numbers; I need to find the two closest numbers
and
have the lowest value returned. Anybody?

Susie - SFAngelgirl

hmmmm, no, the first one you came up with is what I need. thanks again

"Ron Coderre" wrote:

Yikes....every time I read this thing I see something different!


If the values in A1:A10 are NOT in ascending order and we want to find the
lower of the two consecutive values with the smallest absolute
difference....

A1:A10 contains: {3; 50; 101; 200; 215; 312; 475; 619; 812; 1000}

Maybe this ARRAY FORMULA:
=MIN(OFFSET(A1,MATCH(SMALL(ABS(A2:A10-A1:A9),1),ABS(A2:A10-A1:A9),0)-{1,0},))

This time the smallest absolute difference is between 101 and 200.
The formula returns: 101

Am I on the right track, yet?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Ron Coderre" wrote in message
...
If the range of number is already in ascending order....

Using the same example:
With A1:A10 containing: {3; 50; 101; 200; 215; 312; 475; 619; 812; 1000}

This ARRAY FORMULA returns the lower of the 2 sequental values with the
smallest difference:
=INDEX(A1:A10,MATCH(SMALL(A2:A10-A1:A9,1),A2:A10-A1:A9,0))
or...
this non-array version:
=INDEX(A1:A10,INDEX(MATCH(SMALL(A2:A10-A1:A9,1),A2:A10-A1:A9,0),0))

Again...
the smallest difference is between 200 and 215
so the formula returns: 200

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Ron Coderre" wrote in message
...
If the numbers are not in any particular order....

With A1:A10 containing: {3; 312; 50; 215; 619; 101; 200; 1000; 475; 812}

This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just
ENTER...
-Sorts the range in ascending order
-Calculates the differences between those values
-Returns the smallest value of the pair with the smallest difference
=INDEX(SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT (A1:A10)-1))),MATCH(SMALL(SMALL($A$1:$A$10,ROW($A$1:INDEX($ A:$A,COUNT(A1:A10)-1))+1)-SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))),1),SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUN T(A1:A10)-1))+1)-SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))),0))

Since text wrap will probably impact the display, here's that formula in
sections:
=INDEX(SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT (A1:A10)-1))),
MATCH(SMALL(SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A, COUNT(A1:A10)-1))+1)
-SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))),1),
SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))+1)
-SMALL($A$1:$A$10,ROW($A$1:INDEX($A:$A,COUNT(A1:A10 )-1))),0))

Using my sample data....
the smallest difference is between 200 and 215.
The formula returns: 200

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Susie (SFAngelgirl)" Susie
wrote in message
...
excel 2003
In comparing a range of numbers; I need to find the two closest numbers
and
have the lowest value returned. Anybody?

Susie - SFAngelgirl