find all the possible differences equals to 3

J

Jason

Hi,

I have two column of number, A and B. I would like to find all the
possibility for (# in B)- (#in A) is equal to 3 then return the corresponding
numbers.

e.g

A B
1 3
2 4
5 8
7 13

4-1=3, return 1 & 4
8-5=5, return 5 & 8

Any input would be helpful
Jason
 
T

T. Valko

This seems to do what you want:

Enter this formula in D1 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(ISERROR(SMALL(IF(ISNUMBER(MATCH(A$1:A$4+3,B$1:B$4,0)),ROW(A$1:A$4)-MIN(ROW(A$1:A$4))+1),ROWS($1:1))),"",INDEX(A$1:A$4,SMALL(IF(ISNUMBER(MATCH(A$1:A$4+3,B$1:B$4,0)),ROW(A$1:A$4)-MIN(ROW(A$1:A$4))+1),ROWS($1:1))))

Enter this formula in E1:

=IF(D1="","",D1+3)

Select both D1 and E1 and copy down until you get blanks.

Biff
 
L

Leo Heuser

Jason said:
Hi,

I have two column of number, A and B. I would like to find all the
possibility for (# in B)- (#in A) is equal to 3 then return the
corresponding
numbers.

e.g

A B
1 3
2 4
5 8
7 13

4-1=3, return 1 & 4
8-5=5, return 5 & 8

Any input would be helpful
Jaso
Click to expand...

Hi Jason

Assuming data in A2:B20, and the difference to look for
in C1 (here it is 3), here's one way to do it. The result is
returned as a decimal number. 1 & 4 as 1.4, 5 & 8 as 5.8 etc.

In E2 enter this array formula (E1 must be present and empty,
or at least must not contain data present in B2:B20).

=MIN(IF((COUNTIF($E$1:E1,TRANSPOSE($A$2:$A$20)+($B$2:$B$20)/10^(LEN($B$2:$B$20)))=0)*($B$2:$B$20-TRANSPOSE($A$2:$A$20)=$C$1)*TRANSPOSE($A$2:$A$20<>"")*($B$2:$B$20<>""),TRANSPOSE($A$2:$A$20)+($B$2:$B$20)/10^(LEN($B$2:$B$20))))


The formula must be entered with <Shift><Ctrl><Enter>,
also if edited later.

Copy E2 down with the fill handle (the little square in the lower
right corner of the cell). In case of duplicates only one instance
is displayed.
 
T

T. Valko

That's an interesting approach.

97.....100
7.......10
-2......1
-4......-1

Returns: 97.1, 7.1, -1.9, -4.01 respectively.

My formula does not account for empty cells.

Biff
 
L

Leo Heuser

"T. Valko" <[email protected]> skrev i en meddelelse
That's an interesting approach.

97.....100
7.......10
-2......1
-4......-1

Returns: 97.1, 7.1, -1.9, -4.01 respectively.

My formula does not account for empty cells.

Biff
Click to expand...


You're quite right, Biff. My mistake. Thanks for pointing it out!
Unfortunately, right now I can't see any way around it.

Leo Heuser
 
Top