Help with Excel

[Cartref45]

Hi,

Can anybody help me with a formula to calculate the following please?

I need to know how many distinct numbers in a line match the following line, I'm having a problem with lines that have the same number more than once.

3 5 10 13 18 answer = 1
1 1 3 14 19 answer = 1
3 4 5 5 13 answer = 1
1 2 6 8 13 answer = 1
1 4 7 10 17 answer = 3
1 2 6 7 10 answer = 2
1 2 3 15 19 answer = 1
3 3 6 7 20 answer = 1
1 2 6 8 16 answer = 2
1 3 14 16 19 answer = 0
2 7 7 12 18 answer = 2
2 2 6 8 12 answer = 1
1 6 10 19 22 answer = 1
1 3 7 13 17 answer = 2
1 1 3 3 4 answer = 2
1 4 5 14 16 answer = 0
3 6 12 17 24 answer = 0
1 1 2 4 38 answer = 2
2 4 10 14 15

I'd apprecite any help

Thanks

Cartref45

 

[Frank]

Well I don't happen to know a single nifty formula that sits in a single cell which will give an answer, only by using 2 checking tables can I come up with an answer, so my version needs 11 columns to get the answer.

If your data is in cells A1:E19 then in cell G1 copy and paste this formula in : =IF(ISNA(MATCH(A1,$A2:$E2,0)),"",MATCH(A1,$A2:$E2,0)).

Copy this formula over to cell K1, now select range G1:K1 and copy the row of formulas down to row 19. This table lists the (column) position of each match in the row below it.
The next checking table looks for duplicated values of position in the match table and counts how many in each position.

In cell M1 enter the formula = SUM(IF($G1:$K1=1,1,0)) . This should be an ARRAY formula. To make it an array formula, with your cursor at the end of the formula you have to hold down shift and ctrl keys whilst pressing <enter>, then the formula will appear like this :- {=SUM(IF($G1:$K1=1,1,0))} the brackets appear after you enter it in the way described..
This checks for how many 1's there are in the match table ( the =1 part of the formula) on this row and counts them. The next cell to the right, N1 will count 2's so its formula is {=SUM(IF($G1:$K1=2,1,0))}. The easy way is to copy the formula in M1 along and then edit N1 to change the =1 to =2, not forgetting this is an array formula when you've finished. Similarly O1, P1, Q1 need to be edited to check for 3,4,5 respectively and made into array formulas. when Range M1:Q1 have formulas in them, then select the range and copy the formulas down to row 19. You should have a grid of 0's,1's,2's etc in the five columns. All that remains is to count how many values are above zero on each row of this checking table. Enter formula =COUNTIF(M1:Q1,">0") in cell R1. Copy this formula down the column to row 19. This column now holds the answer you are looking for.

Note:-The checking tables could be hidden by hiding the columns if theres nothing else using those columns.

Here is a screenshot of my test sheet:-
https://www.mediafire.com/?h6kp23n9abctctj

If anyone has a better solution, please share it with us.

 

[Cartref45]

Hi Frank,

Thanks for your help, just downoaded the file and I'll integrate it into my sheet in a bit, just got back from the cinema with the wife.

I had a feeling it would be too complex for just one cell.

I'll follow up a little later

Thanks

Cartref

 

[Cartref45]

Frank,

That works perfectly, I have always used the "MATCH" function before in Columns, it never occurred to me to use it in Rows.

The explanation was also spot on, thanks.

I tend to service many different sheets for various criteria, I then look for a couple that are overdue to use with common repeating filters to remove numbers or lines.

This one is for looking at repeating skips.

Cartref

 

[Frank]

Well I'm glad it worked OK. Good luck with your system.

 

[Icewynd]

I do something similar with skips, but I count them for each game, then I roll them up into categories. =COUNTIF(B2:51,0), where B2:B51 are the skips for the 49 numbers in my game.

I think what you are calling skip 1 is what I call skip 0 -- i.e. zero skips between hits, or a hit from the same number twice in a row. This is the most common type of hit in any lottery game.

The categories I use are Skips 0-4, 5-10, 11-15, and 16+. Usually about half of the winning numbers will fall in the 0-4 category. Don't know what your game is, but I see a lot of skips between 1 and 3.

Good Luck!

 

[Cartref45]

Icewynd / Frank,

Yes I sometimes use skips similar to Icewynd, but I use 1 instead of his (0) for a repeat, I use groups 1-3 (0-2), 4-8 (3-7) and >=9 (>=8). This is similar to the first column of "History Segments" in Expert Lotto.

The lines that Frank kindly worked on for me represent the skips for the last 19 draws of the UK Thunderball in skip order.

Thanks to Frank's work I can use a "skip repeat table" amongst other stats to look at overdue possibilities. As you can see from the answer column, a repeat of 1 skip has repeated 4 consecutive times, the average for a repeat of 1 is every 2 draws for the last 900 draws.

So the last skips for the UK Thunderbal were: 3,5,10,13,18 - for the next draw this represents numbers 3;14,26,35 - 5;10,28,34 - 10;3,27, 13;2,11 & there are no numbers that have a skip of 18.

So I am proposing that a single match of 1 for any 1 of the above 4 number groups is unlikely to happen. Using a Group Filter in the above program to achieve this I can reject 261,566 of the 575757 possible lines (5/39 w/o bonus), roughly 45%.

Similarly I can use this with another overdue filter concerning "Decade Groups", Units, Tens, Twenties & Thirties.

Examples of the Decade Group Filter are:
3011 - 3 Units, 0 Tens, 1 Twenty & 1 Thirty
0212 - 0 Units, 2 Tens, 1 Twenty & 2 Thirties

A "repeat table" for this filter shows that a repeat of 2 is very overdue, the last Decade Group distribution for the UK Thunderball was 1211, so I propose to accept lines that have only 2 of the previous Group,eg, 1121, 2210, 0311 etc. This reduces the 575,757 lines to 289,950, a reduction of around 50%.

I know it's a big ask for both filters to hit the same week but we have to have some hope sometimes, these 2 filters used together leave 158,823 from the initial 575757 lines a reduction of around 72%.

There are many filters like this, but they only happen now and again, that's why I keep so many spreadsheets and stats. Hopefully, my theory is correct.

Anyway, thanks to you both for replying, my previous posts didn't spark much conversation even though 185 people downloaded 1 file and 32 downloaded the other. As someone said in a previous post somewhere, there are too many lurkers and not enough posters.

But to the people who do post and help each other, I wish success and maybe a big win.

Incidentally, I realised during this post, that if it's a long one, draft in Notebook first.

Cartref

 

[Icewynd]

Interesting stuff, Cartref! And very similar to what I am doing for the Canadian 6/49.

I must say, though, that my thinking is starting to shift away from chasing the long out measure (digit, decade, pattern, etc). towards sticking with what is happening in the game now.

Too many times I have been frustrated by playing a group of numbers that are "due", only to have them stay out for an extended period of time. Now I am playing with the current pattern plus some "wild cards", i.e. random picks that I throw into the mix. Still haven't won the JP, though!

Good Luck!

 

[Cartref45]

Icewynd,

Yes I know what you mean that's why I mix long term outs and often recurring events. I get the occasional hit 4 out of 5 but more often hit 3's. Trouble is you need a couple of long shot filters to heavily reduce the number of lines, but if a couple of them hit together, your chances of a bigger prize must surely increase.

The good thing is that now I'm retired I have more time to find filters that are overdue and give good reduction, then I mix them up and stay with them until they hit.

Cartref

 

[Icewynd]

You might be interested in what I am doing over on the 6/49 board with completion cycles. The numbers that haven't yet hit in the current cycle are where I get my wild card picks, and the point in the cycle determines how many I select. I was just observing yesterday how few draws have all numbers within their usual ranges -- there is almost always an outlier!

Good Luck!

 

[bloubul]

Help any one

What formula can I use in excel to determine which digits was left out of a draw.
Data start in C1:I10000

BlouBul

 

[Icewynd]

I use COUNTIF to count the hits for each of the numbers in my game (1-49). Any numbers that show a zero are still unhit. When the cycle gets to the point where there are no unhit numbers remaining, I reset the formula to count the hits in the next cycle.

Good Luck!

 

[Frank]

You need to make a table with your ball numbers 1 to whatever (you dont tell us) along the top. The countif formulas go along the rows of each draw and count the corresponding number at the top of the table in the column of the formula.You will need a $ sign in each formula before the row number referencing the ball you are counting to make sure you only count the balls in the top row. The range of counting has to be anchored at the first ball in the cycle (or first ball in the lottery) so that address has got to have both $$ signs in use, whereas the last lotto ball drawn per draw (could be the 6th or 7th , does your 7 columns include a draw number ?) needs its column anchoring by using a $ sign in the formula. The exact formula depends on whether you have listed the NEWEST draw at the top of your list or the OLDEST draw at the top of your list. (you don't tell us ).This also controls whether you copy your row of formulas UP or DOWN.
Counting the zeroes on each row of the table should help you know how many undrawn balls there are, per draw.
In short, no-one can give you a formula because you havent given us sufficient information.