Jun 19 2022 05:19 AM
Hello,
I'm working on a database to find patterns in a lottery, based on frequencies of combinations in the previous drawings.
My table has 3 columns: Ball 1, Ball 2, and Pairs. Ball 1 and Ball 2 column is numbered 1-35 with no duplicates (ex, 1 & 2, 1 & 3, 1 & 35, 2 & 3, 2 & 4, etc.). 34 & 35 is the final row.
My database has all the drawings and setup to give me the number of drawings with each pair of balls entered.
Inputs: B1 (Ball1) and C1 (Ball 2)
Output: G1 (Pairs)
G1 is a formula that counts how many drawings have the balls (inputs) in it. In the G column of the database, each drawing has a formula there that uses COUNTIF to determine how many balls in B1:F1 is in the drawing (row).
Example:
B1 = 1, C1 = 2, Then G1 = 4
This means that 4 drawings have both balls "1" and "2" in them.
If you make a change (C1 = 3), then G1 = 5; meaning 5 drawings have both balls "1" and "3".
I'm looking for a formula something like, "This cell is equal to "G1" if "B1" = X and "C1" = Y, where X & Y are the balls paired together. I can only get G1 when I manually enter B1 and C1.
With 595 rows to find the outputs of each combination, is there a formula I can use or if not, how can I use the Data Table function?
Jun 19 2022 08:16 AM - edited Jun 19 2022 09:48 AM
SolutionJun 19 2022 01:51 PM
Jun 19 2022 08:16 AM - edited Jun 19 2022 09:48 AM
SolutionI have created a sample file. Is that what you meant?
A second Example with an alternative pair calculation.