excel again :)

[nottophammer]

Badger and BBB
A mighty big thank you from Notto.

But had a problem deleting blank cells, in column H, the cells looked blank but the info bar showed data is there. So with help from overflow block, found the answer.
Had to use the find box, sorted.
Again thanks

[nottophammer]

Forgot Badger
I like Rik 3

[Bigbroben]

So we all get a thumb up... where?
Is this why the half-closed eye?

[Bigbroben]

So,

I thought I'd share some Excel features I learned a few months ago, for those who do not know yet.

Will start with macros.  This is a great tools that will let you run thousands of trials overnight as you sleep.  And it's easy.

First, decide of which informations are to be extracted or memorized.  In this example here, KTF, we will want to save :
1) Score ( or goal);
2) At which spin the goal was attained;
3) What maximal progression was needed;
4) What minimum br was reached before goal.


I like to put these infos all aligned on one row.  You'll see later why.
So, all nrs are entered, the game is played, you're ready to save the infos.  Ok.

The macro functions is in the View section:


We will record a new macro:


It is a first of three macros.  Let's call it ktfone (only letters are accepted to the macro's name), and choose the key K:


Now , it starts recording everything you will be doing.
So, KTF-one:
1) We will first select the cells containing the datas we want to keep, then right-click -> Copy (or just ctrl+c):


2) We then paste the value of the cells ( the 123 icon) in the row just below, so one right click in the leftmost cell below:


3)Ctrl+c (or right click -> Copy) again the newly ceated cells.
4) Insert copied cells a few rows below:

Slide them down:


We have now created the basics of our data extractor.  We now stop the recording:


From now on, anytime you press ctrl+k, these 4 operations will run automatically.
After 2:

See, the new datas are pushing the older ones lower.

Let's create a second macro called ktftwo, with key t:

This one also records everything you'll be doing ( only on your Excel file, though!).
So if you press ctrl+k 10 times then stop the recording, this new macros will run the first macro 10 times.


Let's create a third macro, ktfthree, key f:

Press ctrl+t then stop recording.
So by pressing ctrl+f, macro ktftwo will run.

Let's dive into the macro text itself.  Go to View Macro:


Choose ktftwo and edit:


Gets you here.  You see: ktftwo runs ktfone ten times.


You can simply select these 10 steps, copy them:

Click at the end of the 10th step, press enter.  Paste the copied steps on the line below:


Or, easier: ctrl+v, enter, ctrl+v, enter, ctrl+v, enter,...  I know you can enter at least 300 times the previous macro in this one.

Same for ktfthree:

We see it produces ''ktftwo'' once.
Same as above: copy this step as many times as you want, up to 300 times ( could be 400, not sure).

So, what do we have here?  A simple ctrl+f will run 300x300, 90k trials of whichever sim you want and whichever datas you want to save for further analysis.

I normally use Excel's random function, although some do not like it.

It simply creates a new set of nrs on any movement.  This ensures the nrs are changed from one data recording to another.

You can also record the first macro as such, if, say, you want to use nrs from random.org or whatever.  Make sure the nrs you want to use are available in another sheet (or same sheet, but specific column).


The macro would select the amount of nrs you want, copy paste them in your Nrs column, continue to record the operations as described in the first macro, delete (then shift-cell up) how many nrs you want, then stop the recording.
So, example, you have a 100k spins to source nrs from. 
Start recording, select a hundred, paste them in your nrs column, run your operations, go back to nr source, delete 25, stop recording.  A 100k nrs, if deleting 25 each time, will let you have 4000 runs ( actually, 3997).

********************
Now I did mentionned earlier that I insert the copied cells a few rows below.  Why is that?
It leaves room to add some other measurements that will automatically calculate what you want as the trils go by.

You can add the sum of your game score:


You see I selected not only the cells with a value in them, but also the one above.  This one is fixed, as opposed to the other ones that are moving.  Doing so will let the first measured cell remain the same (here, AA9), and the last measured cell to remain the last non-blank cell.  Otherwise, only 2 cells will be measured.  So as goes the macro, you can just observe how your score evolves, or how the average rises...




If you want to apply these calculations to the neighbouring columns, select the cells to be copied:


See that small square black spot on the lower right side of the selected cells?  Click on it, hold the button, drag:


Good enough for now.

************************

I might soon explain other little tricks for Excel, basics that are so helpful to know.

Stay tuned!

[Still]

That's perfect.  That's exactly what we need to gather empirical evidence whether or not there is a reason to have faith in such systems as Keep The Faith.  This should let people know exactly what they are getting into before they start. 

[Gitano]

Hi All,

I don't know if I should start a new thread but I have a little question:

can you help me to find Finals units in a list of number? There is formula in excel that works?

Example:

10 23 34 3 35   ----> 0,3,4,5     missing :1,2,6,7,8,9

I'm inventing a system that I like to play, and I'm trying to do a scheme in excel.
Thanks a Lot

Kind Regards

[Bigbroben]

Hi All,

I don't know if I should start a new thread but I have a little question:

can you help me to find Finals units in a list of number? There is formula in excel that works?

Example:

10 23 34 3 35   ----> 0,3,4,5     missing :1,2,6,7,8,9

I'm inventing a system that I like to play, and I'm trying to do a scheme in excel.
Thanks a Lot

Kind Regards

I think it's ''RIGHT'' function.  U can google this.  Just learned about this one lately.  Like '' =right(b5,2) '' will gove you the last 2 digits of cell b5.  Never used it but i think its really easy.  Ask id you need help.

[Bigbroben]

Here you go, LEFT or RIGHT function.

[Gitano]

Hello Bigbroben,

thank you for the suggestion; I have been able to find finals present in ten numbers.


Now I'm trying to list the missing finals in other cells with the strange formula :

= SMALL(IF(ISNA(MATCH(ROW(A$0:A$9);A$0:A$9;0));ROW(A$0:A$9));ROW(A1))

that return the missing finals..but does not work!

[Gitano]

this could help:

"Sometimes it requires not only identifying missing numbers sequence, but also listing missing numbers too. You can deal it with following steps:

1. in the adjacent cell, please enter the formula = SMALL(IF(ISNA(MATCH(ROW(A$1:A$30),A$1:A$30,0)),ROW(A$1:A$30)),ROW(A1))

A1:A30 = range of numbers, the sequence to check against is from 1 to 30

2. Press the Ctrl + Shift + Enter Keys together to finish the formula. Copy down the formula until you get #NUM! errors meaning all missing numbers have been listed. See screenshot:







"link:s://:.extendoffice.com

[Bigbroben]

Just explain what you want to do exactly: you want to play the missing finals until there are none left, or until profit, or?

The RIGHT or LEFT function returns a value that is considered a text.  To make it a nr, example, last digit from cell A3,type =VALUE(RIGHT(A3,1).

I do not uderstand what the equation you copied represents.  It is veyond what I know so far.

See attached: I prefer keeping it simple and use 10 cells instead of one that contains a way too long equation.  It tracks how many of each final happened since 1st spin.

Check it, tell me if you need more parameters.

[6th-sense]

heres one i had knicksmi make me a few years ago also look at the options to bet neighbours..
balance bets and everything

also gitanto in 10 numbers too

[Gitano]

Hi All,
Hi Bigbroben,

I show you my system in the excel attached.

I don't want to play finals until them appear because is not adviseable in roulette..


but , as you can check in the example here, each -ten- spins I recalculate the missing finals and bet the new missing finals number all, for three times only; After that I continue wait for seven spins and recalculate the missing finals and bet 3 times and so on... 

So, I identified ok with the RIGHT excel formula the finals appeared , now I would show in a cell the missing ones and this is the problem. Maybe with the MATCH formula ?

Take the example only as an example to understand the system..because I want to do it more light and smart

thanks

G

[Gitano]

Hi Guys,
Thank You Bigbroben
Thank You 6th-sense

I was able to complete this file but, now I'm stuck at AI column, where I would to show only a WIN although there could be 1, 2 or 3 winnings..Doing that I will be able to count how much winnings and losses in a session.
Do you have an idea ?

Important note :   The scope will be count how many "W" and "L" with 3-finals or 4-finals betting I have

I will play only when I have 3-finals miss (for 3 times) with a progression! check this and you'll be surprise!

[Nickmsi]

Hi Gitano,

Attached is a simple way to determine W or L in column AI.

Cheers

Nick