Help on building a statistical cheat sheet
Our take
The quest to optimize loot in games like Grim Dawn is a surprisingly fertile ground for spreadsheet enthusiasts, and this user’s ambition to build a statistical cheat sheet highlights a fascinating intersection of gaming, data analysis, and the enduring power of the spreadsheet. It’s a testament to how players are increasingly leveraging tools beyond the game itself to understand and master complex systems. The complexity they’re tackling – binomial distributions, cost incrementation, and nuanced probability shifts based on in-game events – underscores the depth of strategic thinking involved in modern ARPGs. This project echoes similar efforts we’ve seen in other data-driven hobbies, from fantasy sports to collectible card games, where users strive to quantify their chances of success and maximize their returns. It’s also worth noting the broader challenges faced by many teams relying on Excel for complex calculations, as illustrated in For the last few months Excel has been behaving very strange for everyone on my team, and its a huge burden to productivity, highlighting the potential pitfalls of relying on a legacy tool for increasingly sophisticated tasks.
The user’s outlined approach – linking gear slots to binomial distributions and aggregating costs – is a solid foundation. However, their identified challenges – particularly incorporating cost incrementation and calculating percentiles – are common hurdles when building dynamic spreadsheet models. The cost incrementation problem, where the reroll cost increases with each failed attempt, is a classic example of needing to integrate external data (the cost table) into a formula. This often requires using lookup functions (like VLOOKUP or XLOOKUP) or creating custom functions (if the spreadsheet software allows) to dynamically retrieve the correct cost based on the number of attempts. The percentile calculation, while optional, adds another layer of complexity, requiring the user to sort and analyze data across multiple scenarios to determine the likelihood of achieving a specific cost within a given range. Furthermore, the added complexity of the "normal vs. rare" stat pool introduces a more intricate probability calculation, where the chances of success shift based on previous rolls, a challenge that requires careful consideration of conditional probabilities. It's a situation where exploring AI-native spreadsheet solutions, as discussed in How to Build Consistent Designs with Claude Code, could offer more streamlined approaches to managing these complex formulas and data relationships.
The broader significance of this project extends beyond the individual gamer’s pursuit of optimal loot. It represents a microcosm of how individuals are using data analysis to understand and interact with increasingly complex systems – whether they’re video games, financial markets, or scientific research. The desire to quantify and optimize outcomes is a fundamental human drive, and spreadsheets, despite their limitations, remain a surprisingly powerful tool for achieving that goal. The user’s willingness to share their progress and seek advice is also commendable, fostering a community of spreadsheet enthusiasts who can learn from each other’s experiences. The reliance on Excel, however, also exposes a potential vulnerability. As discussed in Is data from the Internet not refreshing?, relying on static data within spreadsheets can lead to inaccuracies and inconsistencies, particularly when dealing with dynamic game updates or evolving strategies.
Ultimately, this project underscores the evolving role of spreadsheets in a data-rich world. While they may not be the ideal solution for every data analysis task, their accessibility and flexibility continue to make them a valuable tool for individuals seeking to gain insights from complex systems. The user’s journey highlights both the potential and the challenges of using spreadsheets for advanced statistical modeling, and it raises a compelling question: as AI-powered tools become increasingly integrated into spreadsheet software, will we see a resurgence in the creation of sophisticated, data-driven solutions for niche applications like this one, or will users migrate to entirely new platforms better suited to handle these complexities?
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
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.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience