find all the possible differences equals to 3


G

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
 
Ad

Advertisements

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

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
 
Ad

Advertisements

T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top