Simple statistical analysis in excel.
Problem: Excel sheet. Edited Job description as a bit confusing for some coders.
Column A1:A1000 contains formula =RANDBETWEEN(1,9)
Column B1:B1000 contains formula =IF(AND(A1>3,A1<9),"X","Y")
Obviously since numbers in column A are random the sequence in column B will contain X & Y's randomly.
What I need to know is the frequency of concurrent X's and concurrent Y's starting with 1 in column B.
For example, 200 single X's. i.e. X preceded by and followed by a Y - YXY
45 concurrent XX, i.e. YXXY
19 concurrent XXX, i.e. YXXXY
and so forth for both X and Y values.
just to be clear as some of coders have misunderstood my requirements. Maybe because I am not good at explaining what I need. I have attached a revised excel book.
The column A is meant to be random. It is in fact the number of horses in a race. I am trying to calculate the most effective betting strategy based "partly" on randomness as predicting the winner when the horses don't even know who will win is challenging.
Column A - In races with declared 5-7 runners the horses in 1st and 2nd place win on a place bet. (This is even if some horses get withdrawn before the actual start of the race, so there might be only 6 or less in the actual race). Column B simply checks and displays the winner/loser from column A. I have increased spread due to a number of other factors, i.e. I will be betting on the favourite that statistically has a 30% of coming first so the odds are not simply 2 in 7 but closer to 50/50.
Column D - In races with declared 8-9 runners the horses in 1st and 2nd and 3rd place win on a place bet. (This is even if some horses get withdrawn before the actual start of the race, so there might be only 8 or less in the actual race). Column E simply checks and displays the winner/loser and I have increased spread due to a number of other factors, i.e. I will be betting on the favourite that statistically has a 30% of coming first.
So the complete column A or D gives me 2 lists of winners/losers. From this list I wish to know the patterns i.e. the concurrences of X and Y's.
I want to be able to then redo this so that I get a different list of winners/losers repeating this process N times. This is because I want to get as close as possible to the Average patterns or concurrences.
Instead of flat betting on each race, i.e. 1 unit of currency. I bet based on the likely patterns, Staking 1,9,5,3, i.e. if a losing bet start the series of bets from 1, if a winner step up 1 in the series, if at end of series repeat series.
Because the winners/losers do not alternate in a regular pattern but randomly, by adapting the betting pattern to closely mimic as far as possible the likely pattern makes a huge difference and can turn a losing system into a winning one. For example to break even I might need to win (on average) 72% but if I can win 80% that edge based on appropriate staking and a percentage based bank is sufficient to create a winning system.
hope this clarifies.