Welcome Guest
Log In | Register )
The time is now 9:10 am
You last visited July 26, 2014, 9:09 am
All times shown are
Eastern Time (GMT-5:00)

Finding Pairs with Excel

Topic closed. 1 reply. Last post 8 years ago by Thoth.

Page 1 of 1
PrintE-mailLink
Avatar
New Member
Vancouver
Canada
Member #32657
February 14, 2006
2 Posts
Offline
Posted: November 23, 2006, 8:55 pm - IP Logged

Hey Guys.

I'm trying to find a way of finding pairs within a data series using Excel.

What I have in mind is this.  The data is in each column:

        1            2  3  4
--------------------------------------------------------------------------------
A      11/01/06  2  4  8
B      11/02/06  6  4  2
C      11/03/06  1  2  4
D      11/04/06  7  7  7 


It should output something like  2-4 shows up 3 times.

I tried using COUNTIF and a series of other array formulas but to no real avail.

Anyone ever tried to do this or have an ideas how to go about this.

Thanks in advance.

    Thoth's avatar - binary
    Findlay, Ohio
    United States
    Member #4855
    May 28, 2004
    399 Posts
    Offline
    Posted: November 23, 2006, 10:11 pm - IP Logged

    You might consider adding a four more columns to your spreadsheet...a seperate column for each of the three boxed pairs and the fourth to perform the counts.

    Do you have the digits separated by position?  If so, lets assume that for the first combo you have listed (248) that the 2 is in column C and 4 is in column D and the 8 is in column E....all in the second row.  Use the first row as column labels, like "date" "P1", "P2", "P3".

    In column F you can enter: =SMALL(C2:E2,1)&SMALL(C2:E2,2)  this will give you the "small pair" 24.  You can label this column "Small" in cell F1 

    In column G you can enter =SMALL(C2:E2,1)&SMALL(C2:E2,3)  this will give you the medium pair 28. You can label this column "Med" in cell G1 

    In column H you can enter =SMALL(C2:E2,2)&SMALL(C2:E2,3)  this will give you the large pair 48. You can label this column "Large" in cell H1 

    You only have to enter these formulas in the first three cells below your column labels because you can select them and fill down to your most recent drawing entry.

    Now, column I will be used to perform the count.  Instead of having a label in cell I1, enter "24" into the cell.

    In cell I2 enter =IF(OR(VALUE(F2)=$I$1,VALUE(G2)=$I$1,VALUE(H2)=$I$1),1,"") Be sure to fill this formula all the way down to the very last draw you have entered.  This searches the three boxed pairs for each drawing for the pair this is listed in cell I1.

    In cell J1 enter =COUNTIF(I:I,1) This is the total count that the pair listed in I1 has hit in your list of drawings...without counting it twice within double!  For example, the three boxed pairs for 272 straight will be listed as 22, 27 and 27.  Only one of the 27's will be counted in the total.   

    Since the three boxed pairs for each drawing will always be listed from the lowest to highest digit, you will only need to search the 45 no-match pairs and the 10 doubles.  For example, when counting the pair hits you will never have the pair 21, it will always be listed as 12 instead.  Enter any of the 45 no-match boxed pairs in low to high order in cell I1 and its count should appear to the immediate right in cell J1.  You can enter any double pairs as well.

    There are other ways this can be done, but this one is pretty easy to set up. 

    ~Probability=Odds in Motion~