Welcome Guest
You last visited December 10, 2016, 7:35 am
All times shown are
Eastern Time (GMT-5:00)

Topic closed. 23 replies. Last post 7 years ago by DMW774.

 Page 2 of 2
north carolina/virginia
United States
Member #2097
August 17, 2003
126 Posts
Offline
 Posted: February 14, 2010, 7:52 pm - IP Logged

I am trying to post the spreadsheet in full but I am having a hard time pasting to website.

north carolina/virginia
United States
Member #2097
August 17, 2003
126 Posts
Offline
 Posted: February 14, 2010, 8:28 pm - IP Logged

I used the formula  =countif(\$d\$3:F:F,"0")+Countif(\$d\$3:f:f,"=0") starting off with d3 is the beginning cell for the first drawn number in NC, through F cell representing the 3rd digit.   I copied this formula throughout for each digit.  I am not to sure of the exactness of this formula giving me the follow up numbers.  However I do know it shows the 6 is followed up alot, and 6 is the most drawn digit.  I am still working on this, and I will try the =match.

Number count(across)

Number drawn (down)

0       1       2       3      4       5        6      7       8       9             0 1120 1129 1118 1109 1098 1075 1135 1114 1073 1062             1 1129 1138 1127 1118 1107 1084 1144 1123 1082 1071             2 1118 1127 1116 1107 1096 1073 1133 1112 1071 1060             3 1109 1118 1107 1098 1087 1064 1124 1103 1062 1051             4 1098 1107 1096 1087 1076 1053 1113 1092 1051 1040             5 1075 1084 1073 1064 1053 1030 1090 1069 1028 1017             6 1135 1144 1133 1124 1113 1090 1150 1129 1088 1077             7 1114 1123 1112 1103 1092 1069 1129 1108 1067 1056             8 1073 1082 1071 1062 1051 1028 1088 1067 1026 1015             9 1062 1071 1060 1051 1040 1017 1077 1056 1015 1004
 All Positions
north carolina/virginia
United States
Member #2097
August 17, 2003
126 Posts
Offline
 Posted: February 14, 2010, 8:30 pm - IP Logged

ok that just posted all wrong, maybe you can copy this to excel then do a "text to column" to get it entered into the right cells.  Sorry I dont know how to get the spreadsheet to post here.

north carolina/virginia
United States
Member #2097
August 17, 2003
126 Posts
Offline
 Posted: February 14, 2010, 8:36 pm - IP Logged

Is the formula =countif(a1,"0") any help to you, or am I still missing what you are looking for?

Phoenix, AZ
United States
Member #70984
February 16, 2009
21 Posts
Offline
 Posted: February 15, 2010, 10:26 pm - IP Logged

The image is only for position 1, the formula below is telling how many times the digits going across have followed the digits going down, for position 1 only. The others will have to be created, by changing the ranges.

=SUMPRODUCT(--(\$W\$14:\$W\$115=AC\$14),(--(\$W\$15:\$W\$116=\$AB15)))

This is EXACTLY what I'm looking for. However, I can't seem to get it to work on my spreadsheet.  Assuming my list goes from B2 to B1500 (instead of B2 to B12, like yours), what is the exact formula I need to put in cell F3?

And thanks to all of you for your input.

He who says deafness is a disability is under the false impression that he is saying something worth hearing.

north carolina/virginia
United States
Member #2097
August 17, 2003
126 Posts
Offline
 Posted: February 16, 2010, 10:53 am - IP Logged

Hi this formula is helpful to me also, but I have a problem, my excel will not let me enter the formula you have above, it will not recognize the , so when I enter the formula as this =sumproduct(--\$d\$3:\$d\$10000=T\$1806)+(--(\$d\$4:\$d\$10000=s1807))    this formula will only give me the value of 1 in the answer area.  Do you have any idea what I am missing?

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5912 Posts
Offline
 Posted: February 16, 2010, 12:42 pm - IP Logged

The formula has to be enter like this, making sure the ranges are correct.

=SUMPRODUCT(--(\$B\$2:\$B\$1499=F\$2),(--(\$B\$3:\$B\$1500=\$E3)))

north carolina/virginia
United States
Member #2097
August 17, 2003
126 Posts
Offline
 Posted: February 16, 2010, 2:55 pm - IP Logged

After I copied your formula I have all three positions entered, thank you so much. Now I need to fix the spreadsheet so that as I enter new numbers the spreadsheet will calculate the new ranges.  I might go back and try to do an absolute of the B column C column and D column.

Phoenix, AZ
United States
Member #70984
February 16, 2009
21 Posts
Offline
 Posted: February 16, 2010, 7:05 pm - IP Logged

The formula has to be enter like this, making sure the ranges are correct.

=SUMPRODUCT(--(\$B\$2:\$B\$1499=F\$2),(--(\$B\$3:\$B\$1500=\$E3)))

That worked PERFECTLY. Thanks so much for the help.

He who says deafness is a disability is under the false impression that he is saying something worth hearing.

 Page 2 of 2