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

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))

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))

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)

Zaperlopopotam
Belgium
Member #173932
March 26, 2016
961 Posts
Offline
 Posted: November 17, 2016, 7:24 am - IP Logged
 A B C 0 1 2 3 4 5 6 7 8 9 T 3 1 2 - - - - - - - - - - - 3 2 2 0 0 1 1 0 0 0 0 0 0 2

Matrix

.
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!

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

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

.
Zaperlopopotam
Belgium
Member #173932
March 26, 2016
961 Posts
Offline
 Posted: November 17, 2016, 2:02 pm - IP Logged
 A 1 2 3 4 0 1 2 3 4 5 6 7 8 9 T D 0123 0 1 2 3 1 1 1 1 0 0 0 0 0 0 - - 0235 0 2 3 5 1 0 1 1 0 1 0 0 0 0 - - 1245 1 2 4 5 0 1 1 0 1 1 0 0 0 0 - - - - - - - 0 0 1 0 0 0 0 0 0 0 1 2
.

 Page 1 of 1