Welcome Guest
Log In | Register )
You last visited December 8, 2016, 3:03 pm
All times shown are
Eastern Time (GMT-5:00)

What excel array formula...

8 replies. Last post 21 days ago by Sunglasses.

Page 1 of 1
51
PrintE-mailLink
adulane62's avatar - file php?avatar=16228.gif
From Denver, Rocky Mountain Empire,
United States
Member #49750
February 13, 2007
439 Posts
Offline
Posted: November 17, 2016, 1:55 am - IP Logged

...should I use to see if any of the numbers from the latest draw matched from the previous (i.e., preceding) five draws? I know there is countif, index or match involved, however in what order? Thanks!

Go Broncos!  White Bounce

    winsumloosesum's avatar - Lottery-060.jpg
    Pennsylvania
    United States
    Member #2218
    September 1, 2003
    5387 Posts
    Offline
    Posted: November 17, 2016, 4:02 am - IP Logged

    My 5 most recent draws for the PA530 Treasure Hunt Game are always entered on line 20 (E20:I20)

    So if I wanted to Sum how many numbers repeated after the current drawing on line 20 I would use the following formula:

    The formula below will count how many numbers repeated from the next 5 drawings.

    After you enter the formula below use control +shift+enter (Ctrl-Shift-Enter)

    You should see the brackets { } at each end of the formula

    =SUM(IF(COUNTIF(E21:I25,E20:I20)>0,1,0))

      winsumloosesum's avatar - Lottery-060.jpg
      Pennsylvania
      United States
      Member #2218
      September 1, 2003
      5387 Posts
      Offline
      Posted: November 17, 2016, 4:24 am - IP Logged

      If you want to calculate how many numbers repeated from the previous drawing:

      My 5/30 game entries start on line 20.  My cash 5 draws are entered E20:I20

      This formula is just for the previous drawing only

      =MIN(IF(COUNTIF(E20:I20,E21)>0,1)+IF(COUNTIF(E20:I20,F21)>0,1)+IF(COUNTIF(E20:I20,G21)>0,1)+IF(COUNTIF(E20:I20,H21)>0,1)+IF(COUNTIF(E20:I20,I21)>0,1),IF(COUNTIF(E21:I21,E20)>0,1)+IF(COUNTIF(E21:I21,F20)>0,1)+IF(COUNTIF(E21:I21,G20)>0,1)+IF(COUNTIF(E21:I21,H20)>0,1)+IF(COUNTIF(G21:I21,I20)>0,1))

        winsumloosesum's avatar - Lottery-060.jpg
        Pennsylvania
        United States
        Member #2218
        September 1, 2003
        5387 Posts
        Offline
        Posted: November 17, 2016, 4:33 am - IP Logged

        For Pick 3 and numbers games

        Where my winning number for Pick 3 start on line 20 in cell range G20:I20.  Previous/older draws are below line 20

        This formula will count how many digits repeated from the previous drawing.  Since Pick 3 game can have repeating digits (doubles & triples) use the formula below

        =SUMPRODUCT(IF(COUNTIF($G20:$I20,{0,1,2,3,4,5,6,7,8,9})<COUNTIF($G21:$I21,{0,1,2,3,4,5,6,7,8,9}),COUNTIF($G20:$I20,{0,1,2,3,4,5,6,7,8,9}),COUNTIF($G21:$I21,{0,1,2,3,4,5,6,7,8,9})))

        Adjust the formula for Pick 4 games by changing just the cell range from G20:I20 (Pick 3) to G20:J20 (Pick 4)

          Sunglasses's avatar - nicebear
          Zaperlopopotam
          Belgium
          Member #173932
          March 26, 2016
          961 Posts
          Offline
          Posted: November 17, 2016, 7:24 am - IP Logged
          ABC0123456789T
          312-----------
          32200110000002

          Matrix

          .
            adulane62's avatar - file php?avatar=16228.gif
            From Denver, Rocky Mountain Empire,
            United States
            Member #49750
            February 13, 2007
            439 Posts
            Offline
            Posted: November 17, 2016, 10:29 am - IP Logged

            Thanks, Winsum! The first one is what i was looking for.

            Go Broncos!  White Bounce

              Avatar
              bgonçalves
              Brasil
              Member #92564
              June 9, 2010
              2123 Posts
              Offline
              Posted: November 17, 2016, 11:45 am - IP Logged

              hello sumglass ex= pick4

              0123

              0235

              1245

                Sunglasses's avatar - nicebear
                Zaperlopopotam
                Belgium
                Member #173932
                March 26, 2016
                961 Posts
                Offline
                Posted: November 17, 2016, 1:50 pm - IP Logged

                hello sumglass ex= pick4

                0123

                0235

                1245

                A 0123

                B 0235

                C 1245

                = A Intersect  B Intersect  C

                .
                  Sunglasses's avatar - nicebear
                  Zaperlopopotam
                  Belgium
                  Member #173932
                  March 26, 2016
                  961 Posts
                  Offline
                  Posted: November 17, 2016, 2:02 pm - IP Logged
                  A12340123456789TD
                  012301231111000000--
                  023502351011010000--
                  124512450110110000--
                  -----001000000012
                  .