| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find all the possible differences equals to 3
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find all the possible differences equals to 3
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 "Jason" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find all the possible differences equals to 3
"Jason" skrev i en meddelelse
... 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. -- Best regards Leo Heuser Followup to newsgroup only please. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find all the possible differences equals to 3
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" wrote in message ... "Jason" skrev i en meddelelse ... 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. -- Best regards Leo Heuser Followup to newsgroup only please. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find all the possible differences equals to 3
"Leo Heuser" wrote in message ... "Jason" skrev i en meddelelse ... 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. -- Best regards Leo Heuser Followup to newsgroup only please. "T. Valko" 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find all the possible differences equals to 3
Hi,
Thanks for the input, I used your way to filter my data, but instead of 3, I filtered for 4. The function you provided works well on small number but does not work on big numbers. For example 5 9 3018 1423 4107 5025 4306 5413 5409 13199 15027 19841 18823 25813 23404 25948 27633 26083 42219 42343 42829 When I filter this set of data, the function can locate 5 and 9 but can not locate 5409 and 5413. All my numbers are whole number and none negative. number in both A and B are both listed from smallest to biggest. Total Entry of A can be smaller, bigger or equal to B. Is there a way I can solve this problem? "T. Valko" wrote: 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 "Jason" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find all the possible differences equals to 3
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/4...numbers8zc.jpg I suspect you didn't modify the formula correctly for the different data set. Biff "Jason" wrote in message ... Hi, Thanks for the input, I used your way to filter my data, but instead of 3, I filtered for 4. The function you provided works well on small number but does not work on big numbers. For example 5 9 3018 1423 4107 5025 4306 5413 5409 13199 15027 19841 18823 25813 23404 25948 27633 26083 42219 42343 42829 When I filter this set of data, the function can locate 5 and 9 but can not locate 5409 and 5413. All my numbers are whole number and none negative. number in both A and B are both listed from smallest to biggest. Total Entry of A can be smaller, bigger or equal to B. Is there a way I can solve this problem? "T. Valko" wrote: 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 "Jason" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find all the possible differences equals to 3
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 "T. Valko" wrote in message ... 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/4...numbers8zc.jpg I suspect you didn't modify the formula correctly for the different data set. Biff "Jason" wrote in message ... Hi, Thanks for the input, I used your way to filter my data, but instead of 3, I filtered for 4. The function you provided works well on small number but does not work on big numbers. For example 5 9 3018 1423 4107 5025 4306 5413 5409 13199 15027 19841 18823 25813 23404 25948 27633 26083 42219 42343 42829 When I filter this set of data, the function can locate 5 and 9 but can not locate 5409 and 5413. All my numbers are whole number and none negative. number in both A and B are both listed from smallest to biggest. Total Entry of A can be smaller, bigger or equal to B. Is there a way I can solve this problem? "T. Valko" wrote: 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 "Jason" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I find the differences in two spreadsheets | Excel Discussion (Misc queries) | |||
| Find and Replace | Excel Worksheet Functions | |||
| Code needed to find records from bottom up | Excel Discussion (Misc queries) | |||
| How do I find differences between two excel worksheets? | New Users to Excel | |||
| Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) | |||
Linear Mode
