Help on building a statistical cheat sheet

Hello all,

It's the first time I'm posting here so I hope I don't break any rules. If so, I apologies.

I have the idea of building an excel sheet that could help me calculating the probability and associated cost of a specific loot in a video game.

I'll detail the statistical part, then the idea of what I want to make, and how I think it should be done (which I may be totally wrong about, that's why I'm writing here to seek advices).

The statistical part

I'm playing a game (Grim Dawn if anyone is interested) which is an ARPG where you basically loot items to make a build.

There is different gear slots (head, hands, pants, whatever) and on each slot an item can have 3 status : MI, Other, None.

We're only interested in the "MI" status so far, both other aren't relevant for now.

When an item is consider as an "MI", it can be rerolled between different values to get the exact value we want for a build.

Each reroll has a cost, that increase after each try till it be capped (those values are fix).

So for a specific item you have 1/X chance to get the right roll at the first try (X depends on the gear slot and is a fix value), then on the 2nd roll, etc...

It's a binomial distribution which can be written : Probability = 1- (1- 1/X)^number of rolls, with the cost increasing on each rolls (following a known rule)

What I have in mind in terms of user interface

The main idea behind this sheet, is to let people estimate the cost of rerolling all the gear piece they need.

So there will be an entry sheet where people can choose from a list for each item slots like this

https://preview.redd.it/85wopqwffuph1.png?width=1185&format=png&auto=webp&s=8f04d213be3e2a2878f97ebf0693f0adc36853ea

And I would like another sheet called "Overview" that would indicate the estimate cumulate cost for all the gear, may be with different percentil options ("if you're one of the lucky 25%", "50%", "If you're one of the unlucky 25%")

How I'll plan to do it and my main issues

So basically the idea is to associate every gear cell with the corresponding binomial distribution and sum up everything for the total cost.

My problems are :

- I don't know how to implement the cost incrementation in that formula (after roll 2, cost increase from 1000 to 2000 for exemple, then after roll 5 it increase again, etc....). I have the table value of this cost incrementation but don't know how to link it to the final formula.

- I don't know how to show the different percentil (which is optionnal but cool anyway)

Thanks for your help !

Bonus :

This is actually a simplified model, the real rolling model is a bit more complex.

We're rerolling a "normal" stat, which can be rerolled from a pool of "normal + rare" stats.

BUT as soon as you roll a rare stat, then you can no longer roll a normal stat and only reroll from the rare pool.

If anyone has any idea on how to write this formula, that would help a lot.

For exemple : a ring has 35 normal stat and 25 rare stats.

So for the first roll you'll have a chance of 1/(35+25)= 1/60 chance to roll the specific rare stat you want (we're almost always looking for rare stats).

But if you roll a rare stat, then you'll have 1/25 chance to roll it on your next roll since you're only rolling on the rare pool now.