PDA

View Full Version : Guide Calculating Average Upgrade Costs [Warning: Math]



Walkure
Apr 22, 2013, 06:33 PM
Conceptual

Discussing the the basic theory being used for a general weapon reinforcing/grinding system:
[SPOILER-BOX]
Probability of a String of Trials Occurring
Let’s imagine a specific probability where you fail x amount of times before succeeding a reinforcement value. With this, there is a known P probability of success and a Q=(100%-P) probability of failure. In comparing a string of several independent trials, the overall probability is simply the product of the probability of each individual event occurring. In this situation, one success event occurred, and x amount of failure events occurred. So, in effect, the probability of that specific event can be calculated as such,

http://i.imgur.com/8qLn9B1.png
Example of usage: I have a completely fair coin, which has a probability P=50% of landing heads up and a probability Q=100%-P=50% of landing tails up. I flip the coin until it reaches heads. What is the probability of the event where I fail once before landing on heads?

http://i.imgur.com/vpIpl4I.png
Since each coin flip is an independent trial, I could potentially fail several times before I end up successfully flipping heads. There is a probability for flipping tails any number of times before flipping a single heads.

Total Probability
Each of these strings (failing heads once, twice, etc.) are mutually exclusive events; there is no overlap in probabilities between events. The sum of all these event’s probabilities is one; this is an exhaustive list of probabilities.

For example, is the sum of all events possible in Pstring(x) account for all situations? Can the probabilities of flipping a fair coin be found? The answer is yes; you can calculate the probability of every single event, adding up to a total probability of 100%.


http://i.imgur.com/ouFcZLx.png
After adding the 10th element in the infinite series, the sum total is at 99.9%. As you add more and more examples, the total probability of Pstring adds up to 100%.

Determining Average Cost With Probability
If every outcome, and their probability, is known, and a cost can be assigned to each outcome, then the average cost can be determined:

http://i.imgur.com/cthLUSE.png
So, applying this to a fair coin, with a P=50%=.5, and assigning a cost to a heads and a tails flip, the average cost would be the midpoint between the two costs.

General Formula:
Now, we can look at an item-grinding system, and calculate average cost from there. Assuming that the percentage success rates are known, each event can have a calculated probability, and a cost can be associated with that event. So, it is possible to calculate for the average upgrade cost.

http://i.imgur.com/FEwRVDB.png
This is simply taking Pstring, without assuming the probability of a fair coin (50%), and assigning a cost to each event happening!
[/SPOILER-BOX]
Applying the general formula to PSO2:
[SPOILER-BOX]
I. Examining Attempt Cost
Attempt cost in PSO2 is determined by a base cost of some (relatively small) amount of meseta, a certain amount of Grinders, and any support items you use in each grind attempt. Usually, unless a support item is used, each individual grind attempt has a total cost of under 10,000 meseta. However, this quickly adds up after repeated fails.

II. Examining Success Rate
The success rates used in these calculations are based on this collection of data for 10* weapons (http://pso2osusume.com/kyouka-custom/rare10buki-kanwamae-kanwago-hikaku/). It is relatively recent, dating to April 4, 2013, and has a decently high amount of trials, up to 3801 trials if I'm reading right from Google Translate. This is close enough for a rough estimate. As 10* weapons are usually the most relevant grind costs, as they are well-desired, decently obtainable, and extremely expensive to grind compared to lower rarities, they’ll be the focus of this calculation.

III. Examining Failure Penalty Distribution
There are four possible penalties for failing a grind attempt; your weapon drops in grind value from 0 to 3 levels. Those probabilities are shown in the table above. However, when calculating the failure penalty for usage of the formula, it helps to know the probability given that a failure occurred. In this case, the probability of a given penalty, assuming a failed grind attempt is simply the percentage chance of overall occurrence divided by the sum total of each fail rate. There is no risk of an item being destroyed; the usual penalty is solely determined by the cost of lost grind levels.

Shown below is a table showing the aggregated grind probabilities, using the collection of data as shown in the previous section, with this calculated penalty distribution in a separate segment.

http://i.imgur.com/GqpMtnk.png
As an example, with an initial grind value of 1, there is a 19% chance to fail the grind attempt with no loss in grind value, and no chance of losing one, two, or three grind levels. Thus, it takes up 100% of the failure distribution, as it is the only possible event when a failure occurs.

IV. Examining the Cost of Failure
Considering that there are multiple possible ways to suffer from a grind failure, the failure cost will be an average cost of those penalties.

At +0, and +1, there is no penalty on failure, so the “Fail Cost” of the formula for those sections will be zero, and, since their attempt rate and success rate is also known, their average cost can be calculated. However, how can the proceeding failure costs be determined?

When the weapon drops down a grind a grind level, the cost of such an event would be the cost required to take it back up a level. So the average failure rate depends on the average cost of upgrading previous, as well.

http://i.imgur.com/Judoaic.png

V. Examining Support Items
Support items throw an interesting kink into the mix. There are two kinds of support items to use when grinding: success amplifying support items and risk reducing support items. Using either of these increases the attempt cost, with hopefully positive returns on investment by reducing the average attempts needed to succeed a roll or the failure penalty cost associated with failing a roll.

V.a. Handling Success-Amplifying Items
The most likely way these items work is by raising the overall success rate by a certain percentage, while still maintaining the proportions of penalties during a failure event. So, this would increase the success rate, without changing the derived failure penalty distributions.

V.b. Handling Risk-Reducing Items
A risk penalty is determined by averaging the upgrade costs of previous levels. So, losing two levels would require, on average, the average cost of raising the weapon up again the previous two levels. With the risk reduction (+1) item, only one level would be lost. So, to calculate the penalty cost when using a risk reduction (+1) item, the quickest way would be to treat the -3 penalty like a -2 penalty, -2 like a -1, and a -1 like no penalty.

http://i.imgur.com/mS92kfZ.png
With a full protection item, there is no penalty incurred on any failure; the failure cost is zero.

VI. Optimization
In order to optimize the costs, the most efficient method of upgrading previous upgrades is needed. After all, calculating the failure penalty on a +20% ticket with (+1) protection at +9 shouldn’t necessarily rely on you using the same items on +7 or +8; it could be more effective to use other options for those other circumstances. So, the most optimal methods of grinding low grind-value items should be used for grinding higher levels. This means calculating for the most efficient way to grind a +0, then using that to find the most efficient way to grind a +1, and so on. Through this method, finding the optimal path to a +10 10* Weapon can be determined.[/SPOILER-BOX]


Analysis

Google Drive Version. (https://docs.google.com/spreadsheet/ccc?key=0Al4_gG8wPfu_dGN5ZGhZWkZwS2IwWm5tT1labVFUR Gc&usp=sharing)

Here is a link to the downloadable version. (https://docs.google.com/file/d/0B14_gG8wPfu_V3pQdFFBWHlLRlU/edit?usp=sharing)

Using the downloadable version, you can play around and see how adjusting prices of items affects upgrade costs and likelihoods.

With these assumed rates, and costs, the most effective strategy seems to be:
Start using (-1) protection when your weapon is +7 or higher.
Using a 5% ticket when your weapon is at +9 currently is about the same as not using one.

SociableTyrannosaur
Apr 22, 2013, 06:53 PM
http://i.imgur.com/bSbYWH4.jpg

UnLucky
Apr 22, 2013, 07:07 PM
Hm, so having 5 +5%s for every 1 +10% is better value.

I am actually legitimately surprised by that.

I hope they don't stop giving them out for free.

A weapon that costs 10k per grind attempt could change everything, though.

Xaeris
Apr 22, 2013, 07:15 PM
Upon first glance, I would have expected more of an exponential rise in costs during the last stages of grinding. I'll be interested to read this more in depth later on in the night. It looks like solid work.

Walkure
Apr 22, 2013, 07:34 PM
Upon first glance, I would have expected more of an exponential rise in costs during the last stages of grinding. I'll be interested to read this more in depth later on in the night. It looks like solid work.
The average cost of upgrading +9 alone is usually half of the average total cost of +0->+10, and +7->+10 take up about 80% of the average cost for the most efficient routes. That's a pretty significant jump, if you ask me.

Also:

Hm, so having 5 +5%s for every 1 +10% is better value.

I am actually legitimately surprised by that.

I hope they don't stop giving them out for free.

A weapon that costs 10k per grind attempt could change everything, though.
Well, let's find out!
[SPOILER-BOX]
http://i.imgur.com/YwIMbtC.png
http://i.imgur.com/VfsPXFa.png
[/SPOILER-BOX]So, optimal strategy slightly changes in that you use (+1) protections earlier.

UnLucky
Apr 22, 2013, 08:50 PM
Wow I love this kind of stuff. Now I want to know how much the attempt cost has to be to make higher boosts and risk reductions worth it, or how cheap a 10% has to go for to be worth using over a 5%.

I think I will download your spreadsheet and play with it a little.

SociableTyrannosaur
Apr 22, 2013, 09:26 PM
Spreadsheets are fun, huh?

UnLucky
Apr 22, 2013, 09:31 PM
Considering a few other posts of mine, I don't think it's that surprising.

SociableTyrannosaur
Apr 22, 2013, 09:35 PM
Actually I was being sincere.

Walkure
Apr 22, 2013, 09:47 PM
Wow I love this kind of stuff. Now I want to know how much the attempt cost has to be to make higher boosts and risk reductions worth it, or how cheap a 10% has to go for to be worth using over a 5%.

I think I will download your spreadsheet and play with it a little.
If you did already, I made a quick fix to it; I left the base cost of the no grinders/no support column hard fixed at 6,000 for "base cost" rather than adjusting to the base cost at the start. You can either copy that over from any other column (as that specific column is the same for every condition) or just redownload since it's fixed in the web document.

There are some features, like the Dudu graph or conditional formatting on the aggregated table (google docs won't let me reference a specific cell when selecting a minimum, so I'm not sure if it'll let me bold the minimal option like I have in the screenshot.

I'll go upload it again without a conversion for including those features.

Edit: Here's the full version (https://docs.google.com/file/d/0B14_gG8wPfu_R1VHNnFMYmh6V0U/edit?usp=sharing), I'll add it to OP with details after Falz
Edit 2: Done between cutscenes!

Mekhana
Apr 22, 2013, 10:19 PM
So I was doing it right all along?
Except I used them at +8 and +9.

http://cache.ohinternet.com/images/0/0a/Feelsgoodmangreen.jpg

Xaeris
Apr 22, 2013, 11:19 PM
The average cost of upgrading +9 alone is usually half of the average total cost of +0->+10, and +7->+10 take up about 80% of the average cost for the most efficient routes. That's a pretty significant jump, if you ask me.



It's not insignificant. But like I said: I would have expected "more."

UnLucky
Apr 22, 2013, 11:35 PM
It jumps up by like a million no matter what

Lebensohl
Apr 23, 2013, 04:17 AM
Great job OP! I went through your spreadsheet and your math checks out. It's incredibly useful and thanks!

Syklo
Apr 23, 2013, 05:59 AM
I do specialist maths in high school and I do not get (most or some of) this.

:'(

UnLucky
Apr 23, 2013, 06:22 PM
I've been bouncing through your (updated) spreadsheet, following your references and things, and I don't see anything glaringly incorrect about your assumptions.

But yeah here are some of my simple experiments.

200k Grind Attempt Cost (base+grinders):
[SPOILER-BOX]http://i.imgur.com/sSwoNSn.png

Predictably, No Boost/No Protector was by far the most expensive.

What's fascinating is that the disproportionate grinding cost still does not make higher success boosters and protection desirable. The best choice is still by and large simply a +5% and/or (+1), with a full protect only becoming useful near the end, but only really with a +5% booster.[/SPOILER-BOX]
"Reasonable" Booster Costs:
[SPOILER-BOX]I kept the base+grinder cost the same (6k+2*2k), but changed these pricings:

+5% = 30k
+10% = 60k
+20% = 120k
(+1) = 30k
(Full) = 120k
http://i.imgur.com/Dkxg9kF.png

Grinding prices obviously drop across the board for the higher supplements. But interestingly, it's still not significant enough, even trying for that final grind. [/SPOILER-BOX]
Unreasonable Grinding Costs, Reasonable Boosts
[SPOILER-BOX]The obvious progression, both at once!
http://i.imgur.com/KJxCsoE.png

This is... so, so right. Using nothing at all costs you the most, using cheaper supplements for easy grinds gives you a noticeable benefit, with larger boosts and protection becoming more and more worthwhile. At the highest grind levels, it is always a good idea to use the next highest booster or better protection.

Clearly Sega should be selling the higher boosters individually at linearly tiered prices with normal grinding being extremely pricy. The 5% and (+1) items could go for a bit cheaper, but not by much. This would be beneficial for both the player and Sega alike.[/SPOILER-BOX]

Next time I get really bored, I'll think about reversing formulas to find exact turning points for 10% or Full Protect prices w.r.t. naked grinding costs.

Walkure
Apr 23, 2013, 10:19 PM
Well, this is funny. Clicked around on the website I linked for more information, as I honestly didn't look too far past the rates posted, found this.

http://i.imgur.com/s8OsvJ8.png

Welp. Let's compare some results to my calculations.

http://i.imgur.com/tt4GqHU.png
After clicking "calculate" a couple times:
http://i.imgur.com/q2fMPb6.png
http://i.imgur.com/vROzXHC.png

So, this seems like it simulates running through 0 to 10 several times, and takes the average out of several simulated attempts. It gives about the same average total cost estimate overall from adding up in a similar way (my simulation gives about 1.404m for the same result). I don't seem to have an option for success-boosts either in the simulation.

But yeah, this gives some simple ideas for finding average attempts and average grinder consumption:

create a calculated cell using the optimal cost route on the spreadsheet.
http://i.imgur.com/SgdFq36.png
Now, adjust the costs to only count one grinder or one attempt. Examples:

Finding average grinder consumption:
http://i.imgur.com/ScNfgsq.png
This gives an average of 67 grinders used.

Finding Average Attempts
http://i.imgur.com/FvNRTZA.png
This gives 34 attempts on average to +10 a weapon.

Also I found another little bug:
http://i.imgur.com/V2aN73m.png http://i.imgur.com/Tk21SKU.png
Left those columns hardcoded... just copy over any column. Or redownload in a bit. I'll go fix that in the downloads.

UnLucky
Apr 23, 2013, 10:24 PM
Oh yeah, I found it odd how using a 5% boost was cheaper than nothing at all with 100% success rate. Fixed it on my copy though.

Walkure
Apr 23, 2013, 10:43 PM
It's fixed in both the drive and downloadable versions.

Also:

I do specialist maths in high school and I do not get (most or some of) this.

:'(The process mostly uses binomial probability and statistics, as well as some iterative calculations. If you go into programming you'll get a lot more into iterative calculations. Probability is usually glossed over at most in some math courses, so you might want to take a course specifically in it or just research on your own if you want to learn more.

Syklo
Apr 23, 2013, 11:45 PM
The process mostly uses binomial probability and statistics, as well as some iterative calculations. If you go into programming you'll get a lot more into iterative calculations. Probability is usually glossed over at most in some math courses, so you might want to take a course specifically in it or just research on your own if you want to learn more.

Had the time to read through more of it again and I sort of get it now.
I was confused @ what the difference between fail and attempt cost was, but then I read on....

I'm really surprised that, +6 onwards, it's impossible to fail and not lose a grind (without risk reducers).

Walkure
Apr 24, 2013, 10:46 PM
I got bored, and jealous of the budget function in the simulation made there, so I decided to implement that in excel, too.

Alright, now let's get a bit more advanced.
[SPOILER-BOX]
I added more to the general formula section and added a variance section
http://i.imgur.com/JoQuS40.png
http://i.imgur.com/VmMM13G.png
In short, that's my explanation for the implementation of variance calculations.

[/SPOILER-BOX]Now remember, folks; variance is the square of the standard deviation, and can be added normally by addition, so for the most part I sum up variances as needed AND ONLY THEN take the square root to get the standard deviation.

And, as everyone is probably painfully aware, there is a huge amount of randomness in grinding. This leads to standard deviations well in the range of millions of meseta for total costs. This means that variance, again the square of standard deviation, is going to be GIGANTIC. As in, like, a trillion or more for the total from +0 to +10.

Anyways, let's get on with how this changes the spreadsheet.

Average, or mean values are not changed, before/after pics:

[SPOILER-BOX]http://i.imgur.com/8Wn9t8P.png
http://i.imgur.com/c95H7kQ.png[/SPOILER-BOX]
You'll notice they're slightly different from the previous entries in the thread, and that's because I haven't updated screenshots since catching those few "Attempt Cost" values that were fixed values instead of dynamic.

What's been added:
[SPOILER-BOX]http://i.imgur.com/JMf6rpe.pngMore iterative calculations for everything, since variance has to be totaled up.
http://i.imgur.com/fA9EKDf.png
Calculated columns for the variance and standard deviations in each step.

in new sheets:
Variance calculations sheet:
http://i.imgur.com/VZoCUhw.png
Collects the variance values from each section, and, instead of just getting the minimum, looks up the minimum from the "average costs" table and uses the corresponding variance instead. Then, in the summation column, it adds them up.

Budget Analysis:
http://i.imgur.com/I2Nxw3s.png
Grabs the mean, total variance, and then calculates the standard deviation. Using the "budget" value, which is an input, it uses the Cumulative Distribution Function (CDF) of a Normal Distribution to estimate the probability that you will spend your budget or LESS to upgrade that weapon to +10 from +0.

Proofing and explaining that will pretty much require me to draw out a normal distribution and show what the CDF is doing, and I'm a bit lazy atm.
[/SPOILER-BOX]

Here's the link for it (https://docs.google.com/file/d/0B14_gG8wPfu_eGlDbF9mVEE2dFU/edit?usp=sharing), I'ma proof and stress-test this more over the weekend before updating the OP. Taking a break for now, because I've got pretty much all the features I want on it atm.

Shinmarizu
Apr 24, 2013, 10:59 PM
HOLY CRAP. You weren't kidding when you said the variance was gigantic.
Also quite unsettling: the SD is nearly equal to mean in this scenario. I recently ground a 10* to +10, and the costs feel accurate. It helps a fair bit that the majority of a person's grinders and supplements are free.

Walkure
Apr 25, 2013, 12:31 PM
HOLY CRAP. You weren't kidding when you said the variance was gigantic.
Also quite unsettling: the SD is nearly equal to mean in this scenario. I recently ground a 10* to +10, and the costs feel accurate. It helps a fair bit that the majority of a person's grinders and supplements are free.Yeah the standard deviations are large enough so that a completely non-truncated normal distribution is kind of out of the question for what probability I'm trying to do here:

Best example of that in action:
http://i.imgur.com/bw1cege.png

Best way to do that would be using a left-truncated distribution function, which... yeah I'm not exactly sure why I didn't think of truncation in the first place.

Edit: implemented with:

http://i.imgur.com/ueCYIvs.png
Now let's do some quick logic test to see if this is reasonable (I have the CDF floored at 0%).

http://i.imgur.com/gN66nFZ.pnghttp://i.imgur.com/3OOYx1O.png
The chance of going straight to +10 with no fails in the same method is about .0709%. It's not perfectly accurate, but then again we're not working with a perfectly accurate distribution when discussing this system either. This normal distribution is a continuous function, whereas dudu costs would be a lot of discrete points in a similar pattern.

Now, let's look at higher-up values:
http://i.imgur.com/K9TNdqj.png
http://i.imgur.com/4etYCr6.png
Looks better.

Now, using a function in excel called "goal seek", you can find what budget you'd need to have a specific probability of going from 0->10. The problem is that it starts at 1, and tries adjusting from there. If you just have it adjust the budget, it'll quickly get confused and give up before it can find your desired percentage.

Now, if you have budget as a function of a multiplier to the mean, you can goal seek using that multiplier instead.
http://i.imgur.com/xohIbX1.png

Now, when I use goal seek...

http://i.imgur.com/ImsfvQK.png

it does a pretty good job of finding an exact percentage and estimated budget!
http://i.imgur.com/uAsQGO6.png

Now, this means that with a budget of 3.25m (not including grinders or ticket items) you have a 95% chance of going from 0 to 10 within budget. With a 4m budget, you have a 99% chance of going from 0 to +10.

http://i.imgur.com/T4JsQOh.png

This is, again, using the optimal procedure, mapped out here:
[SPOILER-BOX]http://i.imgur.com/6abpLRU.png[/SPOILER-BOX]

I'll upload and put a link up to the newer version in a bit. Doing a few touchups and reorganizing things.

Shinmarizu
Apr 25, 2013, 03:35 PM
In other words: pick your favourite 10*. Save 4 million meseta as your working budget (with or without grinders and supplements, your call), walk up to Dudu and let 'er rip.

If you have not run out of money before your wallet is empty, congratulations. (Note to skeptics: 95%, 99% or 99.999999% isn't 100%. There will be instances where this doesn't work.)

I am grateful that you've laid down the spreadsheets for this because these can all be adjusted for (1) the emergence of 11* weapons, (2) whenever someone at Sega decides to change the rules, and (3) market variation, partly because someone at Sega is changing the rules or screwing with the frequency of getting supplements.

Stats really do put things in perspective. Kudos.

UnLucky
Apr 25, 2013, 05:14 PM
Very, very nice work


In other words: pick your favourite 10*. Save 4 million meseta as your working budget (with or without grinders and supplements, your call), walk up to Dudu and let 'er rip.
You kind of have to have supplements to follow his optimal path which the budget calc depends on

Walkure
Apr 25, 2013, 07:02 PM
Revised the previous link so that it has the truncated distribution rather than the plain normal distribution, or here (https://docs.google.com/file/d/0B14_gG8wPfu_eGlDbF9mVEE2dFU/edit?usp=sharing).
I am grateful that you've laid down the spreadsheets for this because these can all be adjusted for (1) the emergence of 11* weapons, (2) whenever someone at Sega decides to change the rules, and (3) market variation, partly because someone at Sega is changing the rules or screwing with the frequency of getting supplements.

Stats really do put things in perspective. Kudos.

There are 11* and 12* weapons out already, but apparently not enough data collected for them. Makes sense, though since those weapons are all annoyingly rare.

MoonAtomizer
May 6, 2013, 12:48 AM
I did an experimental simulation of your method (protect at 7, boost at 9) and got this distribution:

http://i.imgur.com/mK15vw5.jpg

MEAN: ~1430000
MEDIAN: ~1130000
99th: ~5060000
HIGH: ~12630000 (lol)

*Using the costs you listed
*Source for success/penalty rates (http://pso2osusume.com/kyouka-custom/rare10buki-kanwamae-kanwago-hikaku/)

For comparing grinding strategies, looking at the mean is good enough. However, because the variance is so large, it is more practical for somebody to know the median rather than the mean when evaluating "relative luck". Of course, getting a median would either require a large random sample or a large number of simulations. From quickly glancing the thread, I am assuming you achieved all your numbers through theoretical calculations.

Good work so far.

Narrillnezzurh
May 7, 2013, 04:55 PM
You kind of have to have supplements to follow his optimal path which the budget calc depends on

I assume he means you can either start with supplements or not start with supplements, since the 4mil includes the price of supplements.

Arrow
May 7, 2013, 08:27 PM
You know what's sad with society these days?
People say I do crazy magical stuff with excel.
And I'm just doing basic formulae and if functions.
Most people don't even know that it can do stuff like this...

Anyway, brilliant work here. Worthy of a sticky I'd say.

UnLucky
May 7, 2013, 08:51 PM
why use Excel when I have Word and Calculator??

Walkure
May 21, 2013, 01:05 AM
Final update with pretty much both drive-viewable and downloadable versions having equivalent prowess. Downloading the drive version will probably mess up the budget calculations, as uploading them definitely required changing around some formula.

Rates were updated, and so I updated them for both versions. Costs changed around so that a 5% ticket is just barely more expensive than not using one.

I decided to emphasize how much using (+1) protection support lowers costs by having a totally unoptimized version of going to +10. I figured that having only the optimized average costs was a bit misleading.

Cheers