Latest Diablo 3 News
DiabloWiki Updates
Support the site! Become a Diablo: IncGamers PAL - Remove ads and more!

Magic Finding Efficiency Spreadsheet

Discussion in 'Single Player Forum' started by Fabian, Feb 26, 2011.

  1. Fabian

    Fabian IncGamers Member

    Joined:
    May 16, 2007
    Messages:
    4,029
    Likes Received:
    267
    Trophy Points:
    277
    Magic Finding Efficiency Spreadsheet

    Hi,

    I've written an excel tool for calculating and/or comparing your Magic Finding efficiency. By inputing your run time, your MF, player settings, hork% and how many monsters you kill, you can see exactly how many of a given unique (I've chosen Death's Web) you will find, on average, over time. It's written primarily with the Pit, AT and Pindle in mind, but I imagine the information could be used for some other things as well, especially with some tweaking.

    Click here to download the spreadsheet

    Before I go over it and explain everything, a 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.

    Below I will explain what all the stuff means, and how to interpret the numbers. If you have any questions about how it works, what it tells you, or anything like that, I would ask you to please read the below section(s). Chances are it's explained in there, otherwise I'd be happy to answer any questions of course.


    Area 85 Level Running

    Here's a screenshot of what this page looks like:

    [​IMG]

    The top section and the bottom section are identical; essentially you have two calculators on the same page, so you can compare two different setups at the same time.

    All the stuff on the right, ilvl/qlvl/BaseChanceUnique/Divisor/QualityFactor/DropChance, are numbers used to calculate the drop chance of the item we're looking at (Death's Web). You don't need to understand or worry about these numbers, unless you want to change the item you want to calculate the chance for. For more information, check out the excellent Item Generation Tutorial in the Statistics forum, particularly section 8.

    The other numbers on the right concern the player setting you want to use. More on that later.

    The numbers in cells C4-C7 are the drop chances of the Unique item from the corresponding type of monster, given the MF amount you've input. In cell C4, the number 110870 means there's a 1/110870 chance of a Boss dropping a Death's Web, given this amount of MF.


    Values you input

    MF (hork), MF (kill) (B9-B10): Here you put down how much MF you're wearing. If you don't use Find Item, you can leave the "MF (hork)" field blank, or leave it at some other number; it won't be used in the calculations. The reason there are two different MF fields is for Barbarians who have one "kill switch" (perhaps a high-damage weapon) and one "hork switch" (perhaps something with FCR, MF and/or +skills). If you hork with the same amount of MF as you kill with, just input the same value in both fields.

    Player setting (B11): This is not very elegant looking, but it works fine. Look at the numbers in cells U15-X15; these are the values to input here in B11, and the drop odds of Minion and Regular monsters will be adjusted correctly. Hint: If you put "=X15", the value from cell X15 will be copied, and you get p7 conditions without having to copy down that number by hand.

    Hork% (B12): This is for Barbarians only. Put the % chance of a successful Find Item. When using horking in these calculations, it is assumed that you hork all Bosses and Champions, and no Minions and Regulars. Non-Barbarians can input 0 here, or leave it blank.

    Run Time (s) (B13): How fast your average run is, in seconds.

    # (D4-D8): The numbers in these cells represent how many monsters of each type you estimate you kill per run, on average. These numbers might be difficult to get completely "right" (see the above disclaimer), but your best estimate will probably not be too far off. These numbers can't be 0, as it will return a "divides by 0" error. If you want to ignore one or more type of monster, putting something like 0,000001 will do the trick.


    Interpreting the numbers

    Chance/Run (C16): This number tells you how many runs you have to do, on average, to find one Death's Web (or whatever item you've changed it to). Put another way, there's a 1/8252 chance per run of finding a Death's Web, in the screenshot above.

    Hours/Drop (C17): This number tells you how many hours you have to run, on average, to find one Death's Web. If you're looking to compare Magic Finding efficiency, this is the most important number of all.

    % After # Runs (C20): Input a number of runs in the B19 cell. In C20, you can then see how many % chance you have of finding at least one Death's Web after that many runs. So given the conditions in the example screenshot, there's an 11.41% chance of finding at least one Death's Web per every 1000 runs you do.

    % After # Hours (C22): This is the same thing as above, but with hours instead of runs. Input a number of hours in the B21 cell, and this number will tell you how many % chance you have of finding at least one Death's Web after running for that many hours. For instance, with the conditions in the example screenshot, you'd have a 44.11% chance of finding at least one Death's Web if you ran for 100 hours.


    In the top section of the example screenshot, I've put the numbers of my Pit running Berserk Barbarian. In the bottom section of the example screenshot, I've put the numbers of my Pit running Poison Necro, doing p7 full'ish clears, although the Necro numbers are very rough estimates, and I'm not vouching for their accuracy :)


    Pindle

    This page looks nearly the same as the A85 page, and so does not warrant a screenshot. The information about Champion and Regular monsters is removed, as those monsters don't exist in Pindle running. Other than that, it works exactly the same, except one thing. In cell E6, you input how many of the minions you hork on average, if you're a Barbarian. As far as I know, some people try and hork only Pindle, and others hork the minions as well. This number can't be 0, as it will return a "divides by 0" error, but if you don't use hork, this number won't affect any calculations.

    The numbers the screenshot comes with, is my Pindle running WW Barb from the 2010 Summer MFO in the top section, and my 2011 Winter MFO teammate jDer's Lightning Amazon's numbers in the bottom section (taken from here). I hope he won't mind :)


    That's it. I hope you find this tool useful for something. If you have any questions or comments, please let me know and I'll do my best to answer them. If you read this and the download link has stopped working, you can email me at fabbe _ sjoblom @ hotmail . com and I'll reply with the spreadsheet.

    Thanks for reading!
     
  2. angeldesignpro

    angeldesignpro IncGamers Member

    Joined:
    Jul 1, 2003
    Messages:
    327
    Likes Received:
    0
    Trophy Points:
    122
    Re: Magic Finding Efficiency Spreadsheet

    I haven't even read your post yet, but I know it has a spreadsheet and is by Fabian, so I wanted to be the first to say: I love you let's get married
     
  3. Fabian

    Fabian IncGamers Member

    Joined:
    May 16, 2007
    Messages:
    4,029
    Likes Received:
    267
    Trophy Points:
    277
    Re: Magic Finding Efficiency Spreadsheet

    ..And I hope you're not the last one to say that!
     
    Last edited: Feb 26, 2011
  4. angeldesignpro

    angeldesignpro IncGamers Member

    Joined:
    Jul 1, 2003
    Messages:
    327
    Likes Received:
    0
    Trophy Points:
    122
    Re: Magic Finding Efficiency Spreadsheet

    Hmm... after looking through it, it's good info, but it's a little less like a moment of having the clouds part than the rune guides. A lot more guesswork involved, and a lot more variables to keep track of (especially MF percentage). I'm toying with the idea of making a pit running char and this gives me a nice mathematical basis for planning so, so still thanks a lot!

    Did you have any major revelations after playing with it?
     
  5. Fabian

    Fabian IncGamers Member

    Joined:
    May 16, 2007
    Messages:
    4,029
    Likes Received:
    267
    Trophy Points:
    277
    Re: Magic Finding Efficiency Spreadsheet

    I would argue that if you're building a magic finding character, but don't know your MF, you're doing it wrong ;) In any case, you can see your MF% in GoMule under "Dump" on your character.

    It is true there's more guesswork in A85 running than (for instance) Trav running, but that's the nature of the beast. I think you might even say you can benefit more from a tool like this than the Trav spreadsheet, as the calculations are rather more complex, and any guess you might come up with in your head would probably be extremely inaccurate (as opposed to Trav running, where having a comparison tool is definitely convenient, but you could probably do the same in your head if you wanted to). At least, that's what I will tell myself after having spent many hours on this :)

    As for revelations, I guess it's mostly confirmed things I already sort of knew; that Barbarians are insanely efficient, and that running speed is a lot more important than MF. That last point, I needed some reminding of, as my usual philosophy is to refuse to lower my MF. Seeing hard numbers makes it a lot tougher to go with Chance Guards over Laying of Hands, in the case of my Pit runner (though I'm not convinced which is better).

    The greatest use I will have of this is to be able to make meaningful comparisons with my p5/p7 Necro and other characters. A character who does fullish clears on high player settings couldn't reasonably be compared to a focus-on-boss-packs-on-p1 type character before this, imo. It will be interesting to see how good I can make that Necro.

    The above, about it being complex and stuff, mostly goes for A85 running of course. The Pindle part of it is pretty straightforward, and will certainly be useful I think.
     
  6. frozzzen

    frozzzen IncGamers Member

    Joined:
    Apr 2, 2009
    Messages:
    2,413
    Likes Received:
    122
    Trophy Points:
    174
    Re: Magic Finding Efficiency Spreadsheet

    According to your spreadsheet I should stick with Pindlebarb, hence your spreadsheet is obviously flawed :p

    Good work. I can see now how far behind Pindlebarb are most of my MF characters. This isn't something that I wanted to know to be honest :D

    It actually took me quite a while to figure out how it works. You should add some color/description in spreadsheet itself to make it prettier and more noob friendly :D
     
  7. Fabian

    Fabian IncGamers Member

    Joined:
    May 16, 2007
    Messages:
    4,029
    Likes Received:
    267
    Trophy Points:
    277
    Re: Magic Finding Efficiency Spreadsheet

    If only I knew how to do that :) I will leave that to someone else I suppose, I'm all about the functionality part, myself.
     
  8. angeldesignpro

    angeldesignpro IncGamers Member

    Joined:
    Jul 1, 2003
    Messages:
    327
    Likes Received:
    0
    Trophy Points:
    122
    Re: Magic Finding Efficiency Spreadsheet

    Big +1 on that. I did a few hundred pindle runs during one of my prior D2 phases, and it bored me to tears. I've even done a few pit runs with my barb and it's not nearly as bad... at least you're somewhat engaged and strategical.



     
    Last edited: Feb 27, 2011
  9. angeldesignpro

    angeldesignpro IncGamers Member

    Joined:
    Jul 1, 2003
    Messages:
    327
    Likes Received:
    0
    Trophy Points:
    122
    Re: Magic Finding Efficiency Spreadsheet

    OK, something real and mathematical to contribute:

    I played around with your calc more extensively Fabian, and was hitting a puzzle: With identical MF, hork, players settings and time/boss pack (and relevant minion kills dropped to 1) I was still getting a small - but quite noticeable - preference for a horking pit runner over a horking pindle runner.

    I scratched me chin. I scratched my head. I clicked on your equations and puked into a trash can from all the numbers and operators. I asked other people if I could scratch their chins to help my thought process.

    I'm pretty sure I've figured it out: Science(TM) says that statistically 3 killed, horked champions yield more sweet, sweet death's web goodness than 1/2 a pindle or 1 boss kill. Which I vaguely remember from MF guides of yore that stated (roughly) 1 pindle = 2 bosses = 2.5 champions = x bazillion (okok, like 30?) monsters for the purposes of MF'in.

    So that's interesting. I haven't rolled many pit maps, but if you could find one that heavily favored champions, and had a build that wasn't slowed down by picking them apart, you could get tiny marginal benefits! yaaaaaaaaaaaaaay.

    Also really enjoyed the calc for hork weapon calculating. Being able to see the difference between 1-2% increase in hork and 0-200 extra MF was extremely useful.

    While I'm at it, may I ask what the fastest decent-to-full MF load pindle run averages people have heard of are? I just found a very unorthodox way of dropping my run times (killing pindle + horking pindle) comfortably below 20 seconds (proof of principle run counter is 10 runs at 17.8 seconds) and I'm wondering if I'm actually breaking new ground in a 10 year-old game...
     
  10. Fabian

    Fabian IncGamers Member

    Joined:
    May 16, 2007
    Messages:
    4,029
    Likes Received:
    267
    Trophy Points:
    277
    Re: Magic Finding Efficiency Spreadsheet

    The fastest pindlebarb decked out in MF gear and a good hork that I am aware of is my own from the summer MFO. I don't really know the details of some of the other MFO particpants though, it's definitely possible there are better ones out there. I definitely think my old barb could be at least slightly improved. I also know that someone else is working on a different build and is claiming great results, I'm interested in reading that write-up when it arrives, and now also yours :)

    I'm not sure I understand the first part of your post correctly, but the thing that should be causing the small differences between pindle and Pit running is the difference in ilvl of Pindle (86) and Pit Bosses (**). The item quality calculation begins with a (400 - (ilvl-qlvl)) * 128 line, so in the case of Unearthed Wands (qlvl 86), a Pindle drop starts with 400*128 = 51200, and a Pit Boss drop starts with 398*128 = 50944, where less is better (for more info on this and the rest of that calculation, see section 8 in the Item Generation Tutorial). If you change Pindle and his Minions' ilvls to ** in the table on the right, I would expect the results to be identical (or change Pit Boss/Minions' ilvls to 86).

    Edit: About run times and such, it should be noted that 10 runs is not a sample size to draw any conclusions from, and I would suggest putting down at least 500, and preferrably at least 1000 before taking anything from it :) As proof, I present this video of my Barb during that Summer MFO, where he comfortably does 8 runs in ~126 seconds (average 15.75), which certainly wasn't anything out of the ordinary, far from it.
     
  11. angeldesignpro

    angeldesignpro IncGamers Member

    Joined:
    Jul 1, 2003
    Messages:
    327
    Likes Received:
    0
    Trophy Points:
    122
    Re: Magic Finding Efficiency Spreadsheet

    Fabian et al:

    I wrote a very long reply, the tl;dr is sur rune + azure wrath = epic win? Here's the long version:

    ---

    Thanks for the pointer re: where the math differs, that probably has a lot to do with it. Am I right that when champions spawn, there are always 3 of them? If so I believe each pack of champions has a better drop chance than a single boss, so that would also contribute to pit efficiency.

    Watching your video, I'm not sure my plan could offer real long term improvement. But I think it's at least possible. The problem is that it is expensive and extremely specialized, so I can only test it in "chunks" - equipping sub-bar items that approximate the effect, but sacrifice MF or whirlwind check rate or stats.

    Part of me wants to grind out the gear, reset the skills, test, write it up, and let it be a surprise / look how clever this build is guide - but (a) it might not work, (b) it would take a long time to test, and (c) it may have already been thought of or done. So, in that spirit, I present my working optimal-pindle-barb hypothesis:

    The end effect of the plan is a pindle run where, despite whirlwinding through the pack, pindleskin is reliably either the first, or one of the first monsters to drop. This means a quick kill, but also means a nearly fool proof hork (I sometimes get slowed down finding the corpse if I've killed all of the minions). In about 100 runs of trying various equip, I think it's a robust enough effect to capitalize on - but I don't have dual griefs or 07 ghoulhides, so it's entirely possible that just bumping damage would make the fancy footwork irrelevant (i.e. if you can kill all of the monsters at once, there's no need to selectively isolate and kill pindle...)

    Here's the idea in optimal form: Grief in one hand, azurewrath in the other, infinity on the merc, and (the kicker) - a sur rune in the azure wrath, combined with (ideally) at least clvl 95 for 100% chance to blind targets on hits from the azurewrath.

    What happens is that blind + KB from sanctuary pushes the minions away and overwrites their AI. This means that after the first post-teleport whirl, you should be on top of pindle with the other monsters being pushed away and blind (i.e. not using charge). At that point, short whirlwinds (ideally 4-8 frames) right on pindle (who is either the only or one of the only non-blinded monsters, so easily identifiable) bring him down VERY quickly - think instantly. I hypothesize that as other monsters are pushed away, the once distributed whirlwinds are now applying basically all of their hits to pindle. In practice, even with sloppy positioning he often pops first or in the first few.

    The infinity serves two purposes. First, decrep would get in the way of blind, and that's no good. Plus it doesn't help much if you already ignore the 50% physical resistance with an offhand sanctuary. Second, I haven't run the numbers (but plan to), but I would imagine infinity gives at least a slight hit chance increase vs. pindle, since the ITD on grief is only working on non-pindle minions.

    The reason for lvl 95 is for the blind, though as long as you're in the 90 area it won't make a huge difference. The defiled warriors are level 85, and Sur adds hit blinds target + 1, so chance = 50 + ((95 +0) - 85) * 5 = 100% chance to blind target.

    My 10 runs @ 17.8 seconds used coif of glory for blind, lawbringer for sanctuary, only 1 point in increased speed, and some other seriously unoptimal kit and put down the 17.8 time. The thing is I've never had runs go more smoothly - a single whirl through the pack and the situation is 'under control' - no slowdown due to PI pindle, no charging from the minions - very little inter-run variation. Obviously more runs would be needed to 'prove' consistency, but that's the idea.

    So… could be cool. And even if it's not cool or a little uncool or been there done that, it was a lot of fun to piece together and begin to test :D
     
  12. Fabian

    Fabian IncGamers Member

    Joined:
    May 16, 2007
    Messages:
    4,029
    Likes Received:
    267
    Trophy Points:
    277
    Re: Magic Finding Efficiency Spreadsheet

    You are in fact not right that Champion packs always spawn in 3's. A champion pack can have between 2 and 4 members (as far as I know anyway), and I don't know how that number is determined, I would guess it's just random but I don't know.

    The Hit Blinds Target idea is pretty fun, and certainly one I hadn't considered. It's something for someone (possibly me in the future) to play around with and test with more optimal equipment than your initial tests. If you do more experimenting with it, let me know how it goes and your thoughts, off-the-wall ideas for optimizing a popular running target is what I live for :)
     
  13. angeldesignpro

    angeldesignpro IncGamers Member

    Joined:
    Jul 1, 2003
    Messages:
    327
    Likes Received:
    0
    Trophy Points:
    122
    Re: Magic Finding Efficiency Spreadsheet

    Thanks for the champion correction. Numbers are definitely hard to pin down, sounds like the quality calc you described has a bigger effect. One day I'll read and internalize the item generation thread. One day!

    Isn't off the wall optimizing fun? I'm having a blast testing things. What program do you use to make your videos? That might help see better comparisons of runs.



     
  14. Fabian

    Fabian IncGamers Member

    Joined:
    May 16, 2007
    Messages:
    4,029
    Likes Received:
    267
    Trophy Points:
    277
    Re: Magic Finding Efficiency Spreadsheet

    I used Growler Guncam for the video above, which I think is a very nice program and worked very well. That said, when I tried recording one recently (for my Pit running WW Barb when I was writing about him), that program would crash for me. Seemed to be some memory issue or something, so I gave up on recording videos for now. Hopefully I'll have a better computer some day.
     
  15. Quickdeath

    Quickdeath IncGamers Member

    Joined:
    Sep 18, 2004
    Messages:
    1,497
    Likes Received:
    0
    Trophy Points:
    255
    Re: Magic Finding Efficiency Spreadsheet

    Fabian, I have a couple of questions.

    First, as you note, the drop % for a given item is dependent on (ilvl-qlvl). But i don't see any option on your spreadsheet for changing the qlvl of the sought after item. Are you actually doing that part of the calc?

    Secondly, would you review your assumptions for hork drops? I am genuinely confused about the hork drop %. My impression has been that Hork drops from all monsters, including bosses and super-uniques, get the drop percentages associated with a normal monster at mlvl=alvl, except that the no drop % is defined by the drop % of the hork skill.

    My general experience with horking has been that it is a great way to convert mana into crappy gear.
     
  16. Fabian

    Fabian IncGamers Member

    Joined:
    May 16, 2007
    Messages:
    4,029
    Likes Received:
    267
    Trophy Points:
    277
    Re: Magic Finding Efficiency Spreadsheet

    Quickdeath, the way the spreadsheet is set up is to show how often one specific unique will show up. I've chosen Death's Web, somewhat arbitrarily, and the numbers in the table on the right hand side is reflecting that (note the 86 qlvl in cell T3 for instance, which is the qlvl of Unearthed Wands). You can change those numbers (qlvl, BaseChanceUnique, Divisor, DropChance) if you want to look at another item than Death's Web and understand what you're doing, but of course the ratio between Death's Web/minute will be the same as Death's Fathom/minute, when comparing two different characters/setups. In any case, the qlvl is definitely taken into account, and it shows up in that data on the right hand side.

    With 55% hork, 45% of the time you will get nothing. 55% of the time, you will get a drop which is identical to a normal drop from that monster, except the nodrop chance will be 0. This doesn't make much practical difference in the case of Boss and Super Uniques of course, since they always have one Magic+ drop anyway, no matter what the player setting, and Champions work similarly (no difference in drop chance as you increase player setting).
     
  17. angeldesignpro

    angeldesignpro IncGamers Member

    Joined:
    Jul 1, 2003
    Messages:
    327
    Likes Received:
    0
    Trophy Points:
    122
    Re: Magic Finding Efficiency Spreadsheet

    I have a couple of questions Fabian:

    (1) I've noticed that often Pindle seems like a fountain of yellows. It's not uncommon for me to s&e with 4 yellows on the ground, from his drop and the hork. Yet running alvl 85 either my perception is way off base or I'm seeing far fewer rares. Does pindle have a quality boost of some kind?

    (2) Champions appear to have a chance to drop just potions and gold. Do normal bosses (I thought they always dropped 1 non-potion non-golditem) or pindle (who I believe always drops 2 non-gold non-potion items)? Is this accounted for on the spreadsheet?

    I searched the item generation tutorial and didn't find satisfactory answers for either (maybe because it's all my imagination?). Thanks again for the resource!

    I decided I wanted to hit 93 before doing more serious MFing, so now I'm decked out in scaredy-barb gear (stormshield was never sweeter than when it started taking dozens of baal runs per level and 2.5% or 10% of that level lost on death meant a tragic amount of wasted time). But one day I will return, and those two questions have been bugging me all day.
     
  18. Fabian

    Fabian IncGamers Member

    Joined:
    May 16, 2007
    Messages:
    4,029
    Likes Received:
    267
    Trophy Points:
    277
    Re: Magic Finding Efficiency Spreadsheet

    1. Your perception is right on target; Pindle does drop rares more frequently than regular bosses. Some monsters have what is called a QualityFactor (see for instance cell W3 in the spreadsheet), and a high QualityFactor basically makes it more likely for that monster to drop an item of that certain quality. Both Pindle and area bosses have a Unique QualityFactor of 800. In fact everything about Pindle and an area boss is identical except for two things; Pindle has two drops (not counting all the potions and such uninteresting stuff) instead of 1, and Pindle's ilvl is 86 instead of **. Try changing Pindle's ilvl (cell S3) to **, and notice that 1 Pindle drop will exactly equal 2 area boss drops (or put another way, the drop odds number in cell C4 will be exactly half of the same cell in the A85 spreadsheet).

    However, the Rare QualityFactor for A85 bosses is 800 (which can be found in the "Act 1 (H) Uitem A*" row in TreasureClassEx.txt if you want to see for yourself), while Pindle's is 972 (found in "Act 5 (H) Super A*"), so his items will spawn as rare more frequently.

    * Actually I'm not sure it's these exact rows, but something similarish anyway :)

    2. Bosses and Pindle always drop 1 and 2 non-potion item, respectively, like you say. Champions can indeed drop only uninteresting items, and it's accounted for.
     
  19. angeldesignpro

    angeldesignpro IncGamers Member

    Joined:
    Jul 1, 2003
    Messages:
    327
    Likes Received:
    0
    Trophy Points:
    122
    Re: Magic Finding Efficiency Spreadsheet

    Thank you very much for the detailed response! You are a gentleman and a scholar.
     
  20. JihadJesus

    JihadJesus IncGamers Member

    Joined:
    Apr 27, 2004
    Messages:
    1,222
    Likes Received:
    0
    Trophy Points:
    255
    Re: Magic Finding Efficiency Spreadsheet

    15 second Pindle runs? I don't think I could even load A5, S&E, and then successfully reload A5 in 15 seconds. That makes me think my load times are....atypical, and potentially a major drain on efficiency.
     

Share This Page