Excel Calculation in Detail¶
This page explains each sheet, each block and each formula of the workbook excel/TacklinTheKraken_TheoCalc.xlsx.
The page Excel workbook gives the short instructions. This page gives the full calculation.
The cell addresses and the formula texts on this page come from the workbook file. The numbers are the results for math version 2.0.0.
1. Structure of the calculation¶
The workbook has 12 sheets. The data moves in one direction, from the inputs to the sheet Summary:
Config Reels_Base Reels_Bonus inputs (yellow cells)
|
v
Win_Base Win_Bonus symbol counts for each stop
|
v
Ways Fish Trigger expected values, probabilities
|
v
Bonus free spins
|
v
Summary RTP, key figures, checks
Distribution simulation values (separate)
The diagram shows the sequence of the calculation. The table gives the exact sources of each sheet.
| Sheet | Reads from | Gives |
|---|---|---|
| Config | no other sheet | All number inputs |
| Reels_Base, Reels_Bonus | no other sheet for the strips. The check cells read the symbol list and the placement rules of Config, and the FS column of the Win sheet. | The reel strips and the reel strip checks |
| Win_Base | Reels_Base | The number of each symbol in the window, for each stop of each base reel |
| Win_Bonus | Reels_Bonus | The same for the bonus reels |
| Ways | Win_Base, Win_Bonus, Config (paytable, bet) | Expected ways pays for the base reels and for the bonus reels |
| Fish | Win_Base, Win_Bonus, Config (fish values, weights, bet) | Expected collect for the base game and for each level |
| Trigger | Win_Base, Config (initial free spins) | Probability of 3, 4 and 5 FS symbols |
| Bonus | Win_Bonus, Ways, Fish, Trigger, Config (levels, multipliers, multiplier rule, bet) | Level probabilities and the expected value of the free spins |
| Summary | Ways, Fish, Trigger, Bonus, Reels_Base, Reels_Bonus, Config (bet) | RTP, key figures and checks |
| Distribution | no other sheet | Win distribution tables from the simulation |
| ReadMe | no other sheet | Short instructions |
The calculation has these steps:
| Step | Sheet | Function |
|---|---|---|
| 1 | Config, Reels_Base, Reels_Bonus | Inputs. You change the yellow cells. |
| 2 | Win_Base, Win_Bonus | Count each symbol in the window, for each stop of each reel. |
| 3 | Ways | Expected ways pays for each pay symbol. |
| 4 | Fish | Expected KEY and FISH collect for the base game and for each level. |
| 5 | Trigger | Probability of 3, 4 and 5 FS symbols. |
| 6 | Bonus | Probability of each level and expected value of the free spins. |
| 7 | Summary | RTP, key figures and checks. |
All results are formulas. The workbook has no macros and no values that a person must copy.
Conventions¶
- Colours. Yellow cells are inputs. Grey cells are fixed by the layout. Green cells are results or checks.
- Units. All values are credits. The RTP is the expected win divided by the bet.
- Stops. Stop
sshows strip positionss,s+1,s+2ands+3. Each stop has the probability 1/100. - Division by 100. A formula that divides by
Config!$B$6calculates a mean or a probability for the 100 stops of one reel. - Fixed layout. The workbook is made for 5 reels, 4 rows and 100 symbols for each reel.
Defined names¶
| Name | Refers to | Meaning |
|---|---|---|
Bet |
Config!$B$3 |
The bet in credits |
Paytable |
Config!$B$11:$F$20 |
The paytable values |
FishMultiplierRule |
Config!$B$68 |
The text all or fish |
TotalRTP |
Summary!$C$9 |
The total RTP |
2. Sheet Config¶
This sheet holds all number inputs.
| Block | Cells | Contents | Type |
|---|---|---|---|
| Theoretical RTP | B1:C1 |
The label and the total RTP of the workbook. C1 is the formula =Summary!$C$9. You see the result of a change on the same sheet as the inputs. |
Formula |
| Bet | B3 |
Bet in credits | Input |
| Layout | B4:B6 |
Rows, reels, symbols for each reel strip | Fixed |
| Paytable | B11:F20 |
One row for each pay symbol. Columns B to F are 1 to 5 of a kind. Credits for one way. | Input |
| Symbol list | H11:J27 |
The 17 symbol names, their class, and the flag "special". The reel sheets use this list. | Fixed |
| Fish values | B25:E28 |
One row for each FISH symbol. Up to 4 values. An empty cell means "no value". | Input |
| Fish value weights | C33:F52 |
One row for each mode and FISH symbol. Weight n belongs to value n. Rows are in groups of 4: base, level 1, level 2, level 3, level 4. | Input |
| Weight sum and mean | G33:H52 |
Sum of the weights and the mean value (from the sheet Fish). For information. | Formula |
| Free spins trigger | B57:B59 |
Initial free spins for 3, 4 and 5 FS symbols | Input |
| Levels | B63:D66 |
KEY threshold (column B), spins (column C), multiplier (column D). Level 1 has the text "initial" for its spins. | Input |
| Multiplier rule | B68 |
all: the multiplier applies to ways pays and collect. fish: collect only. |
Input (list) |
| Placement rules, base reels | B73:F79 |
One row for each special symbol, one column for each reel. 1 = permitted, 0 = not permitted. | Input |
| Placement rules, bonus reels | B82:F88 |
The same for the bonus reel strips | Input |
| Reel strip rules | B90:B91 |
Number of pay symbols between two special symbols, and the largest number of AUS symbols in sequence. The reel sheet checks are made for these values. | Fixed |
The placement rules do not change the RTP. They only control the checks on the reel sheets.
3. Sheets Reels_Base and Reels_Bonus¶
The two sheets have the same layout. Reels_Base holds the base reel strips. Reels_Bonus holds the bonus reel strips.
Reel strips¶
- Cells
B4:F103hold the 100 symbols of reels 1 to 5. Row 4 is stop 0. Row 103 is stop 99. Column A shows the stop number. - Each cell has a list of the permitted symbol names. A special symbol has a blue background.
- Rows 104 to 106 are the wrap rows. They repeat the first 3 symbols of the strip, so that a window at the end of the strip can read its symbols in one range:
Reels_Base!B104: =B4
Reels_Base!B105: =B5
Reels_Base!B106: =B6
Check columns¶
| Columns | Rows | Contents | Formula (reel 1, first row) |
|---|---|---|---|
H:L |
4 to 106 | 1 if the symbol is a special symbol | =IF(COUNTIF(Config!$H$21:$H$27,B4)>0,1,0) |
N:R |
4 to 103 | 1 if the gap rule is broken: the symbol is special and one of the next 3 symbols is special | =IF(AND(H4=1,(H5+H6+H7)>0),1,0) |
T:X |
4 to 103 | 1 if the symbol name is not in the symbol list | =IF(COUNTIF(Config!$H$11:$H$27,B4)=0,1,0) |
AM:AQ |
4 to 106 | 1 if the symbol is an AUS symbol | =IF(COUNTIF(Config!$H$16:$H$20,B4)>0,1,0) |
AS:AW |
4 to 103 | 1 if this position starts more than 3 AUS symbols in sequence | =IF(SUM(AM4:AM7)=4,1,0) |
The gap check and the AUS check read the wrap rows. Thus they are also correct at the end of the strip.
Symbol counts and checks¶
| Cells | Contents | Formula (first cell) |
|---|---|---|
Z4:Z20 |
The 17 symbol names | text |
AA4:AE20 |
Number of each symbol on each reel strip | =COUNTIF(B$4:B$103,$Z4) |
AA21:AE21 |
Number of symbols on the strip | =SUM(AA4:AA20) |
AG4:AK20 |
1 if a special symbol is on a reel where the placement rules do not permit it | =IF(AND(AA14>0,Config!$B$73=0),1,0) (row 14, FISH1) |
AA24:AE24 |
Check: the strip does not have 100 symbols | =IF(AA21=Config!$B$6,0,1) |
AA25:AE25 |
Check: number of stops that break the gap rule | =SUM(N4:N103) |
AA26:AE26 |
Check: number of unknown symbols | =SUM(T4:T103) |
AA27:AE27 |
Check: number of placement rule violations | =SUM(AG4:AG20) |
AA28:AE28 |
Check: number of stops that show two FS symbols | =COUNTIF(Win_Base!$R$4:$R$103,">1") |
AA29:AE29 |
Check: number of positions that start too many AUS symbols in sequence | =SUM(AS4:AS103) |
AA30 |
Result of all checks: OK or CHECK |
=IF(SUM(AA24:AE29)=0,"OK","CHECK") |
A check value of 0 means "no violation". The checks do not stop the calculation. The RTP formulas use the strips as they are.
4. Sheets Win_Base and Win_Bonus¶
These helper sheets are the base of all subsequent formulas. One row is one stop. One column is one symbol on one reel. The cell gives the number of that symbol in the 4-row window at that stop.
- Row 3 holds the symbol names. Rows 4 to 103 are stops 0 to 99.
- Each reel has a block of 17 columns. The sequence of the symbols in a block is: PIC1, PIC2, PIC3, PIC4, PIC5, AUS1, AUS2, AUS3, AUS4, AUS5, FISH1, FISH2, FISH3, FISH4, KEY, WILD, FS.
| Reel | Columns | First column (PIC1) | KEY column | WILD column | FS column |
|---|---|---|---|---|---|
| 1 | B to R | B | P | Q | R |
| 2 | S to AI | S | AG | AH | AI |
| 3 | AJ to AZ | AJ | AX | AY | AZ |
| 4 | BA to BQ | BA | BO | BP | BQ |
| 5 | BR to CH | BR | CF | CG | CH |
Each cell counts one symbol in a range of 4 cells on the reel sheet:
Win_Base!B4: =COUNTIF(Reels_Base!$B4:$B7,B$3)
Win_Base!AX4: =COUNTIF(Reels_Base!$D4:$D7,AX$3)
Win_Base!CE103: =COUNTIF(Reels_Base!$F103:$F106,CE$3)
The last example is the last stop of reel 5. Its range goes into the wrap rows.
From these counts, the other sheets get three types of results with three formula patterns:
| Result | Formula pattern |
|---|---|
| Mean number of a symbol in the window | SUM(column) / 100 |
| Probability of exactly k symbols in the window | COUNTIF(column, k) / 100 |
| Mean of the product of two counts on the same reel | SUMPRODUCT(column 1, column 2) / 100 |
5. Sheet Ways¶
The sheet has two blocks with the same layout. Rows 5 to 14 are the base reel strips. Rows 20 to 29 are the bonus reel strips. One row is one pay symbol.
| Columns | Contents | Formula in row 5 (PIC1, base reels) |
|---|---|---|
B:F |
M(r): mean number of the symbol or WILD in the window of reel r | =(SUM(Win_Base!$B$4:$B$103)+SUM(Win_Base!$Q$4:$Q$103))/Config!$B$6 |
G:K |
Z(r): probability that the window of reel r shows no such symbol and no WILD | =SUMPRODUCT(--((Win_Base!$B$4:$B$103+Win_Base!$Q$4:$Q$103)=0))/Config!$B$6 |
L:P |
Expected number of ways for exactly 1, 2, 3, 4 and 5 of a kind | =B5*C5*D5*J5 (column N, 3 of a kind) |
Q:U |
Paytable values from the sheet Config | =Config!$D$11 (column S, 3 of a kind) |
V:Z |
Expected value for each of a kind number = expected ways x pay | =N5*S5 (column X, 3 of a kind) |
AA |
Expected value of the symbol | =SUM(V5:Z5) |
AB |
RTP of the symbol | =AA5/Config!$B$3 |
AC:AG |
Probability of exactly 1 to 5 of a kind (for information) | =(1-G5)*(1-H5)*(1-I5)*J5 (column AE, 3 of a kind) |
The expected ways for 1 to 5 of a kind in row 5 are:
Ways!L5: =B5*H5
Ways!M5: =B5*C5*I5
Ways!N5: =B5*C5*D5*J5
Ways!O5: =B5*C5*D5*E5*K5
Ways!P5: =B5*C5*D5*E5*F5
The product of the mean counts is correct because the reels are independent. The factor Z of the subsequent reel makes sure that the combination ends there. Column P has no factor Z, because a combination cannot continue after reel 5.
The totals are in AA15 and AB15 (base reels) and in AA30 and AB30 (bonus reels):
Ways!AA15: =SUM(AA5:AA14)
Ways!AB15: =AA15/Config!$B$3
Ways!AA30: =SUM(AA20:AA29)
Numbers for PIC1 on the base reels¶
| Reel | M(r), columns B:F | Z(r), columns G:K |
|---|---|---|
| 1 | 0.2000 | 0.8000 |
| 2 | 0.3200 | 0.7600 |
| 3 | 0.3200 | 0.7000 |
| 4 | 0.3200 | 0.7500 |
| 5 | 0.3200 | 0.7100 |
| Of a kind | Expected ways, columns L:P | Pay, columns Q:U | EV in credits, columns V:Z |
|---|---|---|---|
| 1 | 0.152000 | 0 | 0.000000 |
| 2 | 0.044800 | 0 | 0.000000 |
| 3 | 0.015360 | 5 | 0.076800 |
| 4 | 0.004653 | 15 | 0.069796 |
| 5 | 0.002097 | 50 | 0.104858 |
AA5 = 0.251453 credits. AB5 = 2.5145%. The total AA15 is 3.614598 credits, and AA30 is 3.100092 credits.
Condition
The formulas in columns L to P are correct only if reel 1 has no WILD symbol. The sheet Summary has a check for this condition.
6. Sheet Fish¶
Mean fish values, cells B5:E9¶
One row is one mode (base, level 1 to level 4). One column is one FISH symbol. The mean value is the sum of value x weight, divided by the sum of the weights:
Fish!B5: =SUMPRODUCT(Config!$B$25:$E$25,Config!$C$33:$F$33)/SUM(Config!$C$33:$F$33)
Fish!E9: =SUMPRODUCT(Config!$B$28:$E$28,Config!$C$52:$F$52)/SUM(Config!$C$52:$F$52)
KEY and FISH counts, rows 14 to 20 (base reels) and 25 to 31 (bonus reels)¶
| Columns | Contents | Formula in row 14 (reel 1, base reels) |
|---|---|---|
B |
E[k(r)]: mean number of KEY symbols in the window of the reel | =SUM(Win_Base!$P$4:$P$103)/Config!$B$6 |
C:F |
E[n(r, f)]: mean number of FISH1 to FISH4 in the window of the reel | =SUM(Win_Base!$L$4:$L$103)/Config!$B$6 |
G:J |
E[k(r) x n(r, f)]: mean of the product for the same reel | =SUMPRODUCT(Win_Base!$P$4:$P$103,Win_Base!$L$4:$L$103)/Config!$B$6 |
Row 19 (row 30 for the bonus reels) is the sum of the 5 reels. Row 20 (row 31) is the expected number of KEY and FISH pairs in the full window, E[K x N(f)]:
Fish!C20: =$B$19*C19-SUMPRODUCT($B$14:$B$18,C14:C18)+G19
Fish!F31: =$B$30*F30-SUMPRODUCT($B$25:$B$29,F25:F29)+J30
The formula has three parts:
$B$19*C19is (sum of the KEY means) x (sum of the FISH means). This is correct for a KEY and a FISH on different reels. It is not correct for the same reel.-SUMPRODUCT($B$14:$B$18,C14:C18)removes the part for the same reel.+G19adds the correct value for the same reel, from the product of the counts at each stop.
Collect EV, rows 36 to 40¶
One row is one mode. Row 36 is the base game and uses row 20. Rows 37 to 40 are levels 1 to 4 and use row 31.
Fish!C36: =B5*C20
Fish!G36: =SUM(C36:F36)
Fish!H36: =G36/Config!$B$3
Fish!C37: =B6*C31
Fish!G40: =SUM(C40:F40)
Each cell in C:F is (mean value of the FISH symbol in the mode) x (expected number of pairs). Column G is the collect EV of one spin, before the level multiplier.
| Cell | FISH symbol | Mean value | E[K x N(f)] | EV (credits) |
|---|---|---|---|---|
C36 |
FISH1 | 2.0775 | 0.064000 | 0.132960 |
D36 |
FISH2 | 13.4500 | 0.019200 | 0.258240 |
E36 |
FISH3 | 67.5000 | 0.004800 | 0.324000 |
F36 |
FISH4 | 666.6667 | 0.004800 | 3.200000 |
G36 = 3.915200 credits. This is the collect EV of the base game.
7. Sheet Trigger¶
FS symbols for each reel, rows 5 to 9¶
Columns B, C and D give the probability of 0, 1 and 2 FS symbols in the window of the reel. Column E is the sum. The sum must be 1.
Trigger!B5: =COUNTIF(Win_Base!$R$4:$R$103,0)/Config!$B$6
Trigger!C5: =COUNTIF(Win_Base!$R$4:$R$103,1)/Config!$B$6
Trigger!D5: =COUNTIF(Win_Base!$R$4:$R$103,2)/Config!$B$6
Trigger!E5: =SUM(B5:D5)
Number of FS symbols in the full window, rows 14 to 18¶
Row 14 is the distribution after reel 1. Each subsequent row adds one reel. Columns D to N are the totals 0 to 10. Columns B and C are zero "pad" columns for the formulas at the left edge.
Trigger!D14: =B5
Trigger!G15: =G14*$B$6+F14*$C$6+E14*$D$6
Trigger!G18: =G17*$B$9+F17*$C$9+E17*$D$9
The formula in row 15 reads: the probability of a total of k after reel 2 = (total k after reel 1) x P(0 on reel 2) + (total k-1) x P(1 on reel 2) + (total k-2) x P(2 on reel 2). Row 18 is the distribution for all 5 reels.
Trigger probabilities, rows 23 to 26¶
Trigger!B23: =G18
Trigger!B24: =H18
Trigger!B25: =SUM(I18:N18)
Trigger!B26: =SUM(B23:B25)
Trigger!C26: =IF(B26>0,1/B26,"")
Trigger!D23: =Config!$B$57
| Cell | Meaning | Value |
|---|---|---|
B23 |
P(3 FS symbols) | 0.00555827 |
B24 |
P(4 FS symbols) | 0.00026214 |
B25 |
P(5 or more FS symbols) | 0.00000492 |
B26 |
Trigger probability | 0.00582533 |
C26 |
Trigger frequency | 1 in 171.66 |
8. Sheet Bonus¶
KEY symbols in one free spin, rows 5 to 22¶
Rows 5 to 9 and rows 14 to 18 use the method of the sheet Trigger, with the KEY columns of Win_Bonus.
Row 18 is the distribution of the number of KEY symbols in one free spin (columns D to N = 0 to 10).
Bonus!B5: =COUNTIF(Win_Bonus!$P$4:$P$103,0)/Config!$B$6
Bonus!D18: =D17*$B$9+C17*$C$9+B17*$D$9
Bonus!D20: =N18
Bonus!N20: =D18
Bonus!B22: =SUMPRODUCT(D13:N13,D18:N18)
Row 20 holds the same distribution in the opposite direction (10 to 0). The state tables use this row. B22 is the mean number of KEY symbols for each free spin (0.2000).
Spin EV for each level, rows 27 to 30¶
| Column | Contents | Formula in row 28 (level 2) |
|---|---|---|
B |
Ways EV on the bonus reels | =Ways!$AA$30 |
C |
Collect EV with the weight tables of the level | =Fish!$G$38 |
D |
Level multiplier | =Config!$D$64 |
E |
EV of one spin of the level | =IF(Config!$B$68="all",D28*(B28+C28),B28+D28*C28) |
F |
KEY threshold of the level | =Config!$B$64 |
G |
Spins of the level | =Config!$C$64 |
Column E uses the multiplier rule of Config!B68. With all, the multiplier applies to the two parts. With fish, it applies to the collect only.
| Level | B ways EV |
C collect EV |
D multiplier |
E spin EV |
|---|---|---|---|---|
| 1 | 3.100092 | 10.678853 | 1 | 13.778945 |
| 2 | 3.100092 | 11.085973 | 2 | 28.372131 |
| 3 | 3.100092 | 11.354645 | 5 | 72.273687 |
| 4 | 3.100092 | 11.652133 | 10 | 147.522254 |
State tables, from row 47¶
The state tables calculate the probability to play each level. The state is the total number of KEY symbols in the free spins.
- Row 47 is the header. Columns
MtoALare the states 0 to 25. The last column means "25 or more". ColumnsCtoLare empty pad columns. - A block has one title row and 41 spin rows (spin 0 to spin 40). Column B shows the spin number.
- A cell gives the probability that the free spins are in this level, after this number of spins of the level, with this total number of KEY symbols.
| Block | Title row | Spin rows (spin 0 to spin 40) |
|---|---|---|
| Level 1 (all triggers) | 48 | 49 to 89 |
| Level 2 after a 3 FS trigger | 91 | 92 to 132 |
| Level 3 after a 3 FS trigger | 134 | 135 to 175 |
| Level 4 after a 3 FS trigger | 177 | 178 to 218 |
| Level 2 after a 4 FS trigger | 220 | 221 to 261 |
| Level 3 after a 4 FS trigger | 263 | 264 to 304 |
| Level 4 after a 4 FS trigger | 306 | 307 to 347 |
| Level 2 after a 5 FS trigger | 349 | 350 to 390 |
| Level 3 after a 5 FS trigger | 392 | 393 to 433 |
| Level 4 after a 5 FS trigger | 435 | 436 to 476 |
Level 1, spin 0. The free spins start with 0 KEY symbols. Cell M49 is 1. The other cells of the row are 0.
One spin. Each spin row comes from the row above it:
Bonus!M50: =SUMPRODUCT(C49:M49,$D$20:$N$20)
Bonus!P50: =SUMPRODUCT(F49:P49,$D$20:$N$20)
Bonus!AL50: =SUM($M49:$AL49)-SUM($M50:$AK50)
- The
SUMPRODUCTmultiplies 11 cells of the row above (states k-10 to k) with the reversed KEY distribution in row 20. The result is the sum of P(state k-j) x P(j KEY symbols in one spin) for j = 0 to 10. - For the first states, the range starts in the empty pad columns. An empty cell counts as 0.
- The last column takes the remaining probability: the total of the row above minus the other cells of the row. Thus the total of a row stays the same during a level.
Start of the subsequent level. The spin 0 row of a level 2, 3 or 4 block takes the row at the end of the level before it. It keeps only the states at or above the threshold:
Bonus!M92: =IF(M$47>=Config!$B$64,INDEX(M$49:M$89,Config!$B$57+1),0)
Bonus!P92: =IF(P$47>=Config!$B$64,INDEX(P$49:P$89,Config!$B$57+1),0)
Bonus!P135: =IF(P$47>=Config!$B$65,INDEX(P$92:P$132,Config!$C$64+1),0)
INDEX(range, n + 1)reads the row after n spins. For level 2, n is the number of initial free spins (Config!$B$57for a 3 FS trigger) and the range is the level 1 block. For level 3, n is the number of spins of level 2 and the range is the level 2 block of the same trigger.- The
IFsets a state below the threshold to 0. These states are the free spins that end. - The sum of the spin 0 row is the probability to play the level.
The level 1 block is the same for all triggers. The triggers use different rows of it (after 10, 15 or 20 spins).
Results for each trigger, rows 35 to 40¶
| Column | Contents | Formula in row 35 (3 FS) |
|---|---|---|
B |
Probability of the trigger | =Trigger!$B$23 |
C |
Initial free spins | =Config!$B$57 |
D |
P(play level 1) | =IF(C35>0,1,0) |
E:G |
P(play level 2, 3, 4): the sum of the spin 0 row of the block | =SUM($M$92:$AL$92) |
H |
EV of level 1 = P x spins x spin EV | =D35*C35*$E$27 |
I:K |
EV of levels 2, 3, 4 | =E35*$G$28*$E$28 |
L |
EV of the free spins for this trigger (credits) | =SUM(H35:K35) |
M |
The same as a multiple of the bet | =L35/Config!$B$3 |
N |
Mean number of free spins | =D35*C35+E35*$G$28+F35*$G$29+G35*$G$30 |
The level EV is a product, because the win of a spin in a level is independent of the event "the free spins get to this level".
Bonus!L38: =SUMPRODUCT(B35:B37,L35:L37)
Bonus!L39: =IF(SUM(B35:B37)>0,L38/SUM(B35:B37)/Config!$B$3,0)
Bonus!L40: =L38/Config!$B$3
| Cell | Meaning | Value |
|---|---|---|
E35, F35, G35 |
P(play level 2, 3, 4) after a 3 FS trigger | 0.323171, 0.122812, 0.051681 |
L35 |
EV of the free spins after a 3 FS trigger | 343.1430 credits |
N35 |
Mean number of free spins after a 3 FS trigger | 13.9813 |
L38 |
EV of the free spins for each base spin | 2.090199 credits |
L39 |
Mean free spins win for each trigger | 35.8812 x bet |
L40 |
Free spins RTP | 20.9020% |
Table limits, cells B43:B44¶
Bonus!B43: =IF(MAX(Config!$B$57:$B$59,Config!$C$64:$C$66)<=40,"OK","LIMIT EXCEEDED")
Bonus!B44: =IF(MAX(Config!$B$63:$B$66)<=25,"OK","LIMIT EXCEEDED")
The state tables hold 40 spins for each level and thresholds up to 25. If an input is larger, the cell shows LIMIT EXCEEDED and the result is not valid.
9. Sheet Summary¶
RTP, rows 6 to 9¶
| Row | Component | Column B: EV in credits | Column C: RTP | Value |
|---|---|---|---|---|
| 6 | Base game: ways pays | =Ways!$AA$15 |
=B6/Config!$B$3 |
36.1460% |
| 7 | Base game: KEY and FISH collect | =Fish!$G$36 |
=B7/Config!$B$3 |
39.1520% |
| 8 | Free spins | =Bonus!$L$38 |
=B8/Config!$B$3 |
20.9020% |
| 9 | Total | =SUM(B6:B8) |
=B9/Config!$B$3 |
96.2000% |
- Column D is the share of each component:
=C6/$C$9. - Column E is a fixed reference value from the script
gamemath/exact_calc.py, written when the workbook was made. - Column F is the difference:
=C9-E9. It is 0 for the configuration of the workbook as delivered. It is not 0 after you change an input. This is correct. - Column G is a fixed reference value from the simulation.
Key figures, rows 13 to 27¶
Column B is a formula. Column C is the fixed reference value.
| Cell | Figure | Formula |
|---|---|---|
| B13 | Free spins trigger probability for each base spin | =Trigger!$B$26 |
| B14 | Free spins trigger frequency (1 in N base spins) | =1/B13 |
| B15 | P(3 FS symbols) | =Trigger!$B$23 |
| B16 | P(4 FS symbols) | =Trigger!$B$24 |
| B17 | P(5 FS symbols) | =Trigger!$B$25 |
| B18 | Mean free spins win for each trigger (x bet) | =Bonus!$L$39 |
| B19 | Mean KEY symbols for each base spin | =Fish!$B$19 |
| B20 | Mean KEY symbols for each free spin | =Fish!$B$30 |
| B21 | P(play level 2) after a 3 FS trigger | =Bonus!$E$35 |
| B22 | P(play level 3) after a 3 FS trigger | =Bonus!$F$35 |
| B23 | P(play level 4) after a 3 FS trigger | =Bonus!$G$35 |
| B24 | Mean number of free spins after a 3 FS trigger | =Bonus!$N$35 |
| B25 | Mean bonus win after a 3 FS trigger (x bet) | =Bonus!$M$35 |
| B26 | Mean bonus win after a 4 FS trigger (x bet) | =Bonus!$M$36 |
| B27 | Mean bonus win after a 5 FS trigger (x bet) | =Bonus!$M$37 |
Checks, rows 30 to 36¶
Each check shows OK or a message.
| Cell | Check |
|---|---|
| B30 | Base reel strips obey the design rules |
| B31 | Bonus reel strips obey the design rules |
| B32 | No WILD on reel 1 (necessary for the ways formula) |
| B33 | No FS on the bonus reels (no retrigger in the model) |
| B34 | Spins for each level within the table limit |
| B35 | KEY thresholds within the table limit |
| B36 | Workbook RTP equals exact_calc.py (shipped configuration) |
Simulation reference, from row 38¶
These rows are fixed values from the Monte Carlo simulation (1,000,000,000,000 rounds). They are not formulas. They do not change when you change an input.
10. Sheet Distribution¶
This sheet holds two win distribution tables. One table is for full game rounds (rows 6 to 20). The other table is for the free spins features (rows 26 to 40). One row is one win band, as a multiple of the bet. The bands are the same as on the page RTP and volatility.
The values in columns D and E come from the simulation (1,000,000,000,000 rounds). They are fixed values for the delivered configuration. They do not change when you change an input. The other columns are formulas.
| Column | Contents | Formula in row 6 |
|---|---|---|
A |
The win band | text |
B, C |
Lower and upper limit of the band (x bet) | values |
D |
RTP of the band: the part of the RTP that comes from wins in this band | value from the simulation |
E |
Probability of the band | value from the simulation |
F |
Frequency: 1 in N | =IF(E6>0,1/E6,"") |
G |
Cumulative RTP: this band and all higher bands | =SUM(D6:D$20) |
H |
Cumulative probability: this band and all higher bands | =SUM(E6:E$20) |
I |
Cumulative frequency: 1 divided by the cumulative probability | =IF(H6>0,1/H6,"") |
- The row below each table has the totals. The total of column D is the RTP of the simulation (round table) or the free spins RTP (free spins table). The total of column E is 1.
- In the free spins table, the probability is for one free spins feature. The RTP is a part of the total bet, so that the two tables use the same unit.
- Example:
I15shows that one round in 847 wins 100 x bet or more.
11. Sheet ReadMe¶
This sheet gives short instructions, the model, the limits and the command that copies the inputs to the game configuration.
12. One result from start to end¶
This section follows the 3 of a kind pay of PIC1 in the base game through the workbook.
Reels_Base!B4:F103holds the strips. Reel 1 has 5 PIC1 symbols and no WILD symbol.Win_Basecounts PIC1 and WILD in each window. For reel 1, the PIC1 column isBand the WILD column isQ.Ways!B5= (sum of the two columns) / 100 = 0.2000. This is M(1).Ways!C5= 0.3200 andWays!D5= 0.3200.Ways!J5= 0.7500. This is Z(4), the probability that reel 4 shows no PIC1 and no WILD.Ways!N5= M(1) x M(2) x M(3) x Z(4) = 0.2000 x 0.3200 x 0.3200 x 0.7500 = 0.015360 expected ways.Ways!S5reads the pay of 5 credits fromConfig!D11.Ways!X5= 0.015360 x 5 = 0.076800 credits.Ways!AA5adds the five EV cells of the symbol: 0.251453 credits.Ways!AA15adds all pay symbols: 3.614598 credits.Summary!B6readsWays!AA15.Summary!C6divides by the bet: 36.1460%.Summary!C9adds the three components: 96.2000%.
13. Change an input¶
- Change a yellow cell. Excel calculates all sheets again.
- Read
Summary!C9for the new RTP. - Read the checks on the sheet Summary. All checks must show
OK, with one exception: the last check compares with the delivered configuration. - To use the new values in the game, save the workbook and run
.venv/bin/python tools/excel_to_config.py.
| If you change | These results change |
|---|---|
| The bet | All RTP values. The EV values in credits do not change. |
| The paytable | Sheet Ways, the spin EV of each level, the RTP of the ways pays and of the free spins |
| Fish values or weights of the base game | Row 36 of the sheet Fish and the RTP of the base collect |
| Fish values or weights of a level | The spin EV of that level and the RTP of the free spins |
| Free spins, thresholds, spins or multipliers | The state tables and the RTP of the free spins |
| A base reel strip | Win_Base, then the base ways pays, the base collect and the trigger probability |
| A bonus reel strip | Win_Bonus, then the spin EV of each level, the KEY distribution and the level probabilities |
| A placement rule | Only the checks |
14. Excel functions in the workbook¶
| Function | Use |
|---|---|
COUNTIF |
Count a symbol in a window or on a strip. Count the stops with exactly k symbols. |
SUM |
Totals and means |
SUMPRODUCT |
Mean of a product of two counts. Mean fish value. One step of the state tables. Probability of "no symbol". |
INDEX |
Read the row of a state table after n spins |
IF, AND |
Checks, the multiplier rule, the level threshold |
MAX, ABS |
Limit checks and the reference check |
15. Verification of the workbook¶
- The script
tools/verify_all.pycompares the workbook with the exact calculator. It also writes three changed configurations into a copy of the workbook and compares again. See Verification. - Microsoft Excel and the Excel MCP server calculate the same results from the formulas.
- The largest relative difference between the workbook and the exact calculator is smaller than 0.000000001.