Welcome Guest
You last visited December 4, 2016, 9:15 am
All times shown are
Eastern Time (GMT-5:00)

# excel formula ? to show what number came out after a winning number

Topic closed. 18 replies. Last post 4 years ago by SergeM.

 Page 1 of 2
Illinois
United States
Member #129173
June 11, 2012
87 Posts
Offline
 Posted: June 21, 2012, 11:32 pm - IP Logged

For Pick 3 or Pick 4, each digit of the winning number is in its own column.
For Pick 5 game, each of the five numbers is in its own column.
Let's say with a Pick 5 game, #10 fell in tonight's game in the 2nd numerical position (2nd column). What I want to find is what number came out after #10 in the 2nd position/column of all previous games. Is there a formula I can type in to give me this info?
All I know how to do right now is to use the AutoFilter to find the dates that #10 fell in the 2nd position/column, copy the dates, then look at the whole list to see what numbers fell "after" the dates that #10 was drawn. I'm wondering if there's an easier way to arrive at the info I want.

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: June 22, 2012, 6:51 am - IP Logged

There is a more complicated way.

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: June 22, 2012, 7:26 am - IP Logged

Let's say with a Pick 5 game, #10 fell in tonight's game in the 2nd numerical position (2nd column). What I want to find is what number came out after #10 in the 2nd position/column of all previous games.

Is there a formula I can type in to give me this info?

All I know how to do right now is to use the AutoFilter to find the dates that #10 fell in the 2nd position/column, copy the dates, then look at the whole list to see what numbers fell "after" the dates that #10 was drawn. I'm wondering if there's an easier way to arrive at the info I want.

Using worksheetfunctions for pick 5:

Use one column next to the data for the output, enter a function to return the second number if the previous datarow contained number 10 at ordinal position 2.

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: June 22, 2012, 10:55 am - IP Logged

PICK3 BE

COUNT: TRAILER T/F BY LINE

 N\T 0 1 2 3 4 5 6 7 8 9 0 215 238 197 212 209 215 204 214 198 218 1 217 220 246 239 221 200 223 229 231 230 2 215 220 191 226 230 208 207 206 203 239 3 201 220 203 222 230 212 225 220 230 230 4 204 251 198 208 190 239 212 229 229 232 5 209 230 194 212 200 192 222 214 219 205 6 175 210 226 213 221 215 224 215 221 245 7 221 231 234 209 225 206 225 225 233 223 8 224 209 224 229 230 202 227 235 230 201 9 245 239 214 229 230 205 209 227 214 218

WITH VBA

You don't sum up dates, so that would be a list on the other side.

If you specialize: number occurs 1 time, trailer occurs 1 time, then you get a different result.

 N\T 0 1 2 3 4 5 6 7 8 9 0 173 197 145 174 170 171 163 162 161 179 1 173 192 191 189 186 161 185 177 186 192 2 156 170 143 175 176 160 154 163 153 186 3 168 178 151 171 178 167 187 179 179 183 4 175 203 159 164 146 189 159 184 192 190 5 173 182 149 167 156 150 187 172 189 161 6 133 172 174 175 183 173 177 167 184 199 7 180 181 174 168 191 166 185 176 182 188 8 186 175 175 184 184 169 186 197 187 161 9 187 198 163 184 183 167 175 180 171 174

Indeed you find strange stuff when you define how many times the number and trailer should occur.
So I found here for number 3 times and trailer 3 times the following table.

 N\T 0 1 2 3 4 5 6 7 8 9 0 0 0 0 0 0 0 0 0 2 0 1 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 3 0 0 0 0 0 0 0 0 0 0 4 0 0 0 0 0 0 0 0 0 0 5 0 0 0 0 0 0 0 0 0 0 6 0 0 0 0 0 0 0 0 0 0 7 0 0 0 0 0 0 0 0 0 0 8 0 0 0 0 0 0 0 0 0 0 9 0 0 0 0 0 0 0 0 0 0

I did a test and it is true. It happened:

 1/12/2007 20/06/2012

So I don't know if your post is related to the results of Pick 3 of Belgium.

N*2 AND T*2

 N\T 0 1 2 3 4 5 6 7 8 9 0 5 2 3 0 0 1 3 2 2 1 1 1 2 4 1 2 1 2 2 6 2 2 1 3 4 4 1 1 1 3 0 4 3 2 3 1 4 3 1 1 4 1 2 4 1 4 1 0 1 2 1 0 1 2 5 2 1 2 1 3 2 3 1 0 3 6 3 1 6 3 1 1 3 2 0 1 7 2 1 5 4 0 1 4 0 4 2 8 1 1 3 2 2 0 1 2 5 2 9 0 1 4 2 1 0 0 4 1 1

And so on.

I suppose that your view at it is sequential.

Belgium
Member #128218
May 19, 2012
96 Posts
Offline
 Posted: June 22, 2012, 2:24 pm - IP Logged

Topic sounds nice...

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: June 22, 2012, 2:51 pm - IP Logged

Topic sounds nice...

My data input above was wrong above, but the functions work. It was my exercise file.

I need a break from this.

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5893 Posts
Offline
 Posted: June 22, 2012, 4:01 pm - IP Logged

Is this what you are looking for?  LDP1= Last draw p1; NDP= next draw p1' you will have to do this for each postion.

formula in Hits column= change the ranges for your workbook.

=SUMPRODUCT(--('HOT-COLD'!\$B\$8:\$B\$4000=\$AF\$7),--('HOT-COLD'!\$B\$9:\$B\$4001=\$AG7))

formula for  skips, enter as array formula, Ctrl-Shift-Enter!!!

=CHOOSE(IF(N(\$AH7),IF(COLUMNS(\$AM7:AM7)=1, 1, IF(COLUMNS(\$AM7:AM7)<=\$AH7,2,3)),3),SMALL(IF('HOT-COLD'!\$B\$8:\$B\$4000&"@"&'HOT-COLD'!\$B\$9:\$B\$4001=\$AF\$7&"@"&\$AG7,ROW('HOT-COLD'!\$B\$8:\$B\$4000)-ROW('HOT-COLD'!\$B\$8)),1),SUM(SMALL(IF('HOT-COLD'!\$B\$8:\$B\$4000&"@"&'HOT-COLD'!\$B\$9:\$B\$4001=\$AF\$7&"@"&\$AG7,ROW('HOT-COLD'!\$B\$8:\$B\$4000)),{1,0}+COLUMNS(\$AM7:AM7)-1)*{1,-1}),"")

 LDP1 NDP1 HITS MED MAX AVG DUE SKIPS 1 1 92 38 154 43.41 2 106 3 72 18 286 55.47 1 54 2 68 46 205 58.74 0 0 4 60 43 245 66.57 1 69 5 43 45 549 92.88 0 21 6 40 71 371 99.85 1 128 7 34 109 386 117.47 1 107 8 33 103 315 121.03 1 150 9 27 123 356 147.93 1 216 12 24 93 741 166.42 1 219 11 23 97 740 173.65 2 276 10 22 141 656 181.55 2 417 15 16 127 558 249.63 1 322 13 10 212 1192 399.40 3 1192 16 7 354 1849 570.57 0 191 14 6 468 1373 665.67 1 452 18 6 330 1270 665.67 0 28 17 5 587 1805 798.80 2 1805 19 5 233 1256 798.80 0 89 23 3 644 2171 1331.33 0 644 20 2 1390 1840 1997.00 1 1840 22 1 747 747 3994.00 0 747 27 1 2052 2052 3994.00 2052 24 1 1574 1574 3994.00 1574 21 0 #NUM! 0 #DIV/0! 26 0 #NUM! 0 #DIV/0! 28 0 #NUM! 0 #DIV/0! 25 0 #NUM! 0 #DIV/0! 29 0 #NUM! 0 #DIV/0! 30 0 #NUM! 0 #DIV/0! 31 0 #NUM! 0 #DIV/0! 32 0 #NUM! 0 #DIV/0! 33 0 #NUM! 0 #DIV/0! 34 0 #NUM! 0 #DIV/0! 35 0 #NUM! 0 #DIV/0! 36 0 #NUM! 0 #DIV/0!
Illinois
United States
Member #129173
June 11, 2012
87 Posts
Offline
 Posted: June 22, 2012, 4:03 pm - IP Logged

Using just a short span of winning numbers here, rather than all of them:

5/31/12 ==  5    10    13     26    34
6/1/12  ==   2    13    14    29     33
6/2/12  ==   1     4     35    36     39
6/3/12  ==   1     2      7      8     28
6/4/12  == 13    16    17    19     34
6/5/12  ==   5    12    32    33     39
6/6/12  == 13    20    25     31    38
6/7/12  == 11    15    16    21     38
6/8/12  ==  3      9    12     27     35
6/9/12  ==  1      3     7     17      20
6/10/12 ==  7    10    20    24     38
6/11/12 ==  7     8      9     11     35
6/12/12 == 12    17    18    31     36
6/13/12 ==   2    24    26    28     29
6/14/12 ==   2     9     10    11     26
6/15/12 ==   1    11    20    24     33
6/16/12 ==   5    13    16    22     27
6/17/12 ==   1    18    23    24     29
6/18/12 ==   3     7    19     21     36
6/19/12 ==   2     6     7     11      24
6/20/12 ==   2     6    23    28      35
6/21/12 ==   1    11    13    20     27

#10 fell  in the second position on 5/31/12 and on 6/10/12.  Numbers 13 and 8 came out after the #10.

So, in doing what you suggest, 13 and 8 (and more when I do the whole set of winning #s) will show in the results?

Thanks.

United States
Member #124493
March 14, 2012
7023 Posts
Offline
 Posted: June 22, 2012, 4:27 pm - IP Logged

Is this what you are looking for?  LDP1= Last draw p1; NDP= next draw p1' you will have to do this for each postion.

formula in Hits column= change the ranges for your workbook.

=SUMPRODUCT(--('HOT-COLD'!\$B\$8:\$B\$4000=\$AF\$7),--('HOT-COLD'!\$B\$9:\$B\$4001=\$AG7))

formula for  skips, enter as array formula, Ctrl-Shift-Enter!!!

=CHOOSE(IF(N(\$AH7),IF(COLUMNS(\$AM7:AM7)=1, 1, IF(COLUMNS(\$AM7:AM7)<=\$AH7,2,3)),3),SMALL(IF('HOT-COLD'!\$B\$8:\$B\$4000&"@"&'HOT-COLD'!\$B\$9:\$B\$4001=\$AF\$7&"@"&\$AG7,ROW('HOT-COLD'!\$B\$8:\$B\$4000)-ROW('HOT-COLD'!\$B\$8)),1),SUM(SMALL(IF('HOT-COLD'!\$B\$8:\$B\$4000&"@"&'HOT-COLD'!\$B\$9:\$B\$4001=\$AF\$7&"@"&\$AG7,ROW('HOT-COLD'!\$B\$8:\$B\$4000)),{1,0}+COLUMNS(\$AM7:AM7)-1)*{1,-1}),"")

 LDP1 NDP1 HITS MED MAX AVG DUE SKIPS 1 1 92 38 154 43.41 2 106 3 72 18 286 55.47 1 54 2 68 46 205 58.74 0 0 4 60 43 245 66.57 1 69 5 43 45 549 92.88 0 21 6 40 71 371 99.85 1 128 7 34 109 386 117.47 1 107 8 33 103 315 121.03 1 150 9 27 123 356 147.93 1 216 12 24 93 741 166.42 1 219 11 23 97 740 173.65 2 276 10 22 141 656 181.55 2 417 15 16 127 558 249.63 1 322 13 10 212 1192 399.40 3 1192 16 7 354 1849 570.57 0 191 14 6 468 1373 665.67 1 452 18 6 330 1270 665.67 0 28 17 5 587 1805 798.80 2 1805 19 5 233 1256 798.80 0 89 23 3 644 2171 1331.33 0 644 20 2 1390 1840 1997.00 1 1840 22 1 747 747 3994.00 0 747 27 1 2052 2052 3994.00 2052 24 1 1574 1574 3994.00 1574 21 0 #NUM! 0 #DIV/0! 26 0 #NUM! 0 #DIV/0! 28 0 #NUM! 0 #DIV/0! 25 0 #NUM! 0 #DIV/0! 29 0 #NUM! 0 #DIV/0! 30 0 #NUM! 0 #DIV/0! 31 0 #NUM! 0 #DIV/0! 32 0 #NUM! 0 #DIV/0! 33 0 #NUM! 0 #DIV/0! 34 0 #NUM! 0 #DIV/0! 35 0 #NUM! 0 #DIV/0! 36 0 #NUM! 0 #DIV/0!

good gosh carbob!!!

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5893 Posts
Offline
 Posted: June 22, 2012, 4:35 pm - IP Logged

Using just a short span of winning numbers here, rather than all of them:

5/31/12 ==  5    10    13     26    34
6/1/12  ==   2    13    14    29     33
6/2/12  ==   1     4     35    36     39
6/3/12  ==   1     2      7      8     28
6/4/12  == 13    16    17    19     34
6/5/12  ==   5    12    32    33     39
6/6/12  == 13    20    25     31    38
6/7/12  == 11    15    16    21     38
6/8/12  ==  3      9    12     27     35
6/9/12  ==  1      3     7     17      20
6/10/12 ==  7    10    20    24     38
6/11/12 ==  7     8      9     11     35
6/12/12 == 12    17    18    31     36
6/13/12 ==   2    24    26    28     29
6/14/12 ==   2     9     10    11     26
6/15/12 ==   1    11    20    24     33
6/16/12 ==   5    13    16    22     27
6/17/12 ==   1    18    23    24     29
6/18/12 ==   3     7    19     21     36
6/19/12 ==   2     6     7     11      24
6/20/12 ==   2     6    23    28      35
6/21/12 ==   1    11    13    20     27

#10 fell  in the second position on 5/31/12 and on 6/10/12.  Numbers 13 and 8 came out after the #10.

So, in doing what you suggest, 13 and 8 (and more when I do the whole set of winning #s) will show in the results?

Thanks.

You will need to sort the draws, new to old, for formulas to work correct.

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: June 22, 2012, 4:40 pm - IP Logged
 N LAST DIGIT GROUP O/E FREQ. SKIPS ACTUAL HIGH SKIP DUE H.S. 1 1 0 ODD 6 2,0,5,5,1,3 0 -5 - 2 2 0 EVEN 6 1,1,9,0,4,0 -1 -9 0,11 3 3 0 ODD 3 8,0,8 -3 -8 0,38 4 4 0 EVEN 1 2 -19 -19 1,00 5 5 0 ODD 3 4,10 -5 -10 0,50 6 6 0 EVEN 2 19,0 -1 -19 0,05 7 7 0 ODD 6 3,5,0,0,6,0 -2 -6 0,33 8 8 0 EVEN 2 3,7 -10 -10 1,00 9 9 0 ODD 3 8,2,2 -7 -8 0,88 10 0 10 EVEN 3 9,3 -7 -9 0,78 11 1 10 ODD 6 7,3,2,0,3,1 0 -7 - 12 2 10 EVEN 3 5,2,3 -9 -9 1,00 13 3 10 ODD 6 0,2,1,9,4 0 -9 - 14 4 10 EVEN 1 1 -20 -20 1,00 15 5 10 ODD 1 7 -14 -14 1,00 16 6 10 EVEN 3 4,2,8 -5 -8 0,63 17 7 10 ODD 3 4,4,2 -9 -9 1,00 18 8 10 EVEN 2 12,4 -4 -12 0,33 19 9 10 ODD 2 4,13 -3 -13 0,23 20 0 20 EVEN 5 6,2,0,4,5 0 -6 - 21 1 20 ODD 2 7,10 -3 -10 0,30 22 2 20 EVEN 1 16 -5 -16 0,31 23 3 20 ODD 2 17,2 -1 -17 0,06 24 4 20 EVEN 5 10,2,1,1,1 -2 -10 0,20 25 5 20 ODD 1 6 -15 -15 1,00 26 6 20 EVEN 3 12,0 -7 -12 0,58 27 7 20 ODD 3 8,7,4 0 -8 - 28 8 20 EVEN 3 3,9,6 -1 -9 0,11 29 9 20 ODD 3 1,11,3 -4 -11 0,36 30 0 30 EVEN 0 NIET NIET 31 1 30 ODD 2 6,5 -9 -9 1,00 32 2 30 EVEN 1 5 -16 -16 1,00 33 3 30 ODD 3 1,3,9 -6 -9 0,67 34 4 30 EVEN 2 3 -17 -17 1,00 35 5 30 ODD 4 2,5,2,8 -1 -8 0,13 36 6 30 EVEN 3 2,9,5 -3 -9 0,33 37 7 30 ODD 0 NIET NIET 38 8 30 EVEN 3 6,0,2 -11 -11 1,00 39 9 30 ODD 2 2,2 -16 -16 1,00

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: June 22, 2012, 4:46 pm - IP Logged
 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 N N N N 0 N N N N 0 N N 0 N N N N N N N N N N N N 0 N N N N N N N 0 N N N N N N 0 N N -1 N N N N -1 N N 0 0 N N N N N N N N N N N -1 N N 0 N N N 0 -1 N N N N N 0 -1 N 0 -2 N N N N -2 N N -1 -1 N N N N N N N N N N N -2 N N -1 N N N -1 -2 0 0 N N 0 0 0 N -1 -3 N 0 0 N -3 N N -2 -2 N N N N N N N N N N N -3 N 0 -2 N N N -2 -3 -1 -1 N N -1 -1 -1 N -2 -4 N -1 -1 N -4 N N 0 -3 N 0 0 N 0 N N N N N N -4 N -1 -3 N N N -3 0 -2 -2 N N -2 -2 -2 N -3 0 N -2 -2 N -5 N 0 -1 -4 N -1 -1 N -1 N N N N N N -5 N -2 -4 N N 0 0 -1 -3 -3 N N 0 -3 -3 N -4 -1 N -3 -3 N -6 N -1 0 -5 N -2 -2 N -2 0 N N N N 0 -6 N -3 -5 N 0 -1 -1 -2 -4 -4 N 0 -1 -4 -4 N -5 -2 N -4 -4 N -7 0 -2 -1 -6 0 0 -3 N -3 -1 0 N N N -1 -7 N -4 -6 N -1 -2 -2 -3 -5 -5 N 0 -2 -5 -5 0 -6 -3 N -5 -5 0 -8 -1 0 -2 -7 -1 -1 -4 N -4 -2 -1 N N N -2 -8 0 -5 -7 N -2 -3 -3 -4 0 -6 N -1 -3 0 -6 0 -7 -4 N 0 -6 -1 -9 -2 -1 -3 -8 -2 -2 0 N -5 0 -2 N N N -3 -9 -1 -6 -8 N -3 -4 -4 -5 -1 -7 N -2 -4 -1 -7 -1 -8 -5 N 0 -7 -2 0 -3 -2 -4 -9 -3 -3 -1 N -6 0 -3 N N 0 -4 -10 -2 -7 -9 N -4 -5 -5 -6 -2 -8 N 0 -5 -2 -8 -2 -9 -6 N 0 0 0 -1 0 -3 -5 -10 -4 -4 -2 N -7 -1 -4 N N -1 -5 -11 -3 -8 -10 N -5 -6 -6 -7 0 -9 N -1 -6 -3 -9 -3 -10 -7 N -1 -1 -1 -2 -1 0 -6 -11 -5 -5 0 0 -8 -2 -5 N N -2 -6 -12 -4 -9 -11 N 0 -7 -7 -8 -1 0 N -2 -7 -4 0 -4 -11 -8 N -2 -2 -2 -3 -2 -1 -7 -12 -6 -6 -1 -1 -9 -3 -6 N N 0 -7 0 -5 0 0 N -1 -8 -8 -9 -2 -1 N -3 -8 -5 0 -5 -12 -9 N -3 -3 0 0 0 -2 -8 -13 -7 -7 -2 -2 -10 -4 -7 N N -1 -8 0 -6 -1 -1 N -2 -9 -9 -10 -3 -2 N -4 -9 0 -1 -6 -13 -10 N -4 -4 -1 -1 0 -3 -9 -14 -8 -8 -3 -3 -11 0 -8 N N 0 -9 -1 -7 -2 -2 N -3 -10 0 -11 -4 -3 N -5 -10 -1 -2 -7 -14 0 N -5 -5 -2 -2 -1 -4 0 -15 -9 0 -4 -4 -12 -1 -9 0 N -1 -10 -2 0 -3 -3 N -4 -11 -1 -12 -5 -4 N -6 -11 0 -3 -8 -15 -1 N -6 -6 -3 -3 -2 -5 -1 -16 -10 -1 -5 0 -13 -2 -10 -1 0 0 -11 -3 -1 -4 0 N -5 -12 -2 -13 -6 -5 N -7 -12 -1 -4 0 -16 -2 N 0 -7 -4 -4 -3 -6 -2 -17 -11 -2 -6 -1 0 -3 0 -2 -1 -1 -12 -4 -2 -5 -1 N -6 -13 -3 -14 -7 0 N -8 -13 -2 0 -1 -17 -3 0 0 -8 -5 -5 0 -7 -3 -18 -12 -3 -7 -2 -1 -4 -1 -3 -2 0 -13 -5 -3 -6 -2 N -7 -14 -4 -15 -8 -1 N -9 -14 -3 0 -2 -18 -4 0 -1 -9 -6 -6 -1 -8 -4 -19 -13 -4 -8 -3 -2 -5 -2 -4 0 -1 -14 -6 -4 0 -3 N -8 -15 -5 -16 0 -2 N -10 -15 0 -1 -3 -19 -5 -1 -2 -10 -7 -7 0 -9 0 -20 -14 -5 -9 -4 -3 0 -3 -5 -1 -2 -15 -7 0 -1 -4 N -9 -16 -6 -17 -1 -3 N -11 -16

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: June 22, 2012, 4:49 pm - IP Logged
 s 20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 G.O. 1 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 0 5 5 1 3 0 2 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 1 9 0 4 0 1 3 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 0 8 3 4 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 19 5 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 4 10 5 6 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 0 1 7 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 5 0 0 6 0 2 8 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 7 10 9 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 2 2 7 10 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 9 3 7 11 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 3 2 0 3 1 0 12 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 2 3 9 13 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 0 2 1 9 4 0 14 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 20 15 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 14 16 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 2 8 5 17 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 4 2 9 18 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 4 4 19 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 13 3 20 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 2 0 4 5 0 21 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 10 3 22 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 5 23 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 2 1 24 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 2 1 1 1 2 25 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 15 26 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 12 0 7 27 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 7 4 0 28 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 9 6 1 29 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 11 3 4 30 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ NIET 31 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 5 9 32 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 16 33 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 3 9 6 34 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 3 17 35 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 5 2 8 1 36 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 9 5 3 37 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ NIET 38 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 0 2 11 39 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 2 16

Illinois
United States
Member #129173
June 11, 2012
87 Posts
Offline
 Posted: June 23, 2012, 4:47 am - IP Logged

Gee, I'm sorry, but none of these suggestions sounds like the info I'm looking for. I'm not interested in pick 3, skips, hits, avg, med, max, due, odd/even, or frequency. Let me rephrase what I'm wanting, hope that Excel can perform it, and someone knows how to get the results for me.

Winning Lotto numbers are in numerical order, with numbers in their respective columns. If I want to search the 2nd number column for the number/value contained in the next cell directly below a given number/value, say 10, is there a formula I can type in that will lookup all the values of 10 but only show me the next cell below each value of 10?

Column A is the date of the drawing.

Column B is the first number in numerical order.

Column C is the 2nd number in numerical order. 10 is in Column C, with Row numbers of:

24, 53, 61, 76, 78, 106, 111, 134, 143, 153, 156, 184, 191, 202, 214, 226, 248, 284, 338, 345, 401, 410, 413, 425, 438, 448, 455, 508, 515, 542, 552.

But I'm wanting results to show what is in the next cell below each #10, so I want a formula to show me Rows:

25, 54, 62, 77, 79, 107, 112, 135, 144, 154, 157, 185, 192, 203, 215, 227, 249, 285, 339, 346, 402, 411, 414, 426, 439, 449, 456, 509, 516, 543, 553.

Is there a way to get Excel to show me this information?

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: June 23, 2012, 5:47 am - IP Logged

Gee, I'm sorry, but none of these suggestions sounds like the info I'm looking for. I'm not interested in pick 3, skips, hits, avg, med, max, due, odd/even, or frequency. Let me rephrase what I'm wanting, hope that Excel can perform it, and someone knows how to get the results for me.

Winning Lotto numbers are in numerical order, with numbers in their respective columns. If I want to search the 2nd number column for the number/value contained in the next cell directly below a given number/value, say 10, is there a formula I can type in that will lookup all the values of 10 but only show me the next cell below each value of 10?

Column A is the date of the drawing.

Column B is the first number in numerical order.

Column C is the 2nd number in numerical order. 10 is in Column C, with Row numbers of:

24, 53, 61, 76, 78, 106, 111, 134, 143, 153, 156, 184, 191, 202, 214, 226, 248, 284, 338, 345, 401, 410, 413, 425, 438, 448, 455, 508, 515, 542, 552.

But I'm wanting results to show what is in the next cell below each #10, so I want a formula to show me Rows:

25, 54, 62, 77, 79, 107, 112, 135, 144, 154, 157, 185, 192, 203, 215, 227, 249, 285, 339, 346, 402, 411, 414, 426, 439, 449, 456, 509, 516, 543, 553.

Is there a way to get Excel to show me this information?

Put your function next to the outer right column, like told above.
If you want a list, you need to add a column for concatenation.
Maybe there is a complicated array function for this, I don't go that far because I use the VBE.

So, yes, it is possible.

 Page 1 of 2