Dec 2, 2008

My Upgrades Spreadsheet

And now a quick break for some Excel fun!


This is a spreadsheet I have put together to quickly compare two items and plot my upgrade path. It's a bit complicated, but could be used by anyone so I'm making it available here. A quick overview first:

There are three sheets to the whole workbook. The first is multi-colored (named: List) and looks up all the information from the second sheet (named: Lookup). The third sheet is not really used that much, but can be helpful to figure out which crafted items give you the most bang for the buck (named: Crafted).

Basically, you enter all the information on the Lookup sheet. You fill out the name of the item, where it drops, and all the stats. It takes some time, but isn't too tough once you get the hang of it. Then, on the List sheet, you can quickly toggle between items from the Lookup sheet and compare them and see where gains and losses come from. For example, right now I'm looking at The Argent Skullcap and Heroes' Frostfire Circlet. I can quickly see that the Circlet grants me 2 STA, 20 INT, 60 SPI, 72 CRIT, and 35 DMG at the cost of 61 HIT. Using numbers provided by MaxDPS.com, I gain 70.96 DPS by upgrading to the Tier 7 headpiece. I'm also alerted in Red that there is a set bonus to the Tier 7 piece which I need to look out for. I also know that it drops in Naxxramas 10-man from Kel'Thuzad.

So we'll take a quick look at the Lookup sheet first.



This is the top-left most area of the Lookup sheet. The first row is simply the column number and should not be touched. The second row contains some information further over and should not be touched. The third row is a descriptor row that tells you what you are entering into the spreadsheet. Below that, you can see all the head items I have input into the sheet and all the neck pieces I have put in.

Column 1 is the row number and should not be changed and column 2 is the Slot position and should not be changed. All items should be grouped by Slot position. Also notice row 4 and row 14. These are small spacer rows. They should be left alone. If you need fewer rows for Head Slot items, delete any of the rows from 6-12. You should always leave the first and last rows in a grouping alone. If you need to insert rows, copy any row from 6-12 and insert it between 6-12 and go from there. Following these rules will keep the spreadsheet running as it should.

From there, you just need to enter all the information. The item name should just be the item name, but as you can see, I added a T7 to the end of some items to keep me cognizant of their rarity. "Dropped In" should just be the instance where it is dropped. If it is a crafted item, just enter "Crafted" and if it is from badges, just enter "Badges" (see the examples of Hat of Wintry Doom and Lattice Choker of Light above. In the "Dropped By" column, you can put where it is dropped, for crafted items, I kept track of what skill level is needed for them, but for Badges, you must put the number of badges required, and only that.



Now on to the stats. Here you just enter the relevant stats for each item. So you can see I've entered Armor, Stamina, Intellect, and so on. Damage is obviously Spell Power now, but I've left it as Damage. The problem here is I don't have AGI or STR or Dodge or other stats built in that could be useful for some. I am pretty sure you can change the descriptor in row 3, but the abbreviations in row 2 cannot be changed.



In these columns we have some additional information. The Proc is some sort of "on effect" type proc. So for example, the Sundial of the Exiled has a Proc of 590 and a Proc Type of "Damage". Sockets 1-3 are just filled in using one letter abbreviations, Meta = M, Red = R, and so on. Socket Bonus Number is the number of stat points you receive as a bonus. And Socket Bonus Type is the type of stat you gain a bonus to. So you can see in the first item, I gain a bonus of 9 DMG (or damage/spell power) if I correctly socket a Meta and a Yellow gem.



Here in these last few columns, we have some random information. Set Bonus can be set to Yes or No. If it's set to Yes, then it will highlight the item on the Listing page. If "Own" is set to yes, it will also highlight it on the main page and help you to distinguish it from other upgrade items. The MaxDPS column represents the DPS each item grants as determined by MaxDPS.com. This is just for reference and to compare items that may seem close in stats. DPS Gain per Badge relies on column E (Dropped By) if Column D says "Badges". So you have to make sure to follow the instructions above for Badge purposes. Cost of Crafting is a rough guestimate that comes from the "Crafting" page. This relies on Column D correctly being identified as a "Crafted" item. The DPS Gain per 100g relies on the Cost of Crafting. So here we see I'd go from 120.78 DPS to 152.63 DPS for just 373g, which is 8.54 DPS per 100g spent.

Here's a full-size image of the Lookup Page: Clicky

So once you have input all the information on the Lookup sheet, it flows through to the "List" sheet. This colorful sheet allows you to quickly compare two items for upgrades and also plot out where your next upgrades will come from.



Here we see in Column A we have the Slot position, column B has the item, and columns C & D show where it is dropped. The orange colored line indicates that the item is currently in our possession. The white colored lines are upgrades and the green colored lines compare the items to the one we own. Each slot position allows for the comparison of two items to the one we own which should go in the top slot. If you just want to compare one item to another, you can throw any item you want in row 3 and 4 and compare the two. But line 4 will always compare against line 3 and line 6 will always compare against line 3.

To compare items, you just use the pull down boxes in each section:



These pull down boxes come from the Lookup page, which is why it's important to leave the first and last lines of each item slot table alone. You can insert lines or delete lines between the first and last items, but the first and last items should always remain there. You can change all the info in them, but the should never be deleted and you should not add additional lines outside of these rows.



Here we just list the different stats for the items and the enchants that are available to that item. Enchants come from the bottom of the lookup page. All it shows here is the different stats for each item.



Here we get socketed stats added to the total. Nothing to do here.



Here we get the totals. This adds up all of the "On Item" stats, all the "Enchant" stats and all the "Sockets" stats. The fist line shows the item we own, the second line shows the first item we're comparing against and the fourth line compares the second item to the item we own. In this example, going from The Argent Skullcap to the Hat of Wintry Doom, we gain 17 STA, 10 INT, 21 CRIT and 1 DMG and lose 17 HIT.



These are the final four columns. Delta Unbalanced shows the total number of stat points that change. So going from the Argent Skullcap to the Hat of Wintry Doom has us go from 278 to 310 skill points. The reason it says unbalanced is because it considers Spirit equal to Spell Damage, which we know is not the case. But using the MaxDPS numbers, we can see a gain of 31.85 DPS. You can also see that the fourth line is colored red because it has a set bonus associated with it. It's just a bright red color to remind you to factor in the set bonus before tossing it aside if it has weaker stats.

Here's an overview of the full listing: Clicky

The last page is the Crafted page. Here we can set prices for crafted items which will factor into the DPS gain per 100g statistic.



Note that Row 1 is hidden. In Row 1, we have the column number. So if you have to add columns, make sure you unhide Row 1, fill in the column number and hide it up again. Row 2 has the cost of the material. These costs are pretty far off as I found Ebonweave and Spellweave are going for almost double the prices I guessed. All of these prices are changeable - so just switch out different prices for the different materials you have in row 3. Then, just enter your item name (it has to match the name on the Lookup sheet or the DPS gain per 100g won't work. Then just enter the quantities for each item and at the end, a total cost will be calculated.

Here's the full page:Clicky

I know it's complicated, but it works for me and it will be pretty complicated to adjust to your own stats (e.g. STR or AGI). While I am willing to answer some questions about the spreadsheet (leave comments on the blog), I can't customize it for you or enter items for you.

Also realize that the spreadsheet could easily break if you do something you shouldn't. Just be careful when deleting or adding rows or columns. Also remember you should not touch the "List" sheet other than using the drop downs to pick different items. And though the "List" sheet might seem to break at times, remember, if you select an item and then delete it from the Lookup, it won't work. So just select another item.

Also, I have manual calculation on for this spreadsheet. You need to hit F9 to calc the spreadsheet. Or you can go into Tools > Options > Calculation and turn automatic calcs back on.

Here's the latest spreadsheet for you to play around with (EXCEL FILE): Clicky

1 comment:

Aurdon said...

Very nice. I have been meaning to write a post on making a "Gear wishlist" with spreadsheets and I'll be sure to link to this post if/when that happens.