Level 12 quest spreadsheet
Level 12 quest spreadsheet
Posting this here so I have a public permalink for this file.
Updated 10/07/07
Presenting Bazzle's level 12 optimizer spreadsheet, version 0.7 . The latest version has a number of updates added by myself and QuantumNightmare.
All of the calculations are (or at least should be) up to date with current spading knowledge. You can download the latest update in Excel format here. An OpenOffice version is also available.
How it works:
This file allows you to specify what kinds of bonuses your character has going into the quest, and then play around with different routes to determine what's optimal for you. There's no single best route through the level 12 quest. It depends a great deal on what skills you have and what other resources are available to you.
This spreadsheet allows you to input your ++combat bonus, item and meat drop bonuses, number of inhalers, familiar weight, and so on, and see which route is best. It will automatically determine how many turns you'll spend on the battlefield and (on average) on each sidequest for whichever route you enter, and sum up your total turns. It even autocalculates several of the routes that I've determined are the only serious options, so you can look at them all at a glance. Plus, there's an arena checker which will tell you if you have enough monsters to fight on-path to complete the arena, or if you're going to have to spend turns somewhere else to finish it.
This file is intended primarily as a tool to help you compare routes relative to each other.
User guide:
IMPORTANT: All inputs should be whole numbers. If you have an item drop bonus of 50%, put 50 in the box, NOT 0.5.
Skills and bonuses:
Put the max bonus you're capable of running when you do each sidequest. Assume you're doing each one separately and independently. If you have access to Knob Goblin Eyedrops, for instance, don't worry about including the -meat penalty in your +meat bonus. Also, do not include inhalers in your +meat bonus. I give them special treatment because of the short duration. Right now current spading on the ducks indicates those adventures are superlikely, not noncombat. Thus --combat is used only for the GMoB calculations and irrelevant if you don't use it. Odor extractors are used only for the calculation of 8-bit and Goatlet turns so far.
Areas undone:
Listed here are 6 zones that are necessary for a hardcore run, and that there's at least some chance you might not have finished by the time you start the quest. Put a 1 next to each zone if you still need to do it, otherwise put a 0. These numbers are used to calculate the feasibility of the arena. For the Middle Chamber, the left cell accepts 1/0, the right accepts the number of wheels remaining, in case it's partially done. There's also a line where you can indicate whether or not the Guy Made of Bees is set up and waiting.
Familiar weight:
Should be self explanatory. Input your leprechaun and familiar weight, including extra weight that you can get from skills, equipment, snowcones, or whatever else.
Sidequests:
Here you can choose which route to take through the quest. Put a 1 next to the sidequests you want to do, and a 0 next to the ones you want to skip. As you change these values, the spreadsheet will recalculate your total turns.
Risk aversion:
2 of the possible sidequests are much more RNG-dependent than others. You can put a 1 in this box to redo the calculations using a more pessimistic estimate of their turncounts, to see if your optimal route changes.
Reading the output:
There are a few different sections of output. The most important is marked "Total Turns". This tells you the expected number of turns the quest will take, given your sidequest selections. It gives you both a frat and a hippy number. Next to the sidequests section you can see the expected number of turns each sidequest will take, given the bonuses you've input on the left. This might be helpful for thinking about which routes to check. The total turns calculation adds up the turns played on the sidequests you've chosen, and also calculates how many turns the battlefield will take you if you do just those sidequests. This assumes that you complete each sidequest immediately upon it becoming available.
Below that is the arena checker. The numbers given there tell you how much ML will still need to be pasted with flyers to complete the arena quest, after you go through all of the areas available to you on the route you've chosen. Note that frat warriors can access the combat areas of the filthworm and duck sidequests without opening them up on the battlefield first, making them viable pasting areas.
If the arena checker is zero, the route you've picked should probably give you enough encounters to complete the arena. If not, you won't be able to do the arena without wasting turns. The surplus turns column to the right tells you how many 170 ML fights (plus your ML bonus) you'll still have to engage in to complete it. This column is included in the total turns calculation.
There are some additional diagnostics you can check. If you scroll to the right, you'll see some information about the RNG-swinginess of the beach and filthworm sidequests.
The beach info gives you the standard deviation of the distribution. This is a statistical measure of the spread of possible outcomes, if it's smaller, you're more likely to come close to the reported turncount. If you don't know how to interpret this number, scroll farther right. The sheet also gives you a rough breakdown of how likely certain ranges of turns should be.
For the filthworms, the sheet tells you the combined probability of going 10 turns in either of the middle zones without getting the needed gland to drop, and having to start over. The expected turncount also takes this possibility into account. Thanks to QuantumNightmare's work this is quite accurate.
The GMoB now, by request, gets rolled into these calculations if it's available and the arena is one of your sidequests. The sheet will calculate the expected number of turns needed to get to the GMoB (based on --combat), and add either that or the surplus turns at 170 ML needed to finish the Arena to your count, whichever is lower. It will also indicate whether you should do, or skip, the GMoB on a given route. Please be advised that this does not account for the number of turns invested in setting up the GMoB in the first place. According to QN's leveling sheet you're sacrificing about 17 mainstat per turn as a level 9 Myst class by leveling in the Bathroom instead of the Gallery. Since it takes so many turns to get the GMoB 4 times, I imagine this is never, or nearly never, optimal as a strategy for the whole run. Nevertheless, you can now calculate the turns saved on the quest once it's set up.
Finally, there are some preset routes. This lists 10 reasonable routes, so that you can compare each one's turncount at a glance. The notation should be easy enough to figure out. Frat AJB-F is a route where you play as a frat warrior and do the Arena, Junkyard, Beach, and Filthworm sidequests. The rest are similar. These also give arena checker and GMoB strategy (where applicable) beside each route.
Notes:
Advanced players who have the two +combat skills are going to generally find that Frat: AJB-F or AJB-FN are optimal. Frat: AJ-FN or AJ-FND usually turn out to be the best routes if you do not have +combat skills. Hippy: DNF-J is slightly suboptimal but pretty competitive if you don't want to deal with the arena.
As a Frat, it's generally best to skip the ducks unless you need to do them to finish the arena. Yes, before you ask, this is even assuming you use the chaos butterfly trick to make it only take 15 turns. The reason for this is that, including the barn, the duck sidequest is at least 18 turns long, and since the choice adventures appear to be unaffected by --combat, the total turns you expect to spend there is higher than that. Right now it's assumed a flat 24. With 4 other sidequests done, though, you only lose 16 battlefield turns by skipping it, meaning you typically save a handful of turns by doing so.
I hope some people find this useful. Feedback is welcome.
Updated 10/07/07
Presenting Bazzle's level 12 optimizer spreadsheet, version 0.7 . The latest version has a number of updates added by myself and QuantumNightmare.
All of the calculations are (or at least should be) up to date with current spading knowledge. You can download the latest update in Excel format here. An OpenOffice version is also available.
How it works:
This file allows you to specify what kinds of bonuses your character has going into the quest, and then play around with different routes to determine what's optimal for you. There's no single best route through the level 12 quest. It depends a great deal on what skills you have and what other resources are available to you.
This spreadsheet allows you to input your ++combat bonus, item and meat drop bonuses, number of inhalers, familiar weight, and so on, and see which route is best. It will automatically determine how many turns you'll spend on the battlefield and (on average) on each sidequest for whichever route you enter, and sum up your total turns. It even autocalculates several of the routes that I've determined are the only serious options, so you can look at them all at a glance. Plus, there's an arena checker which will tell you if you have enough monsters to fight on-path to complete the arena, or if you're going to have to spend turns somewhere else to finish it.
This file is intended primarily as a tool to help you compare routes relative to each other.
User guide:
IMPORTANT: All inputs should be whole numbers. If you have an item drop bonus of 50%, put 50 in the box, NOT 0.5.
Skills and bonuses:
Put the max bonus you're capable of running when you do each sidequest. Assume you're doing each one separately and independently. If you have access to Knob Goblin Eyedrops, for instance, don't worry about including the -meat penalty in your +meat bonus. Also, do not include inhalers in your +meat bonus. I give them special treatment because of the short duration. Right now current spading on the ducks indicates those adventures are superlikely, not noncombat. Thus --combat is used only for the GMoB calculations and irrelevant if you don't use it. Odor extractors are used only for the calculation of 8-bit and Goatlet turns so far.
Areas undone:
Listed here are 6 zones that are necessary for a hardcore run, and that there's at least some chance you might not have finished by the time you start the quest. Put a 1 next to each zone if you still need to do it, otherwise put a 0. These numbers are used to calculate the feasibility of the arena. For the Middle Chamber, the left cell accepts 1/0, the right accepts the number of wheels remaining, in case it's partially done. There's also a line where you can indicate whether or not the Guy Made of Bees is set up and waiting.
Familiar weight:
Should be self explanatory. Input your leprechaun and familiar weight, including extra weight that you can get from skills, equipment, snowcones, or whatever else.
Sidequests:
Here you can choose which route to take through the quest. Put a 1 next to the sidequests you want to do, and a 0 next to the ones you want to skip. As you change these values, the spreadsheet will recalculate your total turns.
Risk aversion:
2 of the possible sidequests are much more RNG-dependent than others. You can put a 1 in this box to redo the calculations using a more pessimistic estimate of their turncounts, to see if your optimal route changes.
Reading the output:
There are a few different sections of output. The most important is marked "Total Turns". This tells you the expected number of turns the quest will take, given your sidequest selections. It gives you both a frat and a hippy number. Next to the sidequests section you can see the expected number of turns each sidequest will take, given the bonuses you've input on the left. This might be helpful for thinking about which routes to check. The total turns calculation adds up the turns played on the sidequests you've chosen, and also calculates how many turns the battlefield will take you if you do just those sidequests. This assumes that you complete each sidequest immediately upon it becoming available.
Below that is the arena checker. The numbers given there tell you how much ML will still need to be pasted with flyers to complete the arena quest, after you go through all of the areas available to you on the route you've chosen. Note that frat warriors can access the combat areas of the filthworm and duck sidequests without opening them up on the battlefield first, making them viable pasting areas.
If the arena checker is zero, the route you've picked should probably give you enough encounters to complete the arena. If not, you won't be able to do the arena without wasting turns. The surplus turns column to the right tells you how many 170 ML fights (plus your ML bonus) you'll still have to engage in to complete it. This column is included in the total turns calculation.
There are some additional diagnostics you can check. If you scroll to the right, you'll see some information about the RNG-swinginess of the beach and filthworm sidequests.
The beach info gives you the standard deviation of the distribution. This is a statistical measure of the spread of possible outcomes, if it's smaller, you're more likely to come close to the reported turncount. If you don't know how to interpret this number, scroll farther right. The sheet also gives you a rough breakdown of how likely certain ranges of turns should be.
For the filthworms, the sheet tells you the combined probability of going 10 turns in either of the middle zones without getting the needed gland to drop, and having to start over. The expected turncount also takes this possibility into account. Thanks to QuantumNightmare's work this is quite accurate.
The GMoB now, by request, gets rolled into these calculations if it's available and the arena is one of your sidequests. The sheet will calculate the expected number of turns needed to get to the GMoB (based on --combat), and add either that or the surplus turns at 170 ML needed to finish the Arena to your count, whichever is lower. It will also indicate whether you should do, or skip, the GMoB on a given route. Please be advised that this does not account for the number of turns invested in setting up the GMoB in the first place. According to QN's leveling sheet you're sacrificing about 17 mainstat per turn as a level 9 Myst class by leveling in the Bathroom instead of the Gallery. Since it takes so many turns to get the GMoB 4 times, I imagine this is never, or nearly never, optimal as a strategy for the whole run. Nevertheless, you can now calculate the turns saved on the quest once it's set up.
Finally, there are some preset routes. This lists 10 reasonable routes, so that you can compare each one's turncount at a glance. The notation should be easy enough to figure out. Frat AJB-F is a route where you play as a frat warrior and do the Arena, Junkyard, Beach, and Filthworm sidequests. The rest are similar. These also give arena checker and GMoB strategy (where applicable) beside each route.
Notes:
Advanced players who have the two +combat skills are going to generally find that Frat: AJB-F or AJB-FN are optimal. Frat: AJ-FN or AJ-FND usually turn out to be the best routes if you do not have +combat skills. Hippy: DNF-J is slightly suboptimal but pretty competitive if you don't want to deal with the arena.
As a Frat, it's generally best to skip the ducks unless you need to do them to finish the arena. Yes, before you ask, this is even assuming you use the chaos butterfly trick to make it only take 15 turns. The reason for this is that, including the barn, the duck sidequest is at least 18 turns long, and since the choice adventures appear to be unaffected by --combat, the total turns you expect to spend there is higher than that. Right now it's assumed a flat 24. With 4 other sidequests done, though, you only lose 16 battlefield turns by skipping it, meaning you typically save a handful of turns by doing so.
I hope some people find this useful. Feedback is welcome.
Last edited by Bazzle on Wed Oct 10, 2007 3:03 pm, edited 7 times in total.
Tech notes:
You don't need to read this unless you're curious about how the numbers are calculated.
Arena: Assumed zero, unless checker > 0, in which case surplus turns is checker/170+ML. Why 170? You can always fight gremlins. Note that filthworm hatchings have ML 165.
Junkyard: Always 12. This is the expected value assuming a 1/4 chance of getting the right gremlin. The exact rate of getting the right gremlin hasn't been spaded out yet but I think 1/4 is reasonable. It seems to me there's generally 2 wrong gremlins in each zone, plus 2 identical-looking copies of the tool-using one.
Beach: 5/(.1 + ++combat).
Filthworms: This assumes all zones have a 11% drop rate, as per spading on the wiki for the hatching and feeding chambers. Rates for the guards haven't been spaded, but are probably similar.
Nuns: Assumes 1100 base meat drop from bandits. Inhalers are handled separately and very accurately.
Ducks: Assumes 15 turns to clear the ducks, plus 9 turns in the barn. No impact from skills because noncombat rate doesn't appear to have any effect.
Battlefield turns:
Thanks to QN these should be completely accurate for all sidequest combinations.
Arena checker:
This is pretty simple, really. It just takes the average ML for each area, adds the ML bonus, and multiplies by expected combat turns spent there. I also calculate expected turns for HitS, Middle Chamber, Valley, and Goatlet taking into account -combats for Middle Chamber, and item drop bonuses/familiar weight for the other three. The ML used for the Middle Chamber calculation maxes out at 10 so as not to interfere with --combat. I may go back and allow more +ML if --combat is 0, but this isn't a high priority at the moment.
Odor extractors:
Assumed encounter rates for bloopers and dairy goats are 40%, 75% respectively, when the extractor option is on.
Familiar weight:
I use the formula on the wiki for the leprechaun. For the fairy I use the formula from the AFH fairy spading thread, 0.0167x + 0.1667 when weight is above 20, and 2.5*weight for weight below 20.
GMoB:
Assumes 75% combat rate in the Bathroom, with 1/3 chance of GMoB when a noncombat is drawn.
As you can see, the calculations done in the spreadsheet rely largely on spading work done by others. I've just accumulated all the currently available knowledge in one place. Thanks to those people for their work.
You don't need to read this unless you're curious about how the numbers are calculated.
Arena: Assumed zero, unless checker > 0, in which case surplus turns is checker/170+ML. Why 170? You can always fight gremlins. Note that filthworm hatchings have ML 165.
Junkyard: Always 12. This is the expected value assuming a 1/4 chance of getting the right gremlin. The exact rate of getting the right gremlin hasn't been spaded out yet but I think 1/4 is reasonable. It seems to me there's generally 2 wrong gremlins in each zone, plus 2 identical-looking copies of the tool-using one.
Beach: 5/(.1 + ++combat).
Filthworms: This assumes all zones have a 11% drop rate, as per spading on the wiki for the hatching and feeding chambers. Rates for the guards haven't been spaded, but are probably similar.
Nuns: Assumes 1100 base meat drop from bandits. Inhalers are handled separately and very accurately.
Ducks: Assumes 15 turns to clear the ducks, plus 9 turns in the barn. No impact from skills because noncombat rate doesn't appear to have any effect.
Battlefield turns:
Thanks to QN these should be completely accurate for all sidequest combinations.
Arena checker:
This is pretty simple, really. It just takes the average ML for each area, adds the ML bonus, and multiplies by expected combat turns spent there. I also calculate expected turns for HitS, Middle Chamber, Valley, and Goatlet taking into account -combats for Middle Chamber, and item drop bonuses/familiar weight for the other three. The ML used for the Middle Chamber calculation maxes out at 10 so as not to interfere with --combat. I may go back and allow more +ML if --combat is 0, but this isn't a high priority at the moment.
Odor extractors:
Assumed encounter rates for bloopers and dairy goats are 40%, 75% respectively, when the extractor option is on.
Familiar weight:
I use the formula on the wiki for the leprechaun. For the fairy I use the formula from the AFH fairy spading thread, 0.0167x + 0.1667 when weight is above 20, and 2.5*weight for weight below 20.
GMoB:
Assumes 75% combat rate in the Bathroom, with 1/3 chance of GMoB when a noncombat is drawn.
As you can see, the calculations done in the spreadsheet rely largely on spading work done by others. I've just accumulated all the currently available knowledge in one place. Thanks to those people for their work.
Last edited by Bazzle on Mon Oct 08, 2007 8:21 am, edited 4 times in total.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
You can trust the leprechaun formula we've put together, but there is no fairy formula right now. The equation you've referenced is only valid over 20 pounds, and we're still spading what happens from 1-20 (which is something completely different). You're probably best off using the NS-11 fairy formula for those first 20 pounds, until something new comes along.Bazzle wrote:Familiar weight:
I use the formula on the wiki for the leprechaun. For the fairy I use the formula from the AFH fairy spading thread, 0.0167x + 0.1667.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
Medicine ball noncombat (where the GMoB comes from) occurs on 1/3rd of noncombats, not 1/4th.Bazzle wrote:GMoB:
Assumes 75% combat rate in the Bathroom, with 1/4 chance of GMoB when a noncombat is drawn.
I should have time to go over this post in more detail, just pointing out what comes to mind right away that can help make your sheet more accurate.
In the case that it might help with putting in the BeeMan:
Getting the encounter Having a Medicine Ball 5 times with ...
These numbers were not right. My simulator had problematic appearance rates. See this post. But it should be fixed now, so I added newer and better numbers.
... +0% noncombat rate: 64.1 turns
... +5% noncombat rate: 53.8 turns
... +10% noncombat rate: 45.8 turns
... +15% noncombat rate: 40.3 turns
... +20% noncombat rate: 35.7 turns
... +25% noncombat rate: 32.2 turns
... +0% noncombat rate: 58.6 turns
... +5% noncombat rate: 48.8 turns
... +10% noncombat rate: 42.1 turns
... +15% noncombat rate: 36.6 turns
... +20% noncombat rate: 32.5 turns
... +25% noncombat rate: 29.4 turns
Of course, you can queue him up while leveling, so these turns aren't totally flushed down the toilet, but more numbers are always better, I'd say.
Getting the encounter Having a Medicine Ball 5 times with ...
These numbers were not right. My simulator had problematic appearance rates. See this post. But it should be fixed now, so I added newer and better numbers.
... +0% noncombat rate: 64.1 turns
... +5% noncombat rate: 53.8 turns
... +10% noncombat rate: 45.8 turns
... +15% noncombat rate: 40.3 turns
... +20% noncombat rate: 35.7 turns
... +25% noncombat rate: 32.2 turns
... +0% noncombat rate: 58.6 turns
... +5% noncombat rate: 48.8 turns
... +10% noncombat rate: 42.1 turns
... +15% noncombat rate: 36.6 turns
... +20% noncombat rate: 32.5 turns
... +25% noncombat rate: 29.4 turns
Of course, you can queue him up while leveling, so these turns aren't totally flushed down the toilet, but more numbers are always better, I'd say.
Last edited by Flolle on Mon Sep 17, 2007 7:10 pm, edited 2 times in total.
Spreadsheet broken?
When clicking on your link, Excel gives "File Error: Data may be lost".
I am using Excel from Office 2003, downloading through firefox.
There are several cells that are defined as =#N/A - this is probably unintentional.
I am using Excel from Office 2003, downloading through firefox.
There are several cells that are defined as =#N/A - this is probably unintentional.
Hmm. Well, first, I've updated the file to fix the Bathroom rate, and tweak a couple of ML calculations to match wiki data. Regarding the broken file issue, I've tried fiddling with a couple of things that might fix it. Unfortunately I'm doing this in OpenOffice and exporting to Excel, so if something breaks it's hard for me to troubleshoot it. If you still can't get it to work, try opening the OpenOffice file, which I've now uploaded here.
If anyone's still getting a similar problem, please post, and if you could, please indicate which cells are showing up as N/As. Thanks.
If anyone's still getting a similar problem, please post, and if you could, please indicate which cells are showing up as N/As. Thanks.
DarthDud wrote:Please factor in the fact that you can "do" the duck and orchard sidequests as a frat before starting the battlefield.
Already accounted for. The ML you can get from those zones counts towards your pasting quota for the arena (if selected), and since you can't officially complete them until you reach them in uniform the battlefield calculations are the same either way.
Edit: User guide is now contained in the spreadsheet file.
Pixels should be an option for areas undone. Both with/without odor.
Also, if the middle chamber provided an option wherein you set how many wheel turns you are going to do, and it factors ML as if you're only using +10 (MCD) there (so as not to kill noncombats), then that would also be awesome. Because in the current form, the middle chamber toggle is not especially useful.
Also, AJB-FD should be a preset.
Furthermore, it would be reasonable if any method which involved 1 inhaler and the nuns auto-added +1 to turncount, due to the turn getting the inhaler.
Also, if the middle chamber provided an option wherein you set how many wheel turns you are going to do, and it factors ML as if you're only using +10 (MCD) there (so as not to kill noncombats), then that would also be awesome. Because in the current form, the middle chamber toggle is not especially useful.
Also, AJB-FD should be a preset.
Furthermore, it would be reasonable if any method which involved 1 inhaler and the nuns auto-added +1 to turncount, due to the turn getting the inhaler.
Okay, new update today.
All of this has been added.
I don't want to include this. First, even though I think you'd argue it's a bad choice, they can be pulled, so I don't like an auto-added turn. And I'd rather have the sheet simply give expected turns for a given set of resources rather than including the time spent getting those resources. That way a player can look at the numbers and judge for themselves what's worth pre-farming.
DarthDud wrote:Pixels should be an option for areas undone. Both with/without odor.
Also, if the middle chamber provided an option wherein you set how many wheel turns you are going to do, and it factors ML as if you're only using +10 (MCD) there (so as not to kill noncombats), then that would also be awesome. Because in the current form, the middle chamber toggle is not especially useful.
Also, AJB-FD should be a preset.
All of this has been added.
DarthDud wrote:Furthermore, it would be reasonable if any method which involved 1 inhaler and the nuns auto-added +1 to turncount, due to the turn getting the inhaler.
I don't want to include this. First, even though I think you'd argue it's a bad choice, they can be pulled, so I don't like an auto-added turn. And I'd rather have the sheet simply give expected turns for a given set of resources rather than including the time spent getting those resources. That way a player can look at the numbers and judge for themselves what's worth pre-farming.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
Linky.
I know I'm treading on dangerous ground when I do this, but explaining the changes I wanted made would have been harder than doing them myself. Here's a new version of your great sheet, with two changes: One cosmetic, the other functional.
- Best preset direction is now highlighted using conditional formatting. Very minor, but I like colour.
- The sheet no longer calculates up to 960 kills. I fixed the time when each subzone opens, so that you now calculate up to 1000 kills. I also updated the battlefield length of each preset, so as to show the correct values.
Summary: cells A48 to B57 are new. Cells E50 to E57 are updated. Cells D65 to D74 are updated. Cells G23 to G33 were given a minor conditional formatting.
Also, if you'd explain to me what the "negative binomial distribution" thing is and how it would help in calculating the beach, I might be able to throw something together for you. I'm posting this not to steal this thread, but to point out some changes that can be incorporated into your official version of the sheet.
I know I'm treading on dangerous ground when I do this, but explaining the changes I wanted made would have been harder than doing them myself. Here's a new version of your great sheet, with two changes: One cosmetic, the other functional.
- Best preset direction is now highlighted using conditional formatting. Very minor, but I like colour.
- The sheet no longer calculates up to 960 kills. I fixed the time when each subzone opens, so that you now calculate up to 1000 kills. I also updated the battlefield length of each preset, so as to show the correct values.
Summary: cells A48 to B57 are new. Cells E50 to E57 are updated. Cells D65 to D74 are updated. Cells G23 to G33 were given a minor conditional formatting.
Also, if you'd explain to me what the "negative binomial distribution" thing is and how it would help in calculating the beach, I might be able to throw something together for you. I'm posting this not to steal this thread, but to point out some changes that can be incorporated into your official version of the sheet.
I have no problem with you doing this, QN, your changes look great. I'll update the top post with your link.
To answer your question: the negative binomial distribution (wikipedia) is similar to the geometric, but instead of describing the probability of n-1 successes before 1 failure, it describes the probability of n-k successes before k failures. Doing the trick of 5/prob is equivalent to adding together 5 independent geometric distributions, but this isn't quite right because all 5 failures are at risk at the same time and so they're not totally independent. The negative binomial describes the real underlying distribution for this problem. Unfortunately Excel's NEGBINOMDIST function doesn't actually use the negative binomial, it just does the geometric approximation.
I might be underestimating Excel, but I think it's near-impossible to actually model the true distribution there, since it involves taking a definite integral. As I say in the writeup, though, this isn't that substantial. In the most extreme case the difference is 3 adventures, and for values with more +combat it's going to approximate the real value very closely.
To answer your question: the negative binomial distribution (wikipedia) is similar to the geometric, but instead of describing the probability of n-1 successes before 1 failure, it describes the probability of n-k successes before k failures. Doing the trick of 5/prob is equivalent to adding together 5 independent geometric distributions, but this isn't quite right because all 5 failures are at risk at the same time and so they're not totally independent. The negative binomial describes the real underlying distribution for this problem. Unfortunately Excel's NEGBINOMDIST function doesn't actually use the negative binomial, it just does the geometric approximation.
I might be underestimating Excel, but I think it's near-impossible to actually model the true distribution there, since it involves taking a definite integral. As I say in the writeup, though, this isn't that substantial. In the most extreme case the difference is 3 adventures, and for values with more +combat it's going to approximate the real value very closely.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
Warning: math intensive for the sake of being math intensive
I read through the wiki link you sent, and our case is almost identical to the example they give with selling chocolate bars at the bottom of the page: given a 40% chance of selling a chocolate at any given house, how many houses will you need to go to in order to sell 5. I threw together a spreadsheet to sum up the series of (odds of getting all 5 lob-frog-men in N turns) * N using the probabilities from the pascal distribution and it gave the exact same odds as just taking 5/rate. Pascal distribution seems to be a simplified negative binomial distribution with an integer desired # of successes.
The 5 combats are completely unrelated events, none of them change the odds of the others happening. There's no reason why 5/rate shouldn't work in this case, unless I'm missing something.
I read through the wiki link you sent, and our case is almost identical to the example they give with selling chocolate bars at the bottom of the page: given a 40% chance of selling a chocolate at any given house, how many houses will you need to go to in order to sell 5. I threw together a spreadsheet to sum up the series of (odds of getting all 5 lob-frog-men in N turns) * N using the probabilities from the pascal distribution and it gave the exact same odds as just taking 5/rate. Pascal distribution seems to be a simplified negative binomial distribution with an integer desired # of successes.
The 5 combats are completely unrelated events, none of them change the odds of the others happening. There's no reason why 5/rate shouldn't work in this case, unless I'm missing something.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
I knew that pascal distribution looked familiar. Pootin and I seem to have reinvented the wheel while spading the effects of combat modifiers on expected turncounts a few months back. It's good to have someone who actually understands these things enough to point out what equations fit where...
(of course I was missing a constant -1, but we'll ignore that point).
(of course I was missing a constant -1, but we'll ignore that point).
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
I'd like to add a new feature, but I don't have time to work out the math just right now. The filthworms uses a basic 3/rate+1 approximation to find the expected length of the quest, but this doesn't include the possibility of getting screwed on the 2nd and 3rd glands. The first basic feature I'd add would be the % chance of getting screwed on either of the last 2 glands: X=(1-droprate)^10. The odds of not getting screwed on either of the glands is then Y=(1-X)^2=(1-(1-droprate)^10)^2.
The second feature would be to include the possibility of being screwed into calculations of the average turn-length. This second calculation is harder: a pascal distribution would be used to calculate the expected turnlength, but every time N=10 is reached on the last two glands the count would need to begin again. I'll work on this math problem sometime this week, but is there any statistical distribution that could help?
Basically, I'd like to give numbers to the odds of being screwed by the beach and filthworms, so we can make a more educated decision whether it's worthwhile or not.
The second feature would be to include the possibility of being screwed into calculations of the average turn-length. This second calculation is harder: a pascal distribution would be used to calculate the expected turnlength, but every time N=10 is reached on the last two glands the count would need to begin again. I'll work on this math problem sometime this week, but is there any statistical distribution that could help?
Basically, I'd like to give numbers to the odds of being screwed by the beach and filthworms, so we can make a more educated decision whether it's worthwhile or not.
- KujjieKujjieKoo
- Spade Ninja
- Posts: 2240
- Joined: Mon Apr 30, 2007 8:50 pm
- Location: !Chicago
- Contact:
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
Thanks kujjie. And thanks bazzle again, for letting me edit this. I've went ahead and implemented the two changes I posted about earlier. Now I just need to think of a way to measure screwage possibility at the beach.
The base rate for glands has been changed to 10%, there is now a display for the odds of not getting screwed by filthworms at your given values of +items, and the expected turncount in the filthworm is slightly (2-7 turns) increased due to the possibility of having to restart.
Odds of not getting screwed are the odds that both of the last 2 glands drop while you are still under the effect of the gland that gives access to the advanced zones. If you are screwed, you need to restart back at the first area.
The turncount for the filthworms was calculated in an odd way. The first gland takes an average of 1/rate turns to appear. The next gland has an expected turncount of 1/rate, but if it takes more than 10 turns then I added the expected turncount for the first gland to drop... since you need to go find that gland all over again. The last gland has an expected turncount of 1/rate, but again if it takes longer than 10 turns I add the expected turncount to get both the previous glands.
This means that with +105% items applied, we expect the first gland to take 4.65 turns, second to take 5.10 turns, and third gland to take 5.60 turns. Total turncount is then roundup(4.65+5.1+5.6)+1=17. Previous calculated turncount was 16. I don't think we need to round up each individual turncount, but I might be wrong.
The base rate for glands has been changed to 10%, there is now a display for the odds of not getting screwed by filthworms at your given values of +items, and the expected turncount in the filthworm is slightly (2-7 turns) increased due to the possibility of having to restart.
Odds of not getting screwed are the odds that both of the last 2 glands drop while you are still under the effect of the gland that gives access to the advanced zones. If you are screwed, you need to restart back at the first area.
The turncount for the filthworms was calculated in an odd way. The first gland takes an average of 1/rate turns to appear. The next gland has an expected turncount of 1/rate, but if it takes more than 10 turns then I added the expected turncount for the first gland to drop... since you need to go find that gland all over again. The last gland has an expected turncount of 1/rate, but again if it takes longer than 10 turns I add the expected turncount to get both the previous glands.
This means that with +105% items applied, we expect the first gland to take 4.65 turns, second to take 5.10 turns, and third gland to take 5.60 turns. Total turncount is then roundup(4.65+5.1+5.6)+1=17. Previous calculated turncount was 16. I don't think we need to round up each individual turncount, but I might be wrong.
KujjieKujjieKoo wrote:If it helps any, Darth and I checked the drop rate on the glands on night in chat, came up with a 10% base rate with a sample size of 500ish. I can do another 500 turns if necessary, but with a 10% +/- 1.3% error, and that it's a 10 turn effect, I feel the 10% is an accurate number.
Granted, we only checked the drop rate in the first zone. >_>
Now I just need to think of a way to measure screwage possibility at the beach.
You could just report the standard deviation of the distribution next to the turncount. It's sqrt(5*(1-p)/(p^2)).
Oh, and before your last update I had written up a little Monte Carlo simulation of this process in one of my stats programs. The numbers it gives match the ones you've come up with in the new version of the sheet almost exactly, both for mean turns and screwage rate. Nice work!
One thing we might want to think about: this is a case where the mean might not be best. The median turns are always lower, sometimes substantially so. So, e.g., even though your expected turns spent with no item drop is 50, half the time it will take you 37 or less. I think since more information is better than less I might see if I can find a simple function that approximates the median and include that in there somewhere.
I was just thinking about there being something like this earlier today, while making my way through the level 12 quest. Since the wiki really has no advice whatsoever on how to do it optimally (turnwise, anyway) this is a fantastic idea. It's awesome that we have people like you in our clan, Bazzle.
Seriously, you rock. And everyone else that's all spadecore and intelligent and shit (which I think is probably a majority of us) - it's stuff like this that makes this shit seriously fun for me. Keep up the great work!
Seriously, you rock. And everyone else that's all spadecore and intelligent and shit (which I think is probably a majority of us) - it's stuff like this that makes this shit seriously fun for me. Keep up the great work!
-Jorgensen-
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
I can logically find my way through statistics, but I have no real understanding of what a standard of deviation is. You might want to "reclaim" your sheet and edit that in yourself.
Mean is the expected turnlength - averaged out per run, that's how long it will take. If the median is very different, that's a pretty good measure of screwage right there, as median is much less influenced by the long tail of possible screwages. 50% of the time, the filthworms will take 37 turns. The other 50%, it'll take much much more. Do you want to plan a run around the 50% of the time that you get good luck? Because of this I still think mean is more useful.
I think a quick number like my "odds of not getting screwed" is an easy and intuitive way of making people understand the risks they run with low +item drops. If we can make a similar quick and easy number for the beach, we're all set. I don't think resorting to median will help with this.
OK, your turn. Convince me why median is the way to go
Mean is the expected turnlength - averaged out per run, that's how long it will take. If the median is very different, that's a pretty good measure of screwage right there, as median is much less influenced by the long tail of possible screwages. 50% of the time, the filthworms will take 37 turns. The other 50%, it'll take much much more. Do you want to plan a run around the 50% of the time that you get good luck? Because of this I still think mean is more useful.
I think a quick number like my "odds of not getting screwed" is an easy and intuitive way of making people understand the risks they run with low +item drops. If we can make a similar quick and easy number for the beach, we're all set. I don't think resorting to median will help with this.
OK, your turn. Convince me why median is the way to go
I've made an update, though I'm still fiddling with one element of it a bit. It adds a couple things: the standard deviation of the beach distribution (smaller is better) and a very rough thing that defines ranges and assigns probabilities to let you look at the actual shape of the distribution. This is the thing I'm still playing with, it's not accurate for high ++combat yet.
I also put in a risk aversion toggle. If you put a 1 in there, it'll use pessimistic numbers for the beach and filthworms (base turns*1.5, rounded up).
I haven't included any median info yet, though it'll be implicit in the range thing once I get it working right.
Edit: Okay, the range thing is close enough for government work. It's not doing exact calculations, I'm just taking advantage of the fact that the density in ranges from 0 to X/2, X/2 to 1, 1 to 3X/2, and 3X/2 to infinity doesn't actually change all that much as X changes (where X is mean turncount). This is never going to be off my more than a few percent for attainable values of ++combat.
I also put in a risk aversion toggle. If you put a 1 in there, it'll use pessimistic numbers for the beach and filthworms (base turns*1.5, rounded up).
I haven't included any median info yet, though it'll be implicit in the range thing once I get it working right.
Edit: Okay, the range thing is close enough for government work. It's not doing exact calculations, I'm just taking advantage of the fact that the density in ranges from 0 to X/2, X/2 to 1, 1 to 3X/2, and 3X/2 to infinity doesn't actually change all that much as X changes (where X is mean turncount). This is never going to be off my more than a few percent for attainable values of ++combat.
Last edited by Bazzle on Wed Oct 10, 2007 3:26 pm, edited 1 time in total.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
Here's an accurate way to get the "shape" of the curve:
% of at least 5 lobmen occuring = 100% - (% chance that 0 to 4 lob men occur)
You can then equate this % chance to 5%, 50%, 90%, and 100% or any number you feel like. Remember that the % chance of exactly 0 or exactly 4 lob men occuring is given by the pascal distribution.
% of at least 5 lobmen occuring = 100% - (% chance that 0 to 4 lob men occur)
You can then equate this % chance to 5%, 50%, 90%, and 100% or any number you feel like. Remember that the % chance of exactly 0 or exactly 4 lob men occuring is given by the pascal distribution.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
.... Well, I guess I'll post my method to help you double check.Hopefully useful over-complication of the problem.
(drop rate = odds of a lob man occurring at the beach)
You can edit the drop rate, as well as the % chance to completion. The only output on the first page is the actual number of turns for each percentage.
(drop rate = odds of a lob man occurring at the beach)
You can edit the drop rate, as well as the % chance to completion. The only output on the first page is the actual number of turns for each percentage.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
I don't think my last sheet was clear, as it was made for your eyes only and not for the standard user. I've updated your sheet with my thing as well, and gotten rid of the standard deviation. That number doesn't mean anything to most people, while the couple % that I just put in should be intuitive.
The % values are allowed to float freely. As you change them, it will display the number of turns expected to have that percent chance of completing the beach. I have them set to 33%, 50%, 66% and 90%, but you can make them anything you want. A value of 66% will calculate the amount of turns you need to spend at the beach to have a 66% chance of finishing it. Between this and the calculated mean, you have a simple way of letting people measure their risk. Again, the % values are user inputs!
Note: I round to a 0 decimal point percentage in making these calculations, so you have a finite turn length even with a 100% chance.
Linky.
Also also, I checked your nuns treatment. Nice work, it's accurate. Barring future spading and GUI improvements, this sheet is looking quite accurate.
EDIT: queue should have an effect on lowering the expected turncount in the junkyard. And I updated the fairy formula to use fractions instead of decimals.
The % values are allowed to float freely. As you change them, it will display the number of turns expected to have that percent chance of completing the beach. I have them set to 33%, 50%, 66% and 90%, but you can make them anything you want. A value of 66% will calculate the amount of turns you need to spend at the beach to have a 66% chance of finishing it. Between this and the calculated mean, you have a simple way of letting people measure their risk. Again, the % values are user inputs!
Note: I round to a 0 decimal point percentage in making these calculations, so you have a finite turn length even with a 100% chance.
Linky.
Also also, I checked your nuns treatment. Nice work, it's accurate. Barring future spading and GUI improvements, this sheet is looking quite accurate.
EDIT: queue should have an effect on lowering the expected turncount in the junkyard. And I updated the fairy formula to use fractions instead of decimals.
Last edited by QuantumNightmare on Thu Oct 11, 2007 2:21 pm, edited 1 time in total.
queue should have an effect on lowering the expected turncount in the junkyard....
Do you mean the odor extractors? Because I don't think that's true. You should generally be getting the needed tool from the first encounter with the right copy of each gremlin in each subzone. I would think that extracting the wrong type would only hurt you.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
I made a stupid mistake in my last version, where the beach noncombat rate wasn't calculated right at all. My link has been updated.
And I mean the queue. The average of 1/rate is only valid if the % chance of getting each adventure remains constant, but the queue will substantially reduce the odds of major screwage occuring. For each wasted turn, the odds of the right monster showing up increase quite a bit. Your odds start off 25%, but then increase to 30%, 40%, and finally 57% (if each monster is encountered once). So the expected turncount is really less than 4 per zone.
Also, there might be one single queue for the five junkyard areas. Note how using a harold's bell in one wine cellar area removes that monster from all 4... this might give a queue effect even when travelling between zones.
EDIT: According to flolle's mad programming skills, the junkyard should take 2.9 turns per area. That's assuming that each zone has it's own queue. If each zone uses the same queue... it gets complicated.
And I mean the queue. The average of 1/rate is only valid if the % chance of getting each adventure remains constant, but the queue will substantially reduce the odds of major screwage occuring. For each wasted turn, the odds of the right monster showing up increase quite a bit. Your odds start off 25%, but then increase to 30%, 40%, and finally 57% (if each monster is encountered once). So the expected turncount is really less than 4 per zone.
Also, there might be one single queue for the five junkyard areas. Note how using a harold's bell in one wine cellar area removes that monster from all 4... this might give a queue effect even when travelling between zones.
EDIT: According to flolle's mad programming skills, the junkyard should take 2.9 turns per area. That's assuming that each zone has it's own queue. If each zone uses the same queue... it gets complicated.
I've uploaded a new version with a cosmetic change to QN's beach distribution cells, and a new calculation for the turns spent in the goatlet (for the arena checker).
I have no strong opinion on the junkyard turncount. Right now it's at 12, and that seems about right to me. My tech notes are pretty clearly wrong in several respects regarding how I got that number (it should be 16 from what I say, and my guess as to how it works is mistaken, I think). But 12 feels very close to right. I'm willing to be convinced that it should be tweaked a bit, but I think it needs more spading as to what the breakdown of gremlins is in each zone.
I have no strong opinion on the junkyard turncount. Right now it's at 12, and that seems about right to me. My tech notes are pretty clearly wrong in several respects regarding how I got that number (it should be 16 from what I say, and my guess as to how it works is mistaken, I think). But 12 feels very close to right. I'm willing to be convinced that it should be tweaked a bit, but I think it needs more spading as to what the breakdown of gremlins is in each zone.
I'd suggest a seperate cell for what the ballroom song is turned to, because you can't exactly turn that off whenever you want. I don't know how you handle possible +combat and possible -combat though.
And it probably won't matter, but the Greed demon might deserve its own cell because its sort of like an inhaler.
And it probably won't matter, but the Greed demon might deserve its own cell because its sort of like an inhaler.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
Battlefield turns
Bazzle,
I was looking at your calculation cells, and they seem a little off.
You have preset AJB-F as taking 64 battlefield turns. It should take 8+59 = 67 turns. 8 @8/turn to open the orchard, and then 59 @16/turn to clear the field.
Similarly with AJB-FN - 8 to open the orchard, 8 more to open the nunnery, and then 26 @32/turn to clear the field, for a total of 42, not 40.
AJB-FND should be 34, not 32.
AJ-FN should be 83, not 80.
AJ-FND should be 66.
DNF-J should be 75.
DNF-BJ should be 42 (like AJB-FN).
DNF-JA should be 58.
DNF-BJA should be 34 (like AJB-FND).
Unless I'm completely mis-reading that section, which is wholly possible.
-David
I was looking at your calculation cells, and they seem a little off.
You have preset AJB-F as taking 64 battlefield turns. It should take 8+59 = 67 turns. 8 @8/turn to open the orchard, and then 59 @16/turn to clear the field.
Similarly with AJB-FN - 8 to open the orchard, 8 more to open the nunnery, and then 26 @32/turn to clear the field, for a total of 42, not 40.
AJB-FND should be 34, not 32.
AJ-FN should be 83, not 80.
AJ-FND should be 66.
DNF-J should be 75.
DNF-BJ should be 42 (like AJB-FN).
DNF-JA should be 58.
DNF-BJA should be 34 (like AJB-FND).
Unless I'm completely mis-reading that section, which is wholly possible.
-David
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
Hello, a friendly neighbor from the HCO forums here.
I was wondering, would you be interested in having this spreadsheet ported over to a web application ? I mean, I find this optimizer very useful and I'm really grateful for all the work you did on it, but I have a strong dislike for spreadsheets. The main reasons being that you have to open it with an external program (which eats all your RAM when you're on a mac and don't have MS Office, but that's another story), and that you have to download it several times if you use more than one computer. And I know I'm not the only one thinking that.
I happen to have a decent knowledge of PHP and fancy AJAX effects, and some free time during the upcoming month, so I could probably try to code an online version if you gave me the formulas that were used for the calculations. PHP doesn't have as many math functions as Excel, so some parts might be slightly tedious to translate - especially when computing distributions. But maybe a first version using mean values only would be ok ?
Let me know what you think about this anyway.
I was wondering, would you be interested in having this spreadsheet ported over to a web application ? I mean, I find this optimizer very useful and I'm really grateful for all the work you did on it, but I have a strong dislike for spreadsheets. The main reasons being that you have to open it with an external program (which eats all your RAM when you're on a mac and don't have MS Office, but that's another story), and that you have to download it several times if you use more than one computer. And I know I'm not the only one thinking that.
I happen to have a decent knowledge of PHP and fancy AJAX effects, and some free time during the upcoming month, so I could probably try to code an online version if you gave me the formulas that were used for the calculations. PHP doesn't have as many math functions as Excel, so some parts might be slightly tedious to translate - especially when computing distributions. But maybe a first version using mean values only would be ok ?
Let me know what you think about this anyway.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
That would be ideal.
The excel sheet has no protection applied, you can just open it up and check out the formulas. The only difficult computations are used for calculating the beach and filthworm subquests, which I coded and can explain if needed. Bazzle did everything else besides from some minor coding refinement on my part, so either of us can help you work your way through the coding.
Caveat: Bazzle's word is final, not mine. Check what he says. And although you can work on an online version, check in with us before anything is made public in order to decide on hosting and credit distribution.
The excel sheet has no protection applied, you can just open it up and check out the formulas. The only difficult computations are used for calculating the beach and filthworm subquests, which I coded and can explain if needed. Bazzle did everything else besides from some minor coding refinement on my part, so either of us can help you work your way through the coding.
Caveat: Bazzle's word is final, not mine. Check what he says. And although you can work on an online version, check in with us before anything is made public in order to decide on hosting and credit distribution.
TeKRunneR wrote:Hello, a friendly neighbor from the HCO forums here.
I was wondering, would you be interested in having this spreadsheet ported over to a web application ? I mean, I find this optimizer very useful and I'm really grateful for all the work you did on it, but I have a strong dislike for spreadsheets. The main reasons being that you have to open it with an external program (which eats all your RAM when you're on a mac and don't have MS Office, but that's another story), and that you have to download it several times if you use more than one computer. And I know I'm not the only one thinking that.
I happen to have a decent knowledge of PHP and fancy AJAX effects, and some free time during the upcoming month, so I could probably try to code an online version if you gave me the formulas that were used for the calculations. PHP doesn't have as many math functions as Excel, so some parts might be slightly tedious to translate - especially when computing distributions. But maybe a first version using mean values only would be ok ?
Let me know what you think about this anyway.
I can bust out my statistics books to help you code any functions needed for statistical analysis, but I bet there are libraries available for your favorite language (javascript and php in this case) that provide the necessary functionality already.
QuantumNightmare wrote:The excel sheet has no protection applied, you can just open it up and check out the formulas.
I have to admit I'm not very familiar with Excel, but is it possible to find out the formulas without having to see which cell corresponds to which variable ? Because that would be quite tedious and I'd much prefer to have the formulas directly if you have them written down.
And although you can work on an online version, check in with us before anything is made public in order to decide on hosting and credit distribution.
Yes of course. I can start by developping it on a host I have without making the address public. Then once it's ready for release we can see wether it needs to be moved to a better host.
alacrity wrote:but I bet there are libraries available for your favorite language (javascript and php in this case) that provide the necessary functionality already.
You're right, a quick search revealed a PECL extension for PHP that provides statistical functions. Not all of them are documented though.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
I didn't write down any of the formulas I used. Excel makes it very easy to see what links to what, so I just coded up some formulas directly.TeKRunneR wrote:I have to admit I'm not very familiar with Excel, but is it possible to find out the formulas without having to see which cell corresponds to which variable ? Because that would be quite tedious and I'd much prefer to have the formulas directly if you have them written down.
It's final exam and final project season right now, so I won't be any help with writing up the formulas for several weeks. I'd really recommend just looking through how the cells are linked together. Not trying to discourage you here, just saying that the info you're looking for doesn't exist until one of us sits down to do it.
Sorry for the delay in responding to your request, I've been a little irritated with KoL this week.
By all means, feel free to use my sheet as the basis for a web application. It would probably be useful to a larger number of people in that format, especially since the interface can be made much more intuitive. I have no skills in that domain, so I can't really help you with it other than answering questions about what's in the spreadsheet.
I'll probably continue to maintain the spreadsheet as the official version. Though barring new spading discoveries I think it's mostly complete. There are only a couple of things left that I've been meaning to do: add an auto-include/exclude option for the +40% meat from Winklered contingent on the Arena being selected, and add in a calculation about turning off the ballroom song before doing the beach. Apart from that I think it's pretty comprehensive as is, so there's not a lot of danger of it needing major future revisions.
Also, re this:
It appears to me you're right, probably just a cut and paste error in those cells. It looks like that's the only arena checker cell that has that mistake though.
I'll fix this and try this weekend to get around to adding the other features and updating it, then you can feel free to run with it how you see fit.
By all means, feel free to use my sheet as the basis for a web application. It would probably be useful to a larger number of people in that format, especially since the interface can be made much more intuitive. I have no skills in that domain, so I can't really help you with it other than answering questions about what's in the spreadsheet.
I'll probably continue to maintain the spreadsheet as the official version. Though barring new spading discoveries I think it's mostly complete. There are only a couple of things left that I've been meaning to do: add an auto-include/exclude option for the +40% meat from Winklered contingent on the Arena being selected, and add in a calculation about turning off the ballroom song before doing the beach. Apart from that I think it's pretty comprehensive as is, so there's not a lot of danger of it needing major future revisions.
Also, re this:
In the calculation of the surplus turns for the hippy side in the arena checker, the formula uses the number of inhalers (C7) instead of the additional ML (C6).
It appears to me you're right, probably just a cut and paste error in those cells. It looks like that's the only arena checker cell that has that mistake though.
I'll fix this and try this weekend to get around to adding the other features and updating it, then you can feel free to run with it how you see fit.
Hey,
I've got a first version ready. It's almost complete, but not really tested. I was gonna say I needed a PHP5-enabled host to show it to you, but I just realized I could activate it on one I have.
Here's the link : http://gone.tc/optimizr
I'll probably rework the top part of the interface to make it look a bit nicer, and I may add an option to use pessimistic numbers like on the spreadsheet, but I don't think I'll bother with statistical distributions. Another thing I'm planning to add is a cookie that'll store your settings so that you'll find the numbers you had last entered in the fields even after you've closed the page.
As I said it's not fully tested, I think it gives coherent numbers but I haven't had the time to compare them with the spreadsheet so tell me if you notice differences.
Oh and last but not least, it works with Safari and Firefox, but I have no easy way to test it with Internet Explorer, especially version 6. There shouldn't be too many problems, but you never really know with that stupid browser...
PS : your forum can be a real bitch. Kept telling me I needed to post 5 times before I could modify my profile when I tried to add anything to this thread (even though I haven't changed anything), and now that I have posted 5 times (yes, oddly it let me post on another thread), the submit button was just showing me a preview and not actually posting. Had to hit it a good dozen of times before it went through =\
I've got a first version ready. It's almost complete, but not really tested. I was gonna say I needed a PHP5-enabled host to show it to you, but I just realized I could activate it on one I have.
Here's the link : http://gone.tc/optimizr
I'll probably rework the top part of the interface to make it look a bit nicer, and I may add an option to use pessimistic numbers like on the spreadsheet, but I don't think I'll bother with statistical distributions. Another thing I'm planning to add is a cookie that'll store your settings so that you'll find the numbers you had last entered in the fields even after you've closed the page.
As I said it's not fully tested, I think it gives coherent numbers but I haven't had the time to compare them with the spreadsheet so tell me if you notice differences.
Oh and last but not least, it works with Safari and Firefox, but I have no easy way to test it with Internet Explorer, especially version 6. There shouldn't be too many problems, but you never really know with that stupid browser...
PS : your forum can be a real bitch. Kept telling me I needed to post 5 times before I could modify my profile when I tried to add anything to this thread (even though I haven't changed anything), and now that I have posted 5 times (yes, oddly it let me post on another thread), the submit button was just showing me a preview and not actually posting. Had to hit it a good dozen of times before it went through =\
TeKRunneR wrote:So, does the lack of feedback means everything is perfect ?
Not necessarily. I learned that feedback is something you have to value, because you won't get it that often if you aren't practically pushing people into it.
Anyway, I looked over the page again, and I think there are two things that could need a little work:
1. The top part needs, as you said yourself, some work. At least align the checkboxes and fields vertically, that would help with keeping a general overview over all inputs and make them easier to change (especially the checkboxes).
2. I think it would be a good idea to somehow include the documentation from the spreadsheet in some way. Right now, you have to have knowledge of the contents of the docu page in the spreadsheet to use the online version.
That is all I have to say right now. I didn't check the numbers, because I think that might be better left to people who have an idea how those calculations work. But good work so far!
- KujjieKujjieKoo
- Spade Ninja
- Posts: 2240
- Joined: Mon Apr 30, 2007 8:50 pm
- Location: !Chicago
- Contact:
KujjieKujjieKoo wrote:Hey TeKRunneR- It looks like you have a typo. The "AJB-OD" route for the frat side is actually displaying the calculated values for the "AJ-OD" path.
Huh, I don't see any problem with that path. I'm getting the same results as with the spreadsheet. Care to re-explain what the issue is ? (with a screenshot maybe ?)
Thanks for the feedback Flolle. I'm gonna try to complete this by the end of the week so it can be made public (by that I mean posting the URL somewhere on the main forums). How should I write the credits ? I'm thinking about adding this at the bottom of the page :
"Formulas by Bazzle (#xxx) and QuantumNightmare (#xxx)
Web application by TeKRunneR (#xxx)"
with the xxx replaced by account number. How does that sound ?
- KujjieKujjieKoo
- Spade Ninja
- Posts: 2240
- Joined: Mon Apr 30, 2007 8:50 pm
- Location: !Chicago
- Contact:
Well, the 4th line in the spreadsheet presets is AJB-FD. The 4th line in my presets is AJB-OD (Arena/Junkyard/Beach - Orchard/Dooks). I get the same results on that line with both the spreadsheet and my app, so I still don't understand the problem. I've never intended to calculate values for an AJ-OD path.
- KujjieKujjieKoo
- Spade Ninja
- Posts: 2240
- Joined: Mon Apr 30, 2007 8:50 pm
- Location: !Chicago
- Contact:
Creepy indeed. Sometimes it's like numbers are trying to tell us something...
Anyway, I think I've got that thing done. I fixed a small bug in the calculations, improved the design slightly, added the documentation and credits, and the data you enter is now saved across sessions through a cookie.
Could you guys please check if everything is alright, so that I can make an announcement on the main forum ? The URL is still http://gone.tc/optimizr
Also the source is available here : http://gone.tc/optimizr/optimizr_src.zip
Anyway, I think I've got that thing done. I fixed a small bug in the calculations, improved the design slightly, added the documentation and credits, and the data you enter is now saved across sessions through a cookie.
Could you guys please check if everything is alright, so that I can make an announcement on the main forum ? The URL is still http://gone.tc/optimizr
Also the source is available here : http://gone.tc/optimizr/optimizr_src.zip
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
Very nice work, I like it.
Can you check your ML values? When I tell the spreadsheet that I have the HitS and pixels left to do without an odor extractor, the sheet says we have slightly more ML left to be done through sidequests than the web app does. The difference is small, but that's all I've seen.
Can you check your ML values? When I tell the spreadsheet that I have the HitS and pixels left to do without an odor extractor, the sheet says we have slightly more ML left to be done through sidequests than the web app does. The difference is small, but that's all I've seen.
Ok I looked at my numbers again, and found that additional ML was not taken into account for Ed. But that's not what caused the differences you've seen. I think that is due to an error from your part : you're computing ML from the junkyard for 11 fights, even though the subquest takes 12 turns.
Glad you like it anyway.
Glad you like it anyway.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal
Actually no it isn't ready yet, the design is awful under IE. As usual. Since I'm on a mac I don't have it right now. So either I get the time to (re)install parallels and windows, or I'll have to wait till I go back to my parents' house. Which should happen next weekend.
Words cannot tell how badly I want IE developers to experience a long and painful death.
Words cannot tell how badly I want IE developers to experience a long and painful death.
- QuantumNightmare
- AFH
- Posts: 1827
- Joined: Mon Jan 29, 2007 10:28 pm
- Location: Montreal