Welcome Guest
You last visited December 11, 2016, 2:30 am
All times shown are
Eastern Time (GMT-5:00)

# The Excel Forumla to Show How Many times a Number Fell with a Number.

Topic closed. 4 replies. Last post 7 years ago by gjpartee.

 Page 1 of 1

United States
Member #74788
May 20, 2009
3 Posts
Offline
 Posted: June 13, 2009, 1:30 am - IP Logged

Dear All

I am seeking help to find out how to design a speadsheet to show how many times each number in a lottery game fell a particular number.  For Example  For the Lottey game Quick Draw.   In Quick Draw you have have 80 numbers to choose from.  You choose ten numbers.  Twenty numbers will be drawn each day.  I have a speadsheet with  the 80 numbers collums accross the top of the page and the Date win row along the left side.  I put a one in each cell that corresponds to the numbers that fell that day.  I  use the count function to count the number time each number has feel.  Now want to do is count number of time each number fell a each number.  For Example. How time did the numbers 2-80 fell with 1, the numbers 3-80 fell with 2 and so forth.  Does the how I set my spead sheet to do so?

 1-May 1 2 3 4 5 6 7 8 9 10 2-May 1 1 3-May 1 1 1 4-May 1 1 1 5-May 1 1 1 6-May 1 1 1 7-May 1 1 1 8-May 1 1 1 3 4 3 4 3 2 2 4 2 3
New Jersey
United States
Member #17843
June 28, 2005
49835 Posts
Offline
 Posted: June 15, 2009, 10:39 am - IP Logged

Sheet 1:
Column A=Date
Column B thru K=Numbers Drawn

Sheet 2:
Column A=Date
Column B thru AT=Pairs of Numbers Drawn

Sheet 3: 80 x 80 Matrix
Row 1=B thru CC (01 thru 80)
Column A=2 thru 81 (01 thru 80)
Cells B2 thru CC81=Pair Count

A mind once stretched by a new idea never returns to its original dimensions!

United States
Member #74788
May 20, 2009
3 Posts
Offline
 Posted: June 30, 2009, 7:28 pm - IP Logged

Thank You for your information.  However I would like to know not only the pairs but How many time did each number fall with number 1 For Example  Here Is sample  chart

 #'s 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 #'s w/ 1 20 5 11 6 4 3 6 5 4 3 8 4 3 2 5 18 8 4 5 2 5 4 4 5 4 1 3 3 4 6 3 3 11 8 21 2 5 2 7 4 6 3 9 4 6 4 2 14 0 0 0 0 0 4 5 4 5 5 18 0 0 0 0 5 6 3 2 2 19 0 0 0 8 7 6 5 7 24 0 0 10 8 5 4 4 23 0 9 9 4 4 6 17 7 10 3 5 3 15 4 11 8 5 9 4 5 8 10 9 7 4 28 4 7 8 9 7 7 12 5 1 2 4 13 13 3 3 3 7 17 14 7 3 6 8 22 15 9 4 3 9 22

Could give the excel formulas to produce from the Quick Draw history?

New Jersey
United States
Member #17843
June 28, 2005
49835 Posts
Offline
 Posted: June 30, 2009, 10:23 pm - IP Logged

Using your example: 01 & 11 fell together 8 times and 07 & 10 fell together 10 times:

#'s      1      2      3      4      5      6      7      8      9      10      11      12      13      14      15      16      17
#'s w/
1      20      5      11      6      4      3      6      5      4      3      8      4      3
2      5      18      8      4      5      2      5      4      4      5      4      1      3      3      4      6      3
3      11      8      21      2      5      2      7      4      6      3      9
4      6      4      2      14      0      0      0      0      0      4
5      4      5      5             18      0      0      0      0      5
6      3      2      2                    19      0      0      0      8
7      6      5      7                           24      0      0      10
8      5      4      4                                  23      0      9
9      4      4      6                                         17      7
10      3      5      3                                                15      4
11      8      5      9      4      5      8      10      9      7      4      28      4      7      8      9      7      7
12      5      1      2                                                       4      13
13      3      3      3                                                       7             17
14      7      3      6                                                       8                    22
15      9      4      3                                                       9                           22

A mind once stretched by a new idea never returns to its original dimensions!

United States
Member #74788
May 20, 2009
3 Posts
Offline
 Posted: July 6, 2009, 11:40 am - IP Logged

Raven62

Thank you for your respomses I feel you could help me a great deal.  I would like  to send you the speadsheets that I have along the explanation of each sheet, whereas you can see exactly I am trying to do.  If this be all right with you.  How may I contacted you?   Because I need all of the excel forumlas to accomplish what i am trying to do.

 Page 1 of 1