| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lowest of two closest numbers
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lowest of two closest numbers
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)),IND EX(A1:A19,MATCH(MIN(B2:B20),B2:B20,0))) I did a few tests and it seems to work. But caveat empor! best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lowest of two closest numbers
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. "Bernard Liengme" wrote: 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)),IND EX(A1:A19,MATCH(MIN(B2:B20),B2:B20,0))) I did a few tests and it seems to work. But caveat empor! best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lowest of two closest numbers
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. "Bernard Liengme" wrote: 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)),IND EX(A1:A19,MATCH(MIN(B2:B20),B2:B20,0))) I did a few tests and it seems to work. But caveat empor! best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lowest of two closest numbers
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lowest of two closest numbers
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lowest of two closest numbers
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lowest of two closest numbers
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lowest of two closest numbers
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lowest of two closest numbers
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lowest of two closest numbers
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lowest of two closest numbers
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lowest of two closest numbers
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to display the number between two numbers closest to another value | Excel Worksheet Functions | |||
| How do you add the three lowest numbers in a column? | Excel Worksheet Functions | |||
| find closest match to a reference number in a row of numbers | Excel Discussion (Misc queries) | |||
| Lowest numbers | Excel Discussion (Misc queries) | |||
| Possible to pick out the lowest eight numbers in a row? | Excel Discussion (Misc queries) | |||
Hybrid Mode
