The Excel Forumla to Show How Many times a Number Fell with a Number.Prev TopicNext Topic

• gjpartee

  United States
  Member #74,786
  May 20, 2009
  3 Posts
  Offline
  Jun 13, 2009, 1:30 am

  ▲▼

  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
• Raven62

  

  25

  New Jersey
  United States
  Member #17,842
  June 28, 2005
  180,983 Posts
  Offline
  Jun 15, 2009, 10:39 am

  ▲▼

  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!
  
  Catch-22: A dilemma or difficult circumstance from which there is no escape because of mutually conflicting or dependent conditions.
  
  Corruptissima re publica plurimae leges: When the republic is at its most corrupt the laws are most numerous.
  
  The best way to learn is to never stop being an Experiential Student!
• gjpartee

  United States
  Member #74,786
  May 20, 2009
  3 Posts
  Offline
  Jun 30, 2009, 7:28 pm

  ▲▼

  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?
• Raven62

  

  25

  New Jersey
  United States
  Member #17,842
  June 28, 2005
  180,983 Posts
  Offline
  Jun 30, 2009, 10:23 pm

  ▲▼

  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!
  
  Catch-22: A dilemma or difficult circumstance from which there is no escape because of mutually conflicting or dependent conditions.
  
  Corruptissima re publica plurimae leges: When the republic is at its most corrupt the laws are most numerous.
  
  The best way to learn is to never stop being an Experiential Student!
• gjpartee

  United States
  Member #74,786
  May 20, 2009
  3 Posts
  Offline
  Jul 6, 2009, 11:40 am

  ▲▼

  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.