so I am a complete excel and math noob and I want to have a cell in excel which will display the "Pelayo number", which is used in calculating bias in a roulette wheel. You can read more about it here: https://www.roulette-bet.com/2015/06/the-roulette-bias-winning-method.html
Let me explain briefly what I want. As you can see on the image there are two columns, in one there are the numbers on a roulette wheel and and in the second one there is the frequency of each number. On top you see number of spins (852). The number on the bottom (23,02.....) is the expected frequency of each number. The table is dynamic, constantly evolving as I enter new data.
Now I want a cell to display the total number of positives. Which is calculated like this:
If there have been 300 spins, each numbers has to have been spun 300/36 = 8.33 in order to be breaking even. This means those which have been spun 8 times are losing a little, and those which have showed 9 times are winning something. If a number has appeared 14 times it is clear it has 14-8.33 = 5.67 which we will express in an abbreviated form like 5. Let’s suppose the exact same situation has occurred for 6 other numbers also, they all will make a total sum of 5.67 5.67 5.67 5.67 5.67 5.67 5.67 = 39.69. as no other number has been spun over 9 times, then we say the amount of total positives at this table at 300 spins is 39.
TLDR So ideally something like: Select all the numbers from (G6:G42) which are bigger than value in (G50) and then substract them one after another from the expected frequency (G50) and then add this all up.
I tried to solve it but just couldnt find a tutorial anywhere
CodePudding user response：
I'll break this down for you, and show you a few helpful Excel concepts along the way.
Especially if you are a beginner, I'd recommend using a helper column. Helper columns are great ways to break down complicated functions into smaller, more manageable parts.
In H6, write =IF(G6>G$50,G6,0). That if statement will set us up for our sum, with either the value in G6 or a 0. The $ will be cleared up in a moment.
Then, hover your mouse over that cell, and a little square box will appear in the lower right corner of H6. Grab that tiny box, and drag it down to H42. This fills in the formula, adjusting all of the numbers relatively as you go. Note that the 50 stayed constant, however - that's what the $ did!
H6 is now your helper column. It doesn't find your answer, but it gets an important, intermediary step done.
Finally, wherever you'd like your answer, write =SUM(G6:G42), and you should be well on your way.
CodePudding user response：
=SUMIF(G6:G42,">"&G50,G6:G42)-COUNTIF(G6:G42,">"&G50)*G50
It sums values that are over in G50 then distracts G50 value as much times as there were values to sum up to.
For example in case G50 is 23.02 and you have values 20, 21, 22, 23, 24, 25.
It would calculate like (22 23 24 25)-4*23.02