excel again :)

[Nickmsi]

Hello Bigbroben,

I found the hardest and most time consuming to code was the Straight Numbers so I coded Standard Modules for each numberset.

I have attached one of the modules for Straight Numbers showing how I Track and make Bet Selections based on Tracking the last 37 spins on a rolling basis.

The Bet Selection is simply betting all unhits after 20 spins.

This is just one way of doing it but I hope the code might give you some ideas and save you some time and effort.

Cheers

Nick

[Anastasius]

Bigben one more ahha

U start tracking
Then once  u see first  repeater. Flat bet it until any number triples .or it hit. Thats it no trigger.  Then start  new game.. 

[Bigbroben]

Straight-ups are rather easy to deal with.  It'S the mix of splits and corners that are harder.

As for striaght-ups, or any category of bet, here is how I apply the reference cell.

Cell B3 is the one giving the parameter to consider ( here, it's the backtrack length).

Then all nrs or splits or corners are assigned a nr written on top of the sheet (here, row3). 


We'll check the formula for cell CJ4


Part1: IF($E4="",""
Column E is where nrs are written in this sheet. If there is no nr written in the E4 cell, (if(E4=""), then put nothing in this cell.
Dollar sign before E ($E4) will let you block the reference column if you happen to copy/slide the formula to the other nr columns.

Part2: IF($D4<$B$3,0
Column D is the spin nr.


So, if not enough spins have gone by, less than the tracking length, I ask it to write zero, otherwise it will call #REF!
$ in front of D4 to keep the reference column, and $ in front of both B and 3 to keep the one cell as reference ( same column, same row).

Part3:
If (COUNTIF(OFFSET($E4,-($B$3-1),0,1,1):$E4,CJ$3)=0,1,0
Goal here is to see if the nr associated with the CJ$3 cell appeared in the tracking window.
Normally, it would be COUNTIF($E4:$E4,CJ$3, which would return how many times the nr in cell CJ3 appeared.  Here, if the result =0, write 1( we want to play the nrs that have not hit in the last 25 nrs).  The 0 in the end is the value to enter if all predecent conditions are false.
OFFSET is very useful when needing to have a varying tracking window.
Denomination is OFFSET(reference, row, column, height, width)
So, we want to know here, for example how many times the nr appeared in a 25 spins window, in cell CJ4.
To make it a 25 spin window, we make the reference as $E4, then we want to backtrack 24 cells up, which will give 24+1 cell to track.
So: OFFSET ($E4 = reference
OFFSET($E4, -($B$3-1)  minus B3 cuz we are going up, not down, and minus 1 cuz reference is 25 and we already have one with E4
OFFSET($E4,-($B$3-1),0,1,1): no change in the column reference, 1cell height, 1 cell width

With this setup, just to modify the value in cell B3 will change the range of the tracking.
With the proper use of $ signs, you can simply click on the cell, click on the little black square bottom right of the cell, hold-drag to all nr columns further right and all the new cells will have the formula copied onto them.  If double click on the square after dragging to the whole row, it will copy the formulas to the lower cells all the way down, provided there is a neighbouring column with inputs.

[Bigbroben]

Bigben one more ahha

U start tracking
Then once  u see first  repeater. Flat bet it until any number triples .or it hit. Thats it no trigger.  Then start  new game.. 

Here.

You want more features, stats, parameters?

[Bigbroben]

Or this one here for a continuous game in 500 spins.

[Bigbroben]

But then, when playing and adding the nrs in ther sheet, you don't want to scroll all the way to the right, find where the little ''1'' is, look for which corresponding nr it belongs to, then scroll back and bet.  You want to have the numbers displayed handily for quick action.

How to go from 37 columns with 1s and 0s to a display like this one below?


It requires 3 blocks of 37 columns:
1) the first was described earlier today
2) lookup block
3) display block

LOOKUP block:
LOOKUP function finds the value you indicate in a first column and return the value in the same row in a second reference column  (you can decide to reduce the lookup to a few cells, but for practical reasons I like to use the whole column).

Ex: for cell F4, we ask lookup to find the value of E4 in column A and return the corresponding value from the B column:
=LOOKUP(E4,B:B,C:C)

N.B: the first column to look into must have its values in an ascending order, sonst it will return the wrong value.

So, the second ''LOOKUP'' block will be used as a reference for the third block.
The first row of the 37 will have a value of 1.  The following will be as such:


with a 38th column using the same formula for LOOKUP's way of reasoning.  If it looks for nr 4 and there are 2x nr4 in the first search vector, it will take the furthest corresponding value of them all, so the last 4.  The 38th column is to include nr36 in the search.  This second block is to build an ascending streak of nrs that the LOOKUP function will pick from.


So, if nr0 (cell CI40) in the first block is a 1, be 1u higher that the cell on the left (AW40), otherwise, same value as AW40.

Click and drag to the cells in the block, double click on the black square to copy them all the way down.

3)DISPLAY Block:

It all comes finally to this one.


LOOKUP will find the value of the nr in row 3, so this row is just a reference. ''1'' means: ''first nr to play is...''  so not the nr itself.
So, this part here:


In this example here, there are 15 nrs played.  The 38th column in the second block returns a value of 16, so:
IF(L$3>=$CH39,""
You don't want the last nr played to repeat over and over in the display, or have a false bet.  So, if the reference nr (L3) is bigger or equal to 16, empty ('''')

(Ah? Why CH39 and not CH40 if we are on cell L40?  Because we want to see the nrs to play before the spin is made, right?

Right.)

Notice the $ signs where they are:
LOOKUP(L$3,$AW39:$CG39,$AW$3:$CG$3
The nr to look for is the one in L3 (1).  For row 40, you find it in the row 39.  The $ is before AW and CG since this is the columns to look for for all the other columns in the third block.
The returned value is always in the Row3, where the played nrs are associated, hence the $ before the 3.

Click drag copy to all other cells in the 3rd block and you should be good.

Put the 3rd block near where you write the nrs for ease of reading.

[Anastasius]

Broben. Forget coding the single number thing. Bet repeats from only on spin 19 to spin 28 now . Start tracking again  if no hit on spin 28 and also reset tracking if a triple hit before spin  19. I think i can do something with the info and maybe a progression lol

[Anastasius]

Broben with  spreadsheet when i set my own parameter  .it isnt doing the automatic change if numbers. Is there a button to make it randomly select a new 500 numbers   

[Herby]

Press F9 on keyboard

[Anastasius]

I will when on pc thanks! Is there a way on my phone for now if not its cool.

[Bigbroben]

I will when on pc thanks! Is there a way on my phone for now if not its cool.

I would think if you ''edit'' a cell, such as double clicking on it then another one, it should work.  It work on a computer, maybe on a phone too, not sure.

[Anastasius]

master of excel... how can i alter the spreadhseets to make them have more spins or is it impossible

[Bigbroben]

link:s://:.rouletteforum.cc/index.php?topic=24992.msg221375#msg221375

Page2

[Gitano]

Hi All,

I'm here again with a question and trying to develop an excel file to study my statistics.

Just to start today I need a button in excel that in a list of spins, let's say 10 spins, leave only 4 to 10.
and so on, the button should put off in a click all the 1º,2º,3º, 11,12,13 21º,22º,23º spins. And leave a list without blanks only with 4º5º6º7º8º9º10º,14º15º16º...and so on.

I found this but it erase only the first three numbers: WHyyy!?

Sub deleteMultipleRows()

     Worksheets("Delete row").Rows("1:3").Delete
    Worksheets("Delete row").Rows("11:13").Delete
    Worksheets("Delete row").Rows("21:23").Delete
    Worksheets("Delete row").Rows("31:33").Delete
End Sub



After that button I could create another button to put off the 8 9 10º... and remain with 2 lists of spins reorganized..and there put these on my system that I will tell you.


Thanks a lot.

Gitano

[Gitano]

I found this; I will try to modify it in order to cut first 3 numbers and 11,12,13º...21,22,23º and so on.

Code Select Expand

Sub Delete_Alternate_Rows_Excel()
    Dim SourceRange As Range

    Set SourceRange = Application.Selection
    Set SourceRange = Application.InputBox("Range:", "Select the range", SourceRange.Address, Type:=8)

    If SourceRange.Rows.Count >= 3 Then
        Dim FirstCell As Range
        Dim RowIndex As Integer

        Application.ScreenUpdating = False

        For RowIndex = SourceRange.Rows.Count To 1 Step -2
            Set FirstCell = SourceRange.Cells(RowIndex, 1)
            FirstCell.EntireRow.Delete
        Next

        Application.ScreenUpdating = True

    End If
End Sub