Welcome Guest
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

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

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 23 4 63 51 31 10 19 2 66 45 7 3 27 1 14 21 39 16 29 2 23 4 63 51 31 10 19 2 66 45 7 3 27 1 14 21 39 16 29 18 22 4 63 51 31 10 19 2 66 45 7 3 27 1 14 28 39 16 29 3
 #'s Played 23 2 34 21 11
 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)

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 23 4 63 51 31 10 19 2 66 45 7 3 27 1 14 21 39 16 29 2 23 4 63 51 31 10 19 2 66 45 7 3 27 1 14 21 39 16 29 18 22 4 63 51 31 10 19 2 66 45 7 3 27 1 14 28 39 16 29 3
 #'s Played 23 2 34 21 11
 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

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.

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

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 *

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

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.

 Page 1 of 1