Please help with MS Excel

Blakespops

Hi All,

I have a problem and need advice, I have some data I need to add some filters to that will sort list removing variables I do not need leaving me with a smaller list to work from.

All this just to win a bet with a friend.

I have six sets of numbers Players on Roulette) each is to place a bet to set rules and friend says with these rules he can not lose. I wish to check. I need to I need to sort my list so the six sets of numbers are containing no more than 4 odd or 4 even. also I need it not to have more than 2 concecutive numbers. and no more than 3 in every 10.

victor2

Ignoring the fact that you can't beat the house, which I suspect is the camp you're in, interesting challenge.
It's more of a job for a database than a spreadshet. Fun to try in Excel though.
Do the six numbers all have to be different?
Are you intending to check them against a randomly generated number or is that another exercise altogether?

googler

Blakespops wrote: »

I have six sets of numbers ..... I need to I need to sort my list so the six sets of numbers are containing no more than 4 odd or 4 even. also I need it not to have more than 2 concecutive numbers. and no more than 3 in every 10.

So, to clarify ...

Each SET has no more than 4 odd or 4 even ???
East set has no more than 2 consecutive numbers

and no more than 3 consecutives in 10 ACROSS the sets....???

Kavajo

Put numbers from the first set in cells A1 to F1. Put other numbers from set below. Sort all of them into ascending order horizontally (use the sort left to right option).
You need to check if each of the numbers are odd or even. If you have your sets of numbers in columns, then you should add six more columns beside and enter a formula e.g. =IF(MOD(A1,2)=0,1,0) and repeat for other 5 columns. This will give a 1 if even, or a 0 if odd. Then add another column for the total of this check =SUM(G1:L1). If you get 4 here then you have 4 even and 2 odds. Filter on the total column for 0, 1, 5 & 6 and delete the rows each time.
Now to check the consecutive numbers rule. You need another formula to the right of the numbers. I'm in cell N1 =IF(OR(AND(A1+1=B1,B1+1=C1),AND(B1+1=C1,C1+1=D1),AND(C1+1=D1,D1+1=E1),AND(D1+1=E1,E1+1=F1)),1,0). Filter on column N and delete all rows with a 1.
And the 3 in 10 rule, which version of Excel are you on? 2007 and later supports multiple conditions in the COUNTIF function, so would be easy. If not, it gets messy here...

Blakespops

victor2 wrote: »

Ignoring the fact that you can't beat the house, which I suspect is the camp you're in, interesting challenge.
It's more of a job for a database than a spreadshet. Fun to try in Excel though.
Do the six numbers all have to be different?
Are you intending to check them against a randomly generated number or is that another exercise altogether?

I am in the camp he can not beat the house either but he says that 6 players all have to cover a set pattern of six numbers but all have to be split as I had said. no more than 4 odd or even numbers, no more than two consecutive numbers, no more than 3 from any set of ten(1-10,2-11,3-12 etc). he states law of average means doing this he always wins but he will not tell me how many lines of various combinations he was left with after he filtered it out as I say with 37 possible numbers it would still be way to high for him to have six friends do this and win every time and it has to be down to luck that he is winning.

now I am just stuck with it in my head and once you get an idea it is hard to let go so I wished to work out doing that as all 6 had to have only bet on six numbers how can they win. a winner is 35-1 so if they all bet seperatly they cover 36 of the 37 numbers thus not making a profit but he says if I work it out I will see how they can cover same number at times making them win more. I just do not get it so wished to work out how to add filters for this.

victor2

So if I understand this then, you want to know how many different combinations of 6 numbers between 0 and 36 are there that meet your criteria?
I understand the 3 from 10 bit now and assume that all six numbers must be different. Still not clear on how the betting would work though. How many bets can they place before they make a profit? Mind you I don't understand how betting on roulette works anyway!

Does the same friend have a foolproof plan for winning the lottery too?:)

Kavajo

I think I understand what his plan is... but it still doesn't make any sense. It sounds like his plan is to 'spread' the bets over the table to try and cover only the numbers that he considers 'more likely' according to his rules.

As other posters have pointed out, for a single number bet, it's a 37/1 odds for a 36/1 return (don't forget the Zero on the wheel). The multiple number bets are rigged in the houses favour in the same way.

Is he saying that once he has sorted all these combinations he will be left with a small enough number of combinations for 6 players to bet on all of them? But roulette just has one winning number each game, so there's no such thing as combinations. And you would never need 6 players, as a player can make as many bets as they like. The croupier will wait while players are placing bets (spending money).

If he is doing multiple bets on different numbers on the same spin, he will be betting against himself. If you bet on 6, then you bet on 17, the two bets oppose. It would make (only very slightly) more sense to bet on Black, Odd, 1-18, and number 17 in combination, so that the bets complement each other. Still won't work over a sustained period because the odds are rigged in the house's favour.

Casinos love it when someone comes in to play a system. They know they have found a good customer they will make lots of money from. I expect he will learn the hard way.

victor2

Another point with these "schemes" is that the roulette wheel has no memory, so what numbers came up in the past has absolutely no bearing on the future. If ten 17s have just come up, the chance of the next one being 17 are 1 in 37, just like they were for the previous ones.
As kavajo says, they just make the casinos happy. Casinos do understand probability and how to ensure everything is just slightly in their favour!

Blakespops

victor2 wrote: »

Another point with these "schemes" is that the roulette wheel has no memory, so what numbers came up in the past has absolutely no bearing on the future. If ten 17s have just come up, the chance of the next one being 17 are 1 in 37, just like they were for the previous ones.
As kavajo says, they just make the casinos happy. Casinos do understand probability and how to ensure everything is just slightly in their favour!

I agree I told him it is luck not skill.
he says the six of them split the combinations left based on something or another that I will find out next time i meet him. I was when i find out not only how many combinations of six numbers they are left but what they are is to split them into the lists between six players and watch that roulette on tv and prove to him it will not work.

I was going to do some macro to sort the numbers so it would give me the sets of six so I can print them. if not too many pages lol.

in answer to the person asking if he bets on all combinations no. they sit and watch what numbers are coming out on some of the tables as he says the guy or lady spinning the ball is the factor as from how the numbers are coming out he knows what ones to start with so as said all six bet six times some on same number as eachother. but as I say the only reason I am asking is it is bugging me to work out how to do the filtering not just why I wish to get the resulting numbers. I am getting a headache trying and failing. maybe as someone said earlier Excel may not be best place to try working this out but I am better at excel than spreadsheets so if failing on Excel would fail in heartbeat anyother way. I have spent a month trying to work it out before I asked for help.

Blakespops

Just got off phone with friend and he states that no more than four numbers are duplicated between the six of them and also no more than three bet on any single number. so that adds a little more to the filtering.

Makes it a nice puzzle to try get macro to sort for me but any help would be great. I can get it to give the sets of six but not any filtered. as I said I do not know how to add filters.

victor2

Blakespops wrote: »

no more than four numbers are duplicated between the six of them and also no more than three bet on any single number.

Not sure I understand that as they appear to contradict each other. Can a number be repeated in a group of six three or four times?

It's quite a challenge for Excel, any 6 from 37 numbers without any filtering applied and allowing for repetition gives just over 2.5 billion combinations. Of course, many of those would be the same 6 numbers, just in a different sequence, but I suspect you'll still end up needing quite a bit of paper to print the list on!:)