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

Excel 2010 Help

Topic closed. 7 replies. Last post 3 years ago by SergeM.

Page 1 of 1
PrintE-mailLink
Avatar

United States
Member #141571
April 18, 2013
58 Posts
Offline
Posted: February 22, 2014, 12:10 pm - IP Logged

Need to know the correct function/formula/statement, ( I do not know the proper name of what it's
called )to enter into a Excel 2010.

That will count the amount of times of one or more matching numbers that may have been drawn with in
each past game of 20 drawn numbers.


Example: Numbers played (23,2,34,21,11)

Check with in a very large list of over 2000 past draws, each game has 20 numbers, each number in a
separate cell. The past draws are NOT sorted or have duplicated numbers.

PAST DRAWN NUMBERS: |23|4|63|51|31|10|19|2|66|45|7|3|27|1|14|21|39|16|29|2| >RESULT> Hit 3 numbers


PAST DRAWN NUMBERS: |23|4|63|51|31|10|19|2|66|45|7|3|27|1|14|21|39|16|29|18| >RESULT> Hit 2 numbers


PAST DRAWN NUMBERS: |22|4|63|51|31|10|19|2|66|45|7|3|27|1|14|28|39|16|29|3| >RESULT> Hit 0 numbers

Surly this is very easy for excel people, but not for me at this time.

Any help is greatly appreciated.... Thank you

    Avatar

    United States
    Member #123200
    February 15, 2012
    53 Posts
    Offline
    Posted: February 22, 2014, 1:15 pm - IP Logged

    Your "Hit" counts were wrong.  Anyhow here it is: Past Draws in Columns B2:U2, B3:U3 and B3:U4, #'s Played in Cells W2:AA2

    Past Draws
    2346351311019266457327114213916292
    23463513110192664573271142139162918
    2246351311019266457327114283916293
    #'s Played
    232342111
    Hits
    4=COUNTIF(B2:U2,W$2)+COUNTIF(B2:U2,X$2)+COUNTIF(B2:U2,Y$2)+COUNTIF(B2:U2,Z$2)+COUNTIF(B2:U2,AA$2)
    3=COUNTIF(B3:U3,W$2)+COUNTIF(B3:U3,X$2)+COUNTIF(B3:U3,Y$2)+COUNTIF(B3:U3,Z$2)+COUNTIF(B3:U3,AA$2)
    1=COUNTIF(B4:U4,W$2)+COUNTIF(B4:U4,X$2)+COUNTIF(B4:U4,Y$2)+COUNTIF(B4:U4,Z$2)+COUNTIF(B4:U4,AA$2)
      Avatar

      United States
      Member #141571
      April 18, 2013
      58 Posts
      Offline
      Posted: February 23, 2014, 5:28 am - IP Logged

      Your "Hit" counts were wrong.  Anyhow here it is: Past Draws in Columns B2:U2, B3:U3 and B3:U4, #'s Played in Cells W2:AA2

      Past Draws
      2346351311019266457327114213916292
      23463513110192664573271142139162918
      2246351311019266457327114283916293
      #'s Played
      232342111
      Hits
      4=COUNTIF(B2:U2,W$2)+COUNTIF(B2:U2,X$2)+COUNTIF(B2:U2,Y$2)+COUNTIF(B2:U2,Z$2)+COUNTIF(B2:U2,AA$2)
      3=COUNTIF(B3:U3,W$2)+COUNTIF(B3:U3,X$2)+COUNTIF(B3:U3,Y$2)+COUNTIF(B3:U3,Z$2)+COUNTIF(B3:U3,AA$2)
      1=COUNTIF(B4:U4,W$2)+COUNTIF(B4:U4,X$2)+COUNTIF(B4:U4,Y$2)+COUNTIF(B4:U4,Z$2)+COUNTIF(B4:U4,AA$2)

      Great, that did it , thank you very much I'm sure that saved me a huge amount of time.

      Cheech

        Avatar
        NASHVILLE, TENN
        United States
        Member #33372
        February 20, 2006
        1044 Posts
        Offline
        Posted: February 23, 2014, 7:39 am - IP Logged

        Here is how I do it which means you should look for other ways.

        I highlight the area in which I want to know "how many numbers?"  I then click on the "name manager" button and give this highlighted area a throw-a-way name, such as "temp1".

        I click on the cell which will contain the data I am seeking.  Using the countif function, I enter this code, " = countif(temp1,23)".  Excel will then tell me how many times 23 came up within the area I so named.

          SergeM's avatar - slow icon.png
          Economy class
          Belgium
          Member #123700
          February 27, 2012
          4035 Posts
          Offline
          Posted: February 23, 2014, 7:48 am - IP Logged

          Need to know the correct function/formula/statement, ( I do not know the proper name of what it's
          called )to enter into a Excel 2010.

          That will count the amount of times of one or more matching numbers that may have been drawn with in
          each past game of 20 drawn numbers.


          Example: Numbers played (23,2,34,21,11)

          Check with in a very large list of over 2000 past draws, each game has 20 numbers, each number in a
          separate cell. The past draws are NOT sorted or have duplicated numbers.

          PAST DRAWN NUMBERS: |23|4|63|51|31|10|19|2|66|45|7|3|27|1|14|21|39|16|29|2| >RESULT> Hit 3 numbers


          PAST DRAWN NUMBERS: |23|4|63|51|31|10|19|2|66|45|7|3|27|1|14|21|39|16|29|18| >RESULT> Hit 2 numbers


          PAST DRAWN NUMBERS: |22|4|63|51|31|10|19|2|66|45|7|3|27|1|14|28|39|16|29|3| >RESULT> Hit 0 numbers

          Surly this is very easy for excel people, but not for me at this time.

          Any help is greatly appreciated.... Thank you

          PAST DRAWN NUMBERS: |23|4|63|51|31|10|19|2|66|45|7|3|27|1|14|21|39|16|29|2| >RESULT> Hit 3 numbers

            RJOh's avatar - chipmunk
            mid-Ohio
            United States
            Member #9
            March 24, 2001
            19904 Posts
            Offline
            Posted: February 23, 2014, 4:29 pm - IP Logged

            Here is how I do it which means you should look for other ways.

            I highlight the area in which I want to know "how many numbers?"  I then click on the "name manager" button and give this highlighted area a throw-a-way name, such as "temp1".

            I click on the cell which will contain the data I am seeking.  Using the countif function, I enter this code, " = countif(temp1,23)".  Excel will then tell me how many times 23 came up within the area I so named.

            LP has charts that break down hits in a similar fashion and can be used to recheck your results.

             * you don't need to buy more tickets, just buy a winning ticket * 
               
                         Evil Looking       

              winsumloosesum's avatar - Lottery-060.jpg
              Pennsylvania
              United States
              Member #2218
              September 1, 2003
              5397 Posts
              Offline
              Posted: February 23, 2014, 8:34 pm - IP Logged

              Need to know the correct function/formula/statement, ( I do not know the proper name of what it's
              called )to enter into a Excel 2010.

              That will count the amount of times of one or more matching numbers that may have been drawn with in
              each past game of 20 drawn numbers.


              Example: Numbers played (23,2,34,21,11)

              Check with in a very large list of over 2000 past draws, each game has 20 numbers, each number in a
              separate cell. The past draws are NOT sorted or have duplicated numbers.

              PAST DRAWN NUMBERS: |23|4|63|51|31|10|19|2|66|45|7|3|27|1|14|21|39|16|29|2| >RESULT> Hit 3 numbers


              PAST DRAWN NUMBERS: |23|4|63|51|31|10|19|2|66|45|7|3|27|1|14|21|39|16|29|18| >RESULT> Hit 2 numbers


              PAST DRAWN NUMBERS: |22|4|63|51|31|10|19|2|66|45|7|3|27|1|14|28|39|16|29|3| >RESULT> Hit 0 numbers

              Surly this is very easy for excel people, but not for me at this time.

              Any help is greatly appreciated.... Thank you

              https://app.box.com/s/5qlhjc4jcym6a1wk30ld

              Enter your numbers in cell V1 (use commas)

              Results appear in column W10 downward

                SergeM's avatar - slow icon.png
                Economy class
                Belgium
                Member #123700
                February 27, 2012
                4035 Posts
                Offline
                Posted: February 24, 2014, 6:57 am - IP Logged

                I am impressed by your knowledge. Coffee