Originally posted by Owlie.
_________________
Here I present a new version of @Fabian's original Magic Finding Efficiency Spreadsheet. It is a tool for estimating the effort (in terms of hours) needed to find some of the most sought after items in the game. It was made with efficiency in mind, and can be used to compare the most efficient magic finding builds running the usual suspects: the Pit, Ancient Tunnels and Pindle/Snapchip.
You can find the spreadsheet here.
The spreadsheet can be conveniently used online by changing the input parameters (light grey fields). The rest is locked to prevent inadvertent errors creeping in. Keep in mind that other users may change your inputs. To save your values, and for general tinkering, simply download your copy of the spreadsheet. Included are two identical copies of the Efficiency sheet for easy comparison between setups, and a FabianEfficiency sheet for eliciting depression/motivation.
Before digging in, here's @Fabian's disclaimer: The numbers the spreadsheet spits out are only as accurate as the numbers you input, so you might want to take them with a grain of salt. If you estimate that you kill an average of 7 boss packs per run, but your "true average" is only 5,8 boss packs per run, obviously this inaccuracy will also be reflected in the resulting numbers.
To this I would add that the parameters with the biggest impact on efficiency–run times, how many bosses you kill per run, MF and /players settings–can all be estimated fairly accurately.
Parameters You Input
To calculate your MF efficiency, you interact only with the Efficiency sheets. These feature three templates: the Pit, Pindle (applicable to Snapchip) and Ancient Tunnels. Each of these has all or some of the following input parameters.
MF. Refers to MF% you have equipped when you kill a monster ("kill switch").
Find Item MF. MF% you have equipped if and when you use Find Item ("hork switch").
Find Item %. The percentage chance your Barbarian has to successfully "hork" a body. For other classes, simply set to 0.
Players. The /players setting you use when doing runs. Due to the item generation specifics this setting influences only the probabilities of Minion and Normal monsters. Hence, the effect of /players becomes more significant the more monsters of these types you kill.
Run Time (s). Your average run time, in seconds.
Bosses per Run. How many bosses per run you kill on average. You should count a Unique monster as one boss, but count a whole Champion pack (2-4 Champion monsters) as one boss as well. You can find details on how the spreadsheet uses this number in the next spoiler.
Minions per Boss. This is how many Minions you estimate you kill per boss. The spreadsheet automatically takes into account that Minions only spawn with Unique bosses. More details in the next spoiler.
Normals per Boss. How many Normal monsters you estimate you kill per boss. It is another way of estimating how many of these monsters you kill per run. In other words, this number is multiplied by the Bosses Per Run number to obtain the total number of Normal monsters killed per run. More details in the spoiler below.
Runs #. A number of runs used to calculate the percentage chance of finding the item after that many runs.
Hours #. A number of hours used to calculate the percentage chance of finding the item after that many hours of running.
Interpreting the Output
Runs for Drop. This number tells you how many runs you have to do, on average, to find one of the listed unique items. Said differently, Fabian's Pit Barb at peak efficiency (FabianEfficiency sheet) has a probability of 1/7273 per run to find a Death's Web. You will find the details of how this number is calculated in the spoiler below.
Hours for Drop. Per @Fabian: This number tells you how many hours you have to run, on average, to find one item. If you're looking to compare Magic Finding efficiency, this is the most important number of all.
% After Runs #. Gives the percentage chance of finding the item after the number of runs you set in Runs #. For example, Fabian's Pit Barb has a 24% chance of finding at least one Death's Web per every 2000 runs.
% After Hours #. Gives the percentage chance of finding the item after the number of hours you set in Hours #. For example, Fabian's Pit Barb has a 58% chance of finding at least one Death's Web after a 100 hours of running.
This concludes the "things you need to know to use the spreadsheet" part of the post.
* * *
Below you will find more information on item generation and the possible modifications of the spreadsheet.
Item Generation and This Spreadsheet
There are two distinct probabilities involved in unique item generation:
Base item drop probability. The probability that the base item (e.g. Sacred Armor) will drop. Influenced by /players. This is calculated on the TCEx sheets.
Unique drop probability. The probability that the base item will roll unique (e.g. Templar's Might, because of course it's going to be Templar's). Influenced by MF. This is calculated on the ItemRatio sheets.
These are then multiplied in the Efficiency sheets to obtain the final probabilities per kill, which are in turn used to calculate the average number of runs needed for a drop.
To allow for MF and /players to be variables, one essentially needs to simulate a drop calculator. @Fabian's spreadsheet allowed for variable MF, but /players needed to be factored in in an ingenious but arguably inelegant way.
To allow for (all the details of) variable /players one needs to dig into base item drop probabilities. This is already an involved task for the easiest examples of Weapon87 and Armor87 TCs, and quite daunting for lower TCs. Items given in the spreadsheet are of this easy variety, except for Ormus' Robes, Nightwing's Veil and Torch of Iro, which are included as examples of the process of adding new items (making their outputs valid only for players 1). More on this below.
The resource I used for much of this is the Item Generation Tutorial by Warrior of Light. (An easier to read version is linked in the Resources section below.)
Item Generation Parameters
The spreadsheet should contain all of the game files needed for unique item generation, with relevant table entries singled out and emphasised. This makes it a convenient tool for looking up parameters relevant for the item generation process. Those interested can find a brief overview of these parameters (and where to find them) in the spoiler below.
Adding New Items or Areas
Looking at different items won't change the efficiency comparisons between different areas/builds. Still, one might want to know how much effort is needed (on average) to find a particular item. Unfortunately, introducing both MF and /players as variables makes the process of adding new items complicated. As I'm not sure anyone at all would be interested in doing this, I will add only a brief sketch of how this can be accomplished, in order of increasing difficulty.
Including other A85 areas can most simply be done by replacing the Pit or AT. As far as I can tell, one would need to replace the appropriate h2h/miss/cast spawn frequencies of Minions and Normal monsters (in row 48), but also add in other monster types (such as Wraiths in Chaos Sanctuary). However, the effects of these monsters on final numbers (for MFing) is usually relatively small, so decent estimates for these areas can be obtained by using the existing Pit/AT templates.
To do any of this, you will need to download a copy of the spreadsheet. Keep in mind that I have hidden the ItemRatio2 and TCEx2 sheets to reduce clutter, so you may have to unhide them.
Credits and Thanks
I maintain that the spreadsheet presented here is merely a more user-friendly and robust version of @Fabian's original spreadsheet. All of the main ideas and formulas are his. So, I would like to thank him for his original work and feedback. I would also like to thank @Albatross and @ffs for their feedback and our discussions, which led to notable improvements of the spreadsheet.
Resources
Magic Finding Efficiency Spreadsheet - @Fabian's original thread.
Item Generation Tutorial - excellent resource from which to learn about item generation.
Silospen's drop calculator - internet-based drop calculator from the creator of GoMule.
Tub's drop calculator - features a more detailed UI than Silospen's.
Hall of Records - wherein you can find the most efficient documented SPF runners and a great thread to participate in if you are looking to improve your efficiency.
Build Efficiency 101 - @ffs' excellent and thorough efficiency resource.
Thank you for reading! As always, all suggestions and corrections are very welcome.
Some Observations
AT vs. Pindle and adjusted efficiency. As you know, adjusted efficiency (adjeff), which is the ultimate measure of efficiency in the Hall of Records, does not account for all details of magic finding (e.g. Minions). While I think adjeff is the best available indicator of efficiency for the purposes of record-keeping, I thought it might be interesting to revisit some of the record entries. The conclusions vary depending on the difficult-to-estimate numbers (e.g. Minions per Boss), but taking reasonable and conservative estimates, here's a couple of examples:
1) Corrupted's AT Sorc needs less time to find Death's Web than Gripphon's AT Conc Barb. Hence, the Sorc might be considered more efficient, in contrast to what adjeff values suggest.
2) Corrupted's AT Sorc needs less time to find Death's Web than Fabian's Pindle Barb, again in contrast to adjeff values.
Hence, AT might be considered more efficient than Pindle.
While these are all fairly minor differences (and, again, dependent on input parameters), it does challenge the established efficiency order of Pit Barb > Pindle Barb > AT Sorc, which I find amusing.
Also interesting to note: point 1) is true for every item listed but Tyrael's Might, for which having FI becomes quite important since Normal monsters cannot drop it.
Death's Fathom discrepancies. While checking all the final drop probabilities (found on Efficiency sheets) against the drop calculators, I noticed discrepancies for Death's Fathom probabilities of: A85 champions, Pindle and Pindle's minions. Having already checked all of the base item probabilities, I knew the issue must be with the unique probabilities. I found this strange, since the results were correct for all other monsters (for the same item) and basically the only thing differing in these formulas are mlvls.
So, I investigated further. I found that I can reproduce the calculator values if mlvl - qlvl = 0, i.e. if I either lower mlvl=(86, 87) to 85 or raise qlvl=85 to (86, 87). As far as I can tell, there is absolutely no reason to do this. Again, all the other formulas check out without this.
So, I'm inclined to believe my spreadsheet here. Please, feel free to correct me on this!
Zod Rune. While not exactly part of magic finding, I thought it would be interesting to include the Zod rune. Certainly, the desire for rune drops might be another factor in choosing what area/build to run.
Please note that the two linked drop calculators give wildly different probabilities for a Zod drop. My numbers agree with Tub's (German) calculator. Otherwise, the process of calculating a Zod drop is the same as the one for base items.
_________________
Here I present a new version of @Fabian's original Magic Finding Efficiency Spreadsheet. It is a tool for estimating the effort (in terms of hours) needed to find some of the most sought after items in the game. It was made with efficiency in mind, and can be used to compare the most efficient magic finding builds running the usual suspects: the Pit, Ancient Tunnels and Pindle/Snapchip.
You can find the spreadsheet here.
The spreadsheet can be conveniently used online by changing the input parameters (light grey fields). The rest is locked to prevent inadvertent errors creeping in. Keep in mind that other users may change your inputs. To save your values, and for general tinkering, simply download your copy of the spreadsheet. Included are two identical copies of the Efficiency sheet for easy comparison between setups, and a FabianEfficiency sheet for eliciting depression/motivation.
Before digging in, here's @Fabian's disclaimer: The numbers the spreadsheet spits out are only as accurate as the numbers you input, so you might want to take them with a grain of salt. If you estimate that you kill an average of 7 boss packs per run, but your "true average" is only 5,8 boss packs per run, obviously this inaccuracy will also be reflected in the resulting numbers.
To this I would add that the parameters with the biggest impact on efficiency–run times, how many bosses you kill per run, MF and /players settings–can all be estimated fairly accurately.
Parameters You Input
To calculate your MF efficiency, you interact only with the Efficiency sheets. These feature three templates: the Pit, Pindle (applicable to Snapchip) and Ancient Tunnels. Each of these has all or some of the following input parameters.
MF. Refers to MF% you have equipped when you kill a monster ("kill switch").
Find Item MF. MF% you have equipped if and when you use Find Item ("hork switch").
Find Item %. The percentage chance your Barbarian has to successfully "hork" a body. For other classes, simply set to 0.
Players. The /players setting you use when doing runs. Due to the item generation specifics this setting influences only the probabilities of Minion and Normal monsters. Hence, the effect of /players becomes more significant the more monsters of these types you kill.
Run Time (s). Your average run time, in seconds.
Bosses per Run. How many bosses per run you kill on average. You should count a Unique monster as one boss, but count a whole Champion pack (2-4 Champion monsters) as one boss as well. You can find details on how the spreadsheet uses this number in the next spoiler.
Minions per Boss. This is how many Minions you estimate you kill per boss. The spreadsheet automatically takes into account that Minions only spawn with Unique bosses. More details in the next spoiler.
Normals per Boss. How many Normal monsters you estimate you kill per boss. It is another way of estimating how many of these monsters you kill per run. In other words, this number is multiplied by the Bosses Per Run number to obtain the total number of Normal monsters killed per run. More details in the spoiler below.
Bosses per Run number can be obtained by counting over a "large enough" sample of runs. The larger the sample the better, but I feel somewhere around 200 runs provides a good first estimate. Estimating the Minions/Normals numbers correctly can be difficult, but they are included as they allow for more detailed comparisons between various builds and setups (e.g. Blizzard vs. Berserk vs. p7 Necro). Compared to Minions, I realize it makes less sense to tie in Normal monsters with bosses, but I have found it to be a slightly easier way to obtain estimates per run.
From these estimates, known game mechanics are used to calculate the number of each monster type killed per run. This accounts for the differences between Uniques and Champions, as well as hand-to-hand/missile/cast minions. (The final numbers are, of course, only estimates.) These mechanics are listed below.
1) 80% of the time a boss pack will spawn Unique, 20% Champion.
2) The number of Champions in a pack is set to 3 (the average of the possible number of Champions per pack).
3) In the MonStats sheet one can find the spawn frequency of h2h/miss/cast monsters. For probabilities, these differences matter only for Minion and Normal monsters. The frequencies are listed below.
From these estimates, known game mechanics are used to calculate the number of each monster type killed per run. This accounts for the differences between Uniques and Champions, as well as hand-to-hand/missile/cast minions. (The final numbers are, of course, only estimates.) These mechanics are listed below.
1) 80% of the time a boss pack will spawn Unique, 20% Champion.
2) The number of Champions in a pack is set to 3 (the average of the possible number of Champions per pack).
3) In the MonStats sheet one can find the spawn frequency of h2h/miss/cast monsters. For probabilities, these differences matter only for Minion and Normal monsters. The frequencies are listed below.
Code:
Pit: 6/7 for H2H, 1/7 for MISS.
AT: 4/6 for H2H, 2/6 for CAST.Runs #. A number of runs used to calculate the percentage chance of finding the item after that many runs.
Hours #. A number of hours used to calculate the percentage chance of finding the item after that many hours of running.
Interpreting the Output
Runs for Drop. This number tells you how many runs you have to do, on average, to find one of the listed unique items. Said differently, Fabian's Pit Barb at peak efficiency (FabianEfficiency sheet) has a probability of 1/7273 per run to find a Death's Web. You will find the details of how this number is calculated in the spoiler below.
Essentially, to calculate this number the drop probabilities of various monster types are multiplied by their per run killed numbers and added together. Find Item is included in the following way: to obtain results closer to reality, Normal monsters are never horked, while Minion monsters are horked only half of the time.
In other words, if you kill a boss, two minions and four normal monsters per run, the spreadsheet adds the probabilities of killing and horking the boss, killing two minions and horking one minion.
In other words, if you kill a boss, two minions and four normal monsters per run, the spreadsheet adds the probabilities of killing and horking the boss, killing two minions and horking one minion.
Hours for Drop. Per @Fabian: This number tells you how many hours you have to run, on average, to find one item. If you're looking to compare Magic Finding efficiency, this is the most important number of all.
% After Runs #. Gives the percentage chance of finding the item after the number of runs you set in Runs #. For example, Fabian's Pit Barb has a 24% chance of finding at least one Death's Web per every 2000 runs.
% After Hours #. Gives the percentage chance of finding the item after the number of hours you set in Hours #. For example, Fabian's Pit Barb has a 58% chance of finding at least one Death's Web after a 100 hours of running.
This concludes the "things you need to know to use the spreadsheet" part of the post.
* * *
Below you will find more information on item generation and the possible modifications of the spreadsheet.
Item Generation and This Spreadsheet
There are two distinct probabilities involved in unique item generation:
Base item drop probability. The probability that the base item (e.g. Sacred Armor) will drop. Influenced by /players. This is calculated on the TCEx sheets.
Unique drop probability. The probability that the base item will roll unique (e.g. Templar's Might, because of course it's going to be Templar's). Influenced by MF. This is calculated on the ItemRatio sheets.
These are then multiplied in the Efficiency sheets to obtain the final probabilities per kill, which are in turn used to calculate the average number of runs needed for a drop.
To allow for MF and /players to be variables, one essentially needs to simulate a drop calculator. @Fabian's spreadsheet allowed for variable MF, but /players needed to be factored in in an ingenious but arguably inelegant way.
To allow for (all the details of) variable /players one needs to dig into base item drop probabilities. This is already an involved task for the easiest examples of Weapon87 and Armor87 TCs, and quite daunting for lower TCs. Items given in the spreadsheet are of this easy variety, except for Ormus' Robes, Nightwing's Veil and Torch of Iro, which are included as examples of the process of adding new items (making their outputs valid only for players 1). More on this below.
The resource I used for much of this is the Item Generation Tutorial by Warrior of Light. (An easier to read version is linked in the Resources section below.)
Item Generation Parameters
The spreadsheet should contain all of the game files needed for unique item generation, with relevant table entries singled out and emphasised. This makes it a convenient tool for looking up parameters relevant for the item generation process. Those interested can find a brief overview of these parameters (and where to find them) in the spoiler below.
1) Treasure Class (TC) determines the items a monster drops, and their probabilities. It influences both of the above steps. Monster's TC is found in the MonStats and Superuniques sheet, while the particulars of a TC are found in the TCEx sheet. Beware that A85 TCs listed in MonStats usually need to be upgraded. Consult the linked item generation tutorial for details.
2) In the ItemTypes sheet the values from the rarity column are used in generating the automatic TCs (weap87 and armo87 in the TCEx sheet).
3) The various parameters–Unique, UniqueDivisor and UniqueMin–at the top of the ItemRatio sheet influence unique probabilities.
4) Monster level (mlvl). Relevant for both probabilities: directly for uniques, through available TCs for base items. Generated from the Levels sheet by adding the following to the area level: +3 for Unique, Minion and Superunique, +2 for Champion and +0 for Normal monsters.
5) Quality level (qlvl) is (confusingly) used for two similar but distinct item properties. Borrowing from the tutorial:
5-1) A level of an item type. These levels are listed in the Weapons and Armor sheets.
5-2) A level of a unique item, which are listed in the UniqueItems sheet.
The first kind of qlvl is used in unique drop probabilities. The second kind is the reason Pindle (mlvl 86) cannot drop Tyrael's, Arachnid's and Azurewrath (qlvl2 87). Even if the base item is found in the TC of a monster (of mlvl), it won't be able to roll unique (of qlvl2) if mlvl < qlvl2.
6) Rarity from the UniqueItems sheet encodes the probability of a particular unique rolling if there are more than one uniques of a given base item. For example, a unique Sacred Armor has a 1:9 chance of rolling Tyrael's Might (rarity 1) and 8:9 chance of rolling Templar's Might (rarity 8).
2) In the ItemTypes sheet the values from the rarity column are used in generating the automatic TCs (weap87 and armo87 in the TCEx sheet).
3) The various parameters–Unique, UniqueDivisor and UniqueMin–at the top of the ItemRatio sheet influence unique probabilities.
4) Monster level (mlvl). Relevant for both probabilities: directly for uniques, through available TCs for base items. Generated from the Levels sheet by adding the following to the area level: +3 for Unique, Minion and Superunique, +2 for Champion and +0 for Normal monsters.
5) Quality level (qlvl) is (confusingly) used for two similar but distinct item properties. Borrowing from the tutorial:
5-1) A level of an item type. These levels are listed in the Weapons and Armor sheets.
5-2) A level of a unique item, which are listed in the UniqueItems sheet.
The first kind of qlvl is used in unique drop probabilities. The second kind is the reason Pindle (mlvl 86) cannot drop Tyrael's, Arachnid's and Azurewrath (qlvl2 87). Even if the base item is found in the TC of a monster (of mlvl), it won't be able to roll unique (of qlvl2) if mlvl < qlvl2.
6) Rarity from the UniqueItems sheet encodes the probability of a particular unique rolling if there are more than one uniques of a given base item. For example, a unique Sacred Armor has a 1:9 chance of rolling Tyrael's Might (rarity 1) and 8:9 chance of rolling Templar's Might (rarity 8).
Adding New Items or Areas
Looking at different items won't change the efficiency comparisons between different areas/builds. Still, one might want to know how much effort is needed (on average) to find a particular item. Unfortunately, introducing both MF and /players as variables makes the process of adding new items complicated. As I'm not sure anyone at all would be interested in doing this, I will add only a brief sketch of how this can be accomplished, in order of increasing difficulty.
1) Obtain the per kill chance for the unique item from drop calculators and replace the MF and FI values (rows 66 and 67 of Efficiency sheets) of Ormus' Robes. The FI value is obtained by multiplying the calculator probability (obtained for the particular value of FI MF) by FI%/100. This yields the correct final results, but only for the particular values of MF, FI MF and /players used in drop calculators.
2) Bypass the hardest part of calculating base item drop probabilities by looking these up in drop calculators. This would still allow MF to be variable, while /players would be fixed. It would involve calculating the chance for a unique in the ItemRatio sheet. This is how Ormus' Robes, Nightwing's Veil and Torch of Iro were introduced.
3) Calculate everything from first principles.
2) Bypass the hardest part of calculating base item drop probabilities by looking these up in drop calculators. This would still allow MF to be variable, while /players would be fixed. It would involve calculating the chance for a unique in the ItemRatio sheet. This is how Ormus' Robes, Nightwing's Veil and Torch of Iro were introduced.
3) Calculate everything from first principles.
Including other A85 areas can most simply be done by replacing the Pit or AT. As far as I can tell, one would need to replace the appropriate h2h/miss/cast spawn frequencies of Minions and Normal monsters (in row 48), but also add in other monster types (such as Wraiths in Chaos Sanctuary). However, the effects of these monsters on final numbers (for MFing) is usually relatively small, so decent estimates for these areas can be obtained by using the existing Pit/AT templates.
To do any of this, you will need to download a copy of the spreadsheet. Keep in mind that I have hidden the ItemRatio2 and TCEx2 sheets to reduce clutter, so you may have to unhide them.
Credits and Thanks
I maintain that the spreadsheet presented here is merely a more user-friendly and robust version of @Fabian's original spreadsheet. All of the main ideas and formulas are his. So, I would like to thank him for his original work and feedback. I would also like to thank @Albatross and @ffs for their feedback and our discussions, which led to notable improvements of the spreadsheet.
Resources
Magic Finding Efficiency Spreadsheet - @Fabian's original thread.
Item Generation Tutorial - excellent resource from which to learn about item generation.
Silospen's drop calculator - internet-based drop calculator from the creator of GoMule.
Tub's drop calculator - features a more detailed UI than Silospen's.
Hall of Records - wherein you can find the most efficient documented SPF runners and a great thread to participate in if you are looking to improve your efficiency.
Build Efficiency 101 - @ffs' excellent and thorough efficiency resource.
Thank you for reading! As always, all suggestions and corrections are very welcome.
Some Observations
AT vs. Pindle and adjusted efficiency. As you know, adjusted efficiency (adjeff), which is the ultimate measure of efficiency in the Hall of Records, does not account for all details of magic finding (e.g. Minions). While I think adjeff is the best available indicator of efficiency for the purposes of record-keeping, I thought it might be interesting to revisit some of the record entries. The conclusions vary depending on the difficult-to-estimate numbers (e.g. Minions per Boss), but taking reasonable and conservative estimates, here's a couple of examples:
1) Corrupted's AT Sorc needs less time to find Death's Web than Gripphon's AT Conc Barb. Hence, the Sorc might be considered more efficient, in contrast to what adjeff values suggest.
2) Corrupted's AT Sorc needs less time to find Death's Web than Fabian's Pindle Barb, again in contrast to adjeff values.
Hence, AT might be considered more efficient than Pindle.
While these are all fairly minor differences (and, again, dependent on input parameters), it does challenge the established efficiency order of Pit Barb > Pindle Barb > AT Sorc, which I find amusing.
Also interesting to note: point 1) is true for every item listed but Tyrael's Might, for which having FI becomes quite important since Normal monsters cannot drop it.
Death's Fathom discrepancies. While checking all the final drop probabilities (found on Efficiency sheets) against the drop calculators, I noticed discrepancies for Death's Fathom probabilities of: A85 champions, Pindle and Pindle's minions. Having already checked all of the base item probabilities, I knew the issue must be with the unique probabilities. I found this strange, since the results were correct for all other monsters (for the same item) and basically the only thing differing in these formulas are mlvls.
So, I investigated further. I found that I can reproduce the calculator values if mlvl - qlvl = 0, i.e. if I either lower mlvl=(86, 87) to 85 or raise qlvl=85 to (86, 87). As far as I can tell, there is absolutely no reason to do this. Again, all the other formulas check out without this.
So, I'm inclined to believe my spreadsheet here. Please, feel free to correct me on this!
Zod Rune. While not exactly part of magic finding, I thought it would be interesting to include the Zod rune. Certainly, the desire for rune drops might be another factor in choosing what area/build to run.
Please note that the two linked drop calculators give wildly different probabilities for a Zod drop. My numbers agree with Tub's (German) calculator. Otherwise, the process of calculating a Zod drop is the same as the one for base items.
Google