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:
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:
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:
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!)
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.
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.
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.
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:
- Mystic Ally (any type)
- Blazing Wrath
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.