Forum Discussion
Roulette Tracking
I have a spreadsheet where I count the results of spins on a Roulette wheel. How can I track the number of spins since the last time a specific result occurred? For example, how many spins has it been since the number 7 came up or how many spins since an odd number?
You could help us help you by giving a bit of basic information on how you track the results in the first place. What sort of records do you have which could serve as the source information for answering the question you have?
Otherwise you're, in effect, asking us to do the whole process of designing the initial tracking system. And we might do something altogether different from how you approach this.
Thank you so much for your response! Below is a screenshot of my tracking sheet, I input the spin results in column N (next result to be input into row 24) and the rest of the sheet displays those results as they pertain to different bets/layouts on the roulette board. The top of the sheet is frozen (row 8 and above) and the data field runs downward for 500 results.
My goal is to have numbers in each of the column headings (columns O to AF in this example) that reflect the number of spin results since each occurred.
The top left obviously shows the layout of the numbers on a roulette board and I would like to replace those numbers with the information 'number of spins since last occurrence', example; instead of showing the number 7 (D6), I would like to display the number of spins since 7 was the result.
I appreciate your help!
=SMALL(IF(ISBLANK(N9:N10000),ROW(N9:N10000)-8),1)-LARGE(IF(N9:N10000=7,ROW(N9:N10000)-8),1)
You can apply this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
This is, frankly, playtime! It uses range composition to build subsets taken from the results range. Sample worksheet formulas are
= LET( Fnλ, LAMBDA(x, x), MAP(result, SpinCounter(Fnλ)) ) and = LET( Fnλ, LAMBDA(x, IF(x, ISEVEN(+x), "zero")), MAP(result, SpinCounter(Fnλ)) )which give results for the winning number and even/odd respectively.
A function Fnλ should be defined to return a unique value for each category of interest, even/odd, black/red etc. MAP passes one cell at a time to SpinCounter(Fnλ).
SpinCounter = LAMBDA(Fnλ, LAMBDA(current, // "Fnλ is a function that maps results to distinct roulette categories" // "current is a specific cell taken from the array of results" LET( //"Define results up to but not including the current cell as a range" priorResults, DROP(top:current, -1), //"Look up cell mapping within the mapped array" priorMatch, XLOOKUP(Fnλ(current), Fnλ(priorResults), priorResults,,,-1), //"Count the results between the current cell and its matched prior cell" count, COUNT(priorMatch:current), IF(count, count - 1, "") ) ))There is more to do, but that will have to be another day!
A copy of the Excel 365 file.
Played around a bit further with formulas to determine where a number is red or black and count offsets to prior occurrence.
To return the color = RedBlackλ(result) and the count = MAP(result, SpinCounter(EvenOddλ)) where RedBlackλ = LAMBDA(number, LET( iseven?, ISEVEN(+number), isRed?, IF(number, IF(1+MOD(number-1, 18) > 10, iseven?, NOT(iseven?)), "zero"), SWITCH(isRed?, TRUE, "red", FALSE, "black", "green") ) );
