find all the possible differences equals to 3

[Guest]

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

 

[Leo Heuser]

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

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

 

[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

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

 

[T. Valko]

When I filter this set of data, the function can locate

5 and 9 but can not locate 5409 and 5413.

It works for me. See this screencap:

http://img265.imageshack.us/img265/4973/findnumbers8zc.jpg

I suspect you didn't modify the formula correctly for the different data
set.

Biff

 

[T. Valko]

P.S......

If you want the comparison value to be variable, instead of hardcoding it
into the formula use a cell to hold that variable and then just refer to
that cell.

Biff