Help Please:CALCULATING AND USING STDEV.S

I seek a little maths help for my rusty stats knowledge.

I play a certain online 'Carnival game' simple enough game.

There are three ordinary dice rolled and there is a simple paytable for each of the outcomes 1,1,1 =3 through to 6,6,6=18. There are wagers possible on Low = 1 to 9 or High =12 to 18, or any pair or any triple.

Now, the RTP from that paytable, I calculate as about 75% in its own right. But there is a bonus that brings RTP up to a stated 96%. The nature of that bonus is that arbitrary multipliers get applied to a random selection of the wager types.

E.g Low (1-9) Normally pays 2 x wager but randomly it might be 4 x wager or even 10 x wager.

Now to the question: I have taken a selection of sequential returns on a specific ONE unit bet and popped them into Excel. E.g say I was betting 1 unit on Low. The list of outcomes was like -1 -1 +1 -1 +1 -1 -1 +3 (My list is longer)
I then applied the Excel function stdev.s() to that range and got 1.512. The mean for that list of values was 0. I.e Profit=0 and loss=0 over that small sample.
I know from the stated RTP for the game that there's an expected loss of 4%, so Population Mean would be -0.04

I aspire to know what will be the 1SD range of outcomes if I place X wagers of $Y

This is what I think I need
Expected profit = -0.04 x Total wagered (From stated RTP of 96%)
For 1SD confidence interval, SD=X^0.5 x 1.512Y
If I make 5000 wagers of $1
Expected Loss = 5000 x 0.04 = $200
SD = 5000^.5 x 1.512x1 = 106.9

So, by my reckoning, 68% of the time I would lose $200 +/- 107 or somewhere in the region Loss 307 to Loss 93

Why am I doing this? I have a Buy In bonus that makes it definitely +EV for me, but I aspire to not have lots of losing days to the variance. The more, smaller bets I make, the less risk of losing a session, but the more bets I make, the longer it takes. I'm looking for the sweet spot where I probably win SOMETHING every day and end the month close to expected profit.
So far, I've wagered typically $10 per spin and session outcomes have given profit between about -$700 and +$600 which seems reasonable.

So. Is my use of STDEV.S correctly applied?
Is the Big SD formula correct. SD= sqrt(Wager Count) x WagerSize x stdev.s(My sample)

TIA
OD

Calculating SD using empirical data is definitely valid for an unknown distribution. (I neither confirm nor deny for calculation of such.)

In this case, however, an educated guess at the multiplier distribution shouldn’t be too tough, as there are only 3 possibilities. (Did I understand you correctly?)

Since your data collection is on a “to 1” basis, my calculations are as well.

Confirming your baseline figure of 75% is simple enough, as 1 * 37.5% + (-1) * 62.5% = -25%, and 75% “for 1” is equal to -25% “to 1”.

Letting X equal the average multiplier applied to wins and setting the above equation to -4% gives the following:
X * 37.5% + (-1) * 62.5% = -4%

X = (62.5% - 4%) / 37.5% = 1.56

Next, we need to come up with a three-way ratio for the 1X, 3X, and 9X (to 1) payouts such that they result in an average multiplier of exactly 1.56.

I have calculated such ratios assuming that the 3X multiplier is either 2, 3, 4, 5, or 6 times as likely as the 9X multiplier. The most likely of these is 21:3:1, as the numbers are “nice”, however, it all depends on how well (or if) any of them match the data.

1X:3X:9X
129:14:7
21:3:1
165:28:7
183:35:7
201:42:7

The key is having enough data to have confidence in your 3X:9X ratio… Note that other 3X:9X ratios could be considered, such as 3:2 or 5:2, 7:3 or 7:4, etc… Once you have matched a three-way ratio to the data, calculating the SD should be pretty straightforward. Just be sure to stick with the “to 1” notation…

I have included the improper ratios below in case they are easier to read.

1X:3X:9X
18-3/7:2:1
21:3:1
23-4/7:4:1
26-1/7:5:1
28-5/7:6:1

Just thought this might assist when comparing to the data…

Thanks,
to be clear I popped 100 Actual results into Excel and used the STDev.S function to do all the calculating.

I took the RTP value as in the game's help page. So, I had Population Mean from the former and empirical sd from the latter. I took no interest in the multiplier, just on the payout outcome.

Quote: OnceDear

…I took no interest in the multiplier, just on the payout outcome.
link to original post

If I understand this correctly, I assume that you are saying you considered any win as +1 in your Excel calculation, as you don’t want the multiplied wins to overstate the StDev.

In that case, we can calculate the StDev from the base game info, as follows:
Var(base) = 0.375 * [1 - (-0.25)]^2 + 0.625 * [(-1) - (-0.25)]^2 = 0.9375
StDev = Var^0.5 = 0.9682458366…

While this seems close to your 1.5xx figure, you may notice that it’s actually about 2/3 the size. You could potentially bet ~1.5 times more with the same risk that you calculated in the OP, while saving about 1/3 of your time.

Quote: camapl

Quote: OnceDear

…I took no interest in the multiplier, just on the payout outcome.
link to original post

If I understand this correctly, I assume that you are saying you considered any win as +1 in your Excel calculation, as you don’t want the multiplied wins to overstate the StDev.

In that case, we can calculate the StDev from the base game info, as follows:
Var(base) = 0.375 * [1 - (-0.25)]^2 + 0.625 * [(-1) - (-0.25)]^2 = 0.9375
StDev = Var^0.5 = 0.9682458366…

While this seems close to your 1.5xx figure, you may notice that it’s actually about 2/3 the size. You could potentially bet ~1.5 times more with the same risk that you calculated in the OP, while saving about 1/3 of your time.
link to original post

Thanks for looking.
I know the non-multiplied results, we can calculate the SD. but I wanted the overall SD with the multiplier.
I was wagering All outcomes simultaneously, The objective to plough through some wagering requirements with Min variance. I was happy to lose 4% of my coin in.

No. I wanted the effect of the multiplied payouts to be used in deriving Sample SD
So if the multiplier was 4x and the profit was 3, then that's what went into the array of outcomes. A bit like the SD for BlackJack is >1 because some wins and losses can be >1

In the actual list of results, I was using simultaneous wagers of £2 on low(1-9), £2 on high(12 to 18), £0.40 0n 10, £0.40 on 11
Total session wager of £4.80
Possible outcomes without multiplier were
Low: Paid £4 giving a loss of 0.80 on the round
High: Paid £4 giving a loss of 0.80 on the round
10: Paid £2.00 giving a loss of £2.80 on the round
11: Paid £2.00 giving a loss of £2.80 on the round
Without multiplier, I would lose on every round.
With multipliers there were some rounds with profit of £3.20, £11.20, £15.20

In excel, I normalised down by dividing the 'profit' by my common session wager of £4.80

So, normalised outcomes were...
0.8, -0.2, -0.7, -0.2, -0.7, -0.2, -0.2, -0.2, 1.3, -0.7, -0.7, 3.8, -0.2, -0.2 ...

My sample size SD came out as 0.99565 The 1.5 number quoted earlier was an error.