lowest of two closest numbers

[Guest]

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

 

[Bernard Liengme]

Here is my offering; I am looking forward to T Valko's - he is a wiz at this
sort of thing. I hope by range you meant a single column (if not, delete my
message)

I will use numbers in A1:A20
In B2 enter =ABS(A1-A2); copy down to B20
In cell were you want the smallest number of the pair with minimum
difference enter
=MIN(INDEX(A2:A20,MATCH(MIN(B2:B20),B2:B20,0)),INDEX(A1:A19,MATCH(MIN(B2:B20),B2:B20,0)))
I did a few tests and it seems to work. But caveat empor!
best wishes

 

[Peo Sjoblom]

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


--


Regards,


Peo Sjoblom

 

[Ron Coderre]

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,COUNT(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)

 

[Ron Coderre]

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]

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)

 

[Guest]

Hi Bernard,

Thank you so much, I gave it a try and it worked out great - what has
consumed a lot of time is now instantaneous.

 

[T. Valko]

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

 

[Guest]

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.

 

[Guest]

Hi Bernard,
Me again, I must have mistyped something wrong, so I tried Ron's first
answer and it work just as well. I am learning from all of you, thanks so
much.

 

[Guest]

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

 

[Guest]

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


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

XL2003, WinXP

 

[Guest]

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

 

[Guest]

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