Welcome Guest
Log In | Register )
You last visited January 21, 2017, 2:30 am
All times shown are
Eastern Time (GMT-5:00)

Excel FYI

Topic closed. 33 replies. Last post 11 years ago by hypersoniq.

Page 1 of 3
PrintE-mailLink
JKING's avatar - Kaleidoscope 3.gif

United States
Member #5599
July 13, 2004
1192 Posts
Offline
Posted: November 13, 2005, 9:16 am - IP Logged

If your like me, you create an excel spread sheet, input the data, sort the data in ascending order and then perform various functions on that data.  In the example below, I wanted to know how often a certain value in L1 occurred with a certain value in L2. In this case, how many times did 1-(L1) and 2-(L2) occur together. The answer is as follows:

=SUMPRODUCT(--($A$2:$A$9=G2),--($B$2:$B$9=H2)) Microsoft Excel

 

  a b c d e f g h i
 1 L1 L2 L3 L4 L5  COL L1 COL L2 NO. of Times
 2 2 2 2 3 7  1 2 3
 3 1 2 3 4 6    
 4 1 1 1 4 5    
 5 1 1 4 5 6    
 6 3 3 4 5 6    
 7 1 2 2 3 5    
 8 2 2 3 4 5    
 9 1 2 3 4 5    


 

Using this, you can obvious find the most reoccurring pairs between columns in your data.

Cool!! Hope someone else finds this info as useful to them as it is to me.

You are a slave to the choices you have made.  jk

Even a blind squirrel will occasioanlly find an acorn.

    lottaloot's avatar - AvatarZ56
    Redford/MI
    United States
    Member #3396
    January 18, 2004
    4867 Posts
    Offline
    Posted: November 13, 2005, 2:26 pm - IP Logged

    Thanks for sharing

    I am like a sponge when it comes to new excel formulas

    I can find a use for any formula.  Big Grin

    going to try inserting it into my spreadsheet now. 

    L ttaL   T

      lottaloot's avatar - AvatarZ56
      Redford/MI
      United States
      Member #3396
      January 18, 2004
      4867 Posts
      Offline
      Posted: November 13, 2005, 2:34 pm - IP Logged

      It works wonderfully!  Just incorporated it into my OK pick 3 sheet.

      If you have any other excel FYI--please know that I would appreciate your posts.

      L ttaL   T

        LottoChica23's avatar - Aquarius
        Fughedaboutit (NY)
        United States
        Member #8160
        October 26, 2004
        6777 Posts
        Offline
        Posted: November 14, 2005, 12:15 am - IP Logged

        Thanks for sharing, this formula is very useful! Banana

          bellyache's avatar - 64x64a9wg

          United States
          Member #12618
          March 18, 2005
          2060 Posts
          Offline
          Posted: November 14, 2005, 12:33 am - IP Logged

          That's interesting JKing. Thanks. =)

          Dance like no one is watching.

            CARBOB's avatar - FL LOTTERY_LOGO.png
            ORLANDO, FLORIDA
            United States
            Member #4924
            June 3, 2004
            5972 Posts
            Offline
            Posted: November 15, 2005, 7:43 pm - IP Logged

            If your like me, you create an excel spread sheet, input the data, sort the data in ascending order and then perform various functions on that data.  In the example below, I wanted to know how often a certain value in L1 occurred with a certain value in L2. In this case, how many times did 1-(L1) and 2-(L2) occur together. The answer is as follows:

            =SUMPRODUCT(--($A$2:$A$9=G2),--($B$2:$B$9=H2)) Microsoft Excel

             

              a b c d e f g h i
             1 L1 L2 L3 L4 L5  COL L1 COL L2 NO. of Times
             2 2 2 2 3 7  1 2 3
             3 1 2 3 4 6    
             4 1 1 1 4 5    
             5 1 1 4 5 6    
             6 3 3 4 5 6    
             7 1 2 2 3 5    
             8 2 2 3 4 5    
             9 1 2 3 4 5    


             

            Using this, you can obvious find the most reoccurring pairs between columns in your data.

            Cool!! Hope someone else finds this info as useful to them as it is to me.

            JKing,

              Your formula with a slight variation.  I use the formula below to tell me how many times a digit has followed another digit. The first part of the equation going across, the second part down. Winsum and Sir Metro have both helped me a great deal. Many thanks to them.

            Carbob

             

            =SUMPRODUCT(--($G$21:$G$3062=BS$21),(--($G$22:$G$3063=$BR22)))

             12345
            1110124121130119
            2127111118124114
            3125128112121140
            4121111147107112
            5120121128116135


              retxx's avatar - mrthumbs
              BOSTON
              United States
              Member #48
              September 9, 2001
              3611 Posts
              Offline
              Posted: November 15, 2005, 10:15 pm - IP Logged

              question; in a pick 4 lottery if I entered the first 2 in column L1 and the last 2 in column L2 and did it as far back as the last 10 or 20 draws and entered your formula would i get the same resu lts to see what overdue pairs to play? thanks

                retxx's avatar - mrthumbs
                BOSTON
                United States
                Member #48
                September 9, 2001
                3611 Posts
                Offline
                Posted: November 15, 2005, 10:17 pm - IP Logged

                P.S. can you give an example to me how you would do this formula as I would like to apply it to the pick 4 in Mass.


                  United States
                  Member #17555
                  June 22, 2005
                  5582 Posts
                  Offline
                  Posted: November 16, 2005, 2:20 am - IP Logged

                  What does FYI stand for" thanx.

                    CARBOB's avatar - FL LOTTERY_LOGO.png
                    ORLANDO, FLORIDA
                    United States
                    Member #4924
                    June 3, 2004
                    5972 Posts
                    Offline
                    Posted: November 16, 2005, 5:58 am - IP Logged

                                              For Your Information

                    Carbob

                      lottaloot's avatar - AvatarZ56
                      Redford/MI
                      United States
                      Member #3396
                      January 18, 2004
                      4867 Posts
                      Offline
                      Posted: November 16, 2005, 7:01 am - IP Logged

                      If your like me, you create an excel spread sheet, input the data, sort the data in ascending order and then perform various functions on that data.  In the example below, I wanted to know how often a certain value in L1 occurred with a certain value in L2. In this case, how many times did 1-(L1) and 2-(L2) occur together. The answer is as follows:

                      =SUMPRODUCT(--($A$2:$A$9=G2),--($B$2:$B$9=H2)) Microsoft Excel

                       

                        a b c d e f g h i
                       1 L1 L2 L3 L4 L5  COL L1 COL L2 NO. of Times
                       2 2 2 2 3 7  1 2 3
                       3 1 2 3 4 6    
                       4 1 1 1 4 5    
                       5 1 1 4 5 6    
                       6 3 3 4 5 6    
                       7 1 2 2 3 5    
                       8 2 2 3 4 5    
                       9 1 2 3 4 5    


                       

                      Using this, you can obvious find the most reoccurring pairs between columns in your data.

                      Cool!! Hope someone else finds this info as useful to them as it is to me.

                      JKing,

                        Your formula with a slight variation.  I use the formula below to tell me how many times a digit has followed another digit. The first part of the equation going across, the second part down. Winsum and Sir Metro have both helped me a great deal. Many thanks to them.

                      Carbob

                       

                      =SUMPRODUCT(--($G$21:$G$3062=BS$21),(--($G$22:$G$3063=$BR22)))

                       12345
                      1110124121130119
                      2127111118124114
                      3125128112121140
                      4121111147107112
                      5120121128116135


                      This is getting better & better by the day.

                      L ttaL   T


                        United States
                        Member #17555
                        June 22, 2005
                        5582 Posts
                        Offline
                        Posted: November 16, 2005, 9:32 am - IP Logged

                        Thanx Carob.

                          JKING's avatar - Kaleidoscope 3.gif

                          United States
                          Member #5599
                          July 13, 2004
                          1192 Posts
                          Offline
                          Posted: November 16, 2005, 11:45 am - IP Logged

                          Is anyone familiar with what the"--" is?

                          =SUMPRODUCT(--($A$2:$A$9=G2),--($B$2:$B$9=H2))

                          You are a slave to the choices you have made.  jk

                          Even a blind squirrel will occasioanlly find an acorn.

                            CARBOB's avatar - FL LOTTERY_LOGO.png
                            ORLANDO, FLORIDA
                            United States
                            Member #4924
                            June 3, 2004
                            5972 Posts
                            Offline
                            Posted: November 17, 2005, 11:02 am - IP Logged

                            Is anyone familiar with what the"--" is?

                            =SUMPRODUCT(--($A$2:$A$9=G2),--($B$2:$B$9=H2))

                            JKing,

                              Below is your answer. I asked the "old reliable boys" at Mrexcel.com, since I didn't know either. Now we both will have learned something.

                            Carbob

                             

                             

                            ANSWER........

                            It's called a double unary minus. Minus minus is the same as plus.
                            It coerces a TRUE/FALSE boolean into 1/0. The same could be achieved
                            by multiplying by 1 or adding zero. But -- is in vogue at the moment because
                            apparently it is a nanosecond or two quicker than the alternatives.

                            It would more likely be written:

                            =SUMPRODUCT(($B$2:$B$8=F2)*($D$2:$D$8="X"))

                            It compares each cell in B2:B8 with F2 and returns an array of TRUE/FALSE values.
                            Then it compares each cell in D2:D8 with "X" and returns another (equally sized) array
                            of TRUE/FALSE values. The 2 arrays are multiplied together, and in the process TRUE is
                            coerced to 1 and FALSE to zero. That results in an array of 1/0 values
                            (1*1=1, 1*0=0, 0*1=0, 0*0=0). This array is then summed. The effect is a
                            count of the two conditions being TRUE.

                              JKING's avatar - Kaleidoscope 3.gif

                              United States
                              Member #5599
                              July 13, 2004
                              1192 Posts
                              Offline
                              Posted: November 18, 2005, 8:32 am - IP Logged

                              Carbob...Thanks.  A well written and informative answer.

                              You are a slave to the choices you have made.  jk

                              Even a blind squirrel will occasioanlly find an acorn.