Diablo® III

So, who would find this useful?

As some of you know, I've been working on a spreadsheet for the monk class. This post is just to show a few things that it does right now and to gauge interest in the spreadsheet—basically, to give you a look at it without going into too many gory details. (I'll save those for another post if people are interested.)

I'll start by saying that I've dabbled with theasiangamer.com, d3up.com, and another website or two, and they seem like great sites. It's possible that they already handle all of what I've already put together or what I have planned, in which case I guess I'll stop wasting my time on this and start using those sites more often :)

Anyway, the worksheet is divided into multiple tabs:
  • One for your character sheet, plus some extras
  • One for your weapons
  • A master sheet for armor, which you use to fiddle with your gear choices
  • Individual sheets for each armor slot (both rings are on the same sheet), which you use to record the stats for several types of gear and to pick the one you're currently wearing
  • An effective life worksheet (more accurately, a snapshot worksheet) that shows the appropriate EL stats, your sustain, and your DPS so you can quickly compare multiple combinations of gears and skills
  • A DPS worksheet (raw at the moment) that calculates your real DPS in basic cases right now and can, with a little bit of knowledge, be used to calculate your real DPS with several modifiers
  • Assorted worksheets with miscellaneous arrays of stats for various skills, runes, difficulty levels, etc.

I've got a folder set up on imgur.com, so I'll begin updating this post shortly (i.e., in between champ fights in the Vault). Here's a look at the Character worksheet, which is the first sheet in the workbook:

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

Most of the Character worksheet is designed to replicate the information you see in your profile and the Details section of your character sheet. Only a handful of fields are entered manually; the rest are pulled from other sections of the workbook (principally the armor and weapons worksheets and the EL/snapshot sheet). Here's what you enter manually:

1. Your level
2. Your paragon level (if you have any)
3. The fields in the Quest section (difficulty level, MP level, act, and NV stack)
4. The two fields in the Build section (your build and your active mantra)
5. The fields in the Follower section (dynamic drop-down fields, plus fields for GF and MF)
6. The six fields in the Active Skills section (chosen from drop-down menus)
7. The three fields in the Passives section (ditto)

The rest of the information is generated by your selections in this tab and the others; at no point do you enter complete character attributes like strength, block chance, etc. The profile you see in the spreadsheet is the same one I last logged out of the game with, so you can check my profile's non-Overawe DPS and Enchantress-buffed armor and several other goodies if you want to compare the two.

Now, as you probably figured, I'm completely OCD about things like this, and I want to make this spreadsheet as user-friendly as possible. So it is full of color-coding that highlights errors or changes you've made, like picking the same passive twice, or picking two runes for the same active. One thing I'm geeked about (cuz I'm a geek like that) is the dynamic drop-down menus for the followers and their skills. Like this:

http://imgur.com/yNKij

In this image, I selected the Enchantress as my follower, selected some skills for her, and then selected the Templar instead. Immediately, the spreadsheet highlighted my skill selections to show that there's something wrong. When I open the skill drop-downs, I can choose only Templar skills for each spot, not Enchantress skills or Scoundrel skills. Cuz I love conditional stuff :)

Anyway, I'll be back after the next fight to show you the Weapons tab.

———

This is the Weapons worksheet, currently loaded with my three best weapons and an assortment of others I was testing at the time:

http://imgur.com/8b1x9

Columns G through N provide space for you to enter up to 8 weapons. You then select the weapons you want to wield from the drop-downs in cells D3 and E3, after which the Character Sheet worksheet does some stuff, and the DPS worksheet does some stuff, and BAM! DPS.

You need to enter the relevant data from row 5 (minimum tooltip damage) to row 31 (spirit regenerated per second), after which the spreadsheet calculates the following factors for each weapon:

1. Base attack speed (e.g., 1.20 for an Echoing Fury)
2. Real attack speed (includes any +IAS% on the weapon itself)
3. Minimum and maximum base damage (for the "core weapon," if you will)
4. Real minimum and maximum tooltip damage
5. "Black" (i.e., non-elemental) damage

Basically, almost everything I wrote in "(Almost) Everything You Should Know about DPS" is on this sheet for each weapon. Off the top of my head, the sheet has two holes:

1. It doesn't include min, max, and +elemental damage from your gear (but maybe it should).
2. It doesn't include very rare weapon affixes like +MS (e.g., Three Hundredth Spear).

It's rather functional, though. In case you're curious, socket bonuses get added to the base stat in one of the "Weapon #" columns. For example, my Fist of Legend shows 138% damage, but a quick look at my profile shows that the weapon itself has a base 68% damage and that I'm using a 70% CHD emerald. Seemed much easier than adding separate lines for each possible socket.

I'll be back in a few with a look at the Armor worksheet.

———

This is a partial look at the Armor worksheet. (It's got a lot of rows. Blame Blizzard, not me!)

http://imgur.com/DEADf

This sheet serves two purposes:

1. It provides a view of everything you're wearing right now.
2. It allows you to mix and match your gear so you can compare sets.

By default, columns D through O show you what you're wearing. This information comes from the individual worksheets for each armor slot. In this example, every item but the gloves (column J) matches the corresponding selection on the appropriate item sheet, so it looks brown and tan, like the rest of the workbook.

Column J is shaded differently to show that I picked a pair of gloves other than the one I'm currently wearing. The Character Sheet tab works on the selections in this sheet, not the individual sheets. This means that I can get a snapshot of my character, make a few changes, take a new snapshot, compare the two (or three, or four), and then settle on my choice. (I'll say more about snapshots later.)

The top left corner of the worksheet includes two buttons:

1. Copy from Worksheets
2. Copy to Worksheets

These buttons run macros that do . . . well, pretty much what they say do. If you click the Copy from Worksheets button, the Armor worksheet will load the selections you made on the individual worksheets, and all the data will appear brown and tan because the selections on this sheet will match those on the individual sheets. If you click the Copy to Worksheets button, the workbook will copy your selections from the drop-down fields in row 3 to the appropriate fields on the item-specific worksheets. So you use the first button to load your original gear if you don't like the combinations you've made, and you use the second to commit your changes once you've got what you want. The color-coding helps show you what you've changed.

I'll be back in a little bit with a peek at one of the individual worksheets for each armor piece.

———

This is a look at a sample sheet for an individual piece of gear. In this case, we're looking at pants.

http://imgur.com/qClO8

The fields are identical to those found on the Armor tab, so there's not much to say about them. The sheet works very much like the Weapons worksheet: you enter a name for the item and the relevant data, and then you select your choice from the drop-down in cell C3. Your selection is essentially saved as the default, so it shows up in the Armor tab if you click the "Copy from Worksheets" button, and any selection on the Armor tab that doesn't match this selection will be shaded differently.

Note that space in row 3 is limited, so I'm using descriptive names rather than the actual name of each item. This helps me remember what they're like when I fiddle with the selections on the Armor tab. You could give yourself more room if you wanted, though. Also, I limited the number of items to 8 per tab because that's as far as you can go without introducing a scroll bar to the drop-down field in cell C3. It could certainly be expanded, however. I've got named ranges all over the workbook, so introducing new items is easy if you're careful about how you insert the new rows and columns.

And I'll be back in a bit with a look at the snapshot/EL worksheet.

———

This is a look at the Snapshot worksheet, which provides a capsule look at your character.

http://imgur.com/bM8d3

It probably tells you more than you need to know, but it shows the following:
  • Your life, dodge, damage reduction, and resistance figures (from the Character Sheet worksheet, which draws from the selections in the Armor worksheet)
  • Your effective life against melee attacks (includes dodge) and affixes (no dodge)
  • Your paper DPS
  • Various factors converted into life per second, some of which are automated (columns I through L), and some of which don't account for everything yet and need to be manually massaged after you copy your profile (e.g., Blazing Wrath's heal, with or without Beacon of Ytar)
  • Your effective LPS totals, both in and out of combat
  • Your real DPS, which for now assumes Cyclone and Thunderclap and draws from your gear selections and the average weapon damage you enter in the DPS worksheet

I'm not convinced that I'm going to show all this information in the final version, but I needed to settle on a few things, and these seemed to be good candidates.

Once you've selected the gear, skills, and armor you want to examine, you click the Copy Snapshot button in the top left corner of the worksheet, which will enter a new line for you in row for and copy the values from each cell into the new row. You can then change the labels in column A. After you generate a few snapshots, you'll have a good method of comparing which combinations might be better or worse.

In this example, I have generated three snapshots, which I've grouped under "Fist of Legend (CHD)" so that I know they all assume I socketed an emerald in my FoL. Each one has a different active/passive combination, but the other skills and gear are the same:

1. Faith in the Light, Beacon of Ytar, and Peaceful Repose (PR not shown)
2. Fire Ally, Pacifism, and Ascension (Ascension not shown)
3. Blazing Wrath, Pacifism, and Ascension (Ascension not shown)

Effective life isn't affected directly by any of these skills, so they stay the same. (I haven't yet begun accounting for Serenity's invulnerability period; I'm not certain I will, either.) The paper DPS is also the same. All three have different total LPS values because of the differences in healing from skills. They also have different effective total LPS figures, which are the ones you'll typically concentrate on. Finally, the real DPS values clearly favor Faith in the Light and Beacon of Ytar. I've used column R in this example as a scratchpad, and I've recorded the maximum value of Fire Ally if I spawn it every second.

I'm running on fumes at the moment, so I think I'm going to pack this up and go to bed. I'll post a picture of the DPS worksheet tomorrow.

———

Decided I'd rather just post this and get it out of the way :)

Here's a look at the DPS worksheet, which is the roughest of the bunch because it's one of the first things I started working on but one of the last things I've gotten around to updating.

http://imgur.com/WH1vZ

The original idea was to allow players to pick individual attacks for their three-attack cycles. Rows 4 through 15 are typically hidden; I'm showing them here just so you can get a peek at what's going on under the hood. (Don't I have a Mystic Ally thread to work on?)

If you look at column B, only five fields need to be entered manually:

1. The first attack in row 1 (selected from a drop-down)
2. The second attack in row 2 (ditto)
3. The third attack in row 3 (ditto)
4. The number of targets in row 16
5. The average weapon damage in row 19

The spreadsheet reads the attack modifiers and proc rates in rows 4 through 15 from an array on another worksheet, and it reads your DEX, attack speed, IAS, CHC, and CHD from your armor and weapon selections. It then spits out your real DPS, broken down by category:

1. Target DPS (the mob you keep beating on)
2. AOE DPS (the 35% damage on the first two TC strikes, plus the 145% AOE to all targets)
3. Your Sweeping Wind DPS (the vortex)
4. Your Cyclone DPS (the tornadoes)

Column D is intended to be a scratchpad column—something you can use to play around with the numbers a bit. In this example, cell D19 shows my AWD with Faith in the Light actived and my Fist of Legend as the active weapon. Essentially, it's a means for me to begin calculating my vortex/tornado damage, which is not yet automated. The remaining columns were intended to compare increases in AWD, DEX, and other factors, but the formulas for the changes (row 34, not shown) are not fully accurate—they're essentially approximations for very small changes that extrapolate reasonably well to larger changes in DEX, CHD, etc.

As I said, this worksheet is rather rough, and I intend to redesign it. Right now, I envision allowing the player to pick and choose various skills, all of which show how much DPS they contribute, and I'm hoping to provide an accurate number at the bottom. I may have to stick with a few staples—for example, Wave of Light is limited by the amount of available spirit, and the length of the attack animation isn't known, so its DPS contribution is hard to calculate. Many other factors aren't difficult to calculate on paper, however:
  • Overawe
  • Mystic Ally (any type)
  • Blazing Wrath
  • Foresight

Etc. And, of course, I'd like to get WKL bonus damage in there, too. Might take me a little while, though. I also plan to allow the player to select one of his selected weapons as the active weapon for his Sweeping Wind snapshot (assuming he uses Sweeping Wind, of course). This will allow you to determine whether you want to check which weapon is active before each battle. (Thanks to the spreadsheet, I already know that my weapons are off by only half a percent, thanks to Faith in the Light.)

Anyway, this sheet needs to be redesigned, so it's going to be phase 2, really. The first thing I'm going to do, though, is change the AWD field so it simply reads the build type, figures out the average damage, and uses it. Right now, I have to enter it by hand.
Edited by Vrkhyz#1472 on 1/4/2013 4:34 AM PST
Reply Quote
Added a look at the Weapon worksheet.

http://imgur.com/8b1x9
Edited by Vrkhyz#1472 on 1/4/2013 4:34 AM PST
Reply Quote
Added a look at the Armor worksheet, which presents a master view of the gear you're wearing and allows you to make changes on the fly.

http://imgur.com/DEADf
Reply Quote
Added a look at one of the individual worksheets—in this case, the Pants worksheet. Because everyone should have a good selection of pants to choose from.

http://imgur.com/qClO8
Reply Quote
Can't wait to check this out tomorrow!
Reply Quote
01/02/2013 07:14 PMPosted by Vrkhyz
I'll start by saying that I've dabbled with theasiangamer.com, d3up.com, and another website or two, and they seem like great sites.


yeah, I use D3up lately for theory-crafting purposes. The spreadsheet looks nice but d3up is just way more flashy with tooltips and customize able item slots. I'm just 1 monk of many but I'd rather have the pretty custom version rather than the spreadsheet version.
Reply Quote
Posts: 4,043
View profile
tag
Reply Quote
Added a view of the Snapshot worksheet, which allows you to compare multiple sets of weapons, armor selections, and skill selections. Heck, even followers and follower skill selections.

http://imgur.com/bM8d3
Reply Quote
BTW, the Snapshot worksheet shows a lower DPS than the Character Sheet worksheet (and one that doesn't match my profile DPS) because I selected the Templar as my follower, which turned off all my Enchantress selections. Hence, no Focused Mind.
Reply Quote
01/02/2013 07:14 PMPosted by Vrkhyz
basically, to give you a look at it without going into too many gory details.


In other words, this thread is a tease! Like looking up !@#$ on 56k and then half way thru downloading the pix you lose connection.

TEASE!
Edited by Lightdemon#1672 on 1/2/2013 8:56 PM PST
Reply Quote
I decided to sacrifice some sleep to bring you the rather messy (but potentially powerful) DPS tab:

http://imgur.com/WH1vZ

@Lightdemon: I protest, sir! If I wanted to get everything up for viewing in a single evening, NOT explaining every single feature was the only way to do it. Also, my definition of "without going into too many gory details" is probably a lot broader than most people's ;-)
Reply Quote

@Lightdemon: I protest, sir! If I wanted to get everything up for viewing in a single evening, NOT explaining every single feature was the only way to do it. Also, my definition of "without going into too many gory details" is probably a lot broader than most people's ;-)


I kid of course. It's just that every time I see this spreadsheet surface in the forums, I get excited (because your work is usually flawless), but then I am told "No, not today, maybe next time". If your spreadsheet was my girl friend, i'd have cheated on her with multiple partners.
Reply Quote
Posts: 7,260
View profile
This looks amazing Vrk, don't have the time to read the whole OP atm but I definitely will tomorrow and leave a more detailed response. Other than that, I can't wait to have access to what looks to be the definitive monk spreadsheet, hurry up! :D
Reply Quote
I think I've got a formula that will calculate AWD from black damage, elemental damage, etc. Might need to clean up the code a little bit. Will probably make it easier by separating into MH and OH.

More later.
Reply Quote
I might just be sleepy right now and am missing something... But, your Character worksheet and Weapons worksheet have the exact same links?

And I would love to get my hands on this spreadsheet. So thank you and finish it when you can please, Vrkhyz!
Reply Quote
I might just be sleepy right now and am missing something... But, your Character worksheet and Weapons worksheet have the exact same links?

And I would love to get my hands on this spreadsheet. So thank you and finish it when you can please, Vrkhyz!

Damn, you're right! Here's the correct link, which I've posted into the relevant posts above:

http://imgur.com/8b1x9

Thanks! Also, now that you're not some random guy nobody knows who could be a bot, I accepted your friend request :)
Reply Quote
[quote]

Thanks! Also, now that you're not some random guy nobody knows who could be a bot, I accepted your friend request :)


LOL!!!

This spreadsheet works amazing. And if you do that black damage stuff, I am set, because while I -could- sit here and do all that math, my kids generally find that a good time to bug me (they are 10 months and 4, so not like I can tell them to go away :P) and I screw stuff up.
Reply Quote
To rip a line from Ozymandias, I coded the black/elemental damage 45 minutes ago.

(Okay, maybe it was 15 hours ago. The point is, it's working now.)
Reply Quote
Very impressive stuff Vrkhyz. It's *almost* enough to get me to start number crunching in this game. I've always loved the Diablo games for the fact you don't have to, and can do all sorts of fun stuff just based on how it "feels," but damned if I don't enjoy a good spreadsheet too.

Throwing in Watchmen quotes never hurts, either.
Reply Quote
Posts: 3,729
View profile
sweeeeet!
Reply Quote

Please report any Code of Conduct violations, including:

Threats of violence. We take these seriously and will alert the proper authorities.

Posts containing personal information about other players. This includes physical addresses, e-mail addresses, phone numbers, and inappropriate photos and/or videos.

Harassing or discriminatory language. This will not be tolerated.

Forums Code of Conduct

Report Post # written by

Reason
Explain (256 characters max)
Submit Cancel

Reported!

[Close]