Couple of things I found in the 20090821.xlsm spreadsheet when I was matching it up to my character
Shieldwall of the Breaker has 40 strength, not 59
Mark of the relentless has 48 strength, not 49
Reticulated armor webbing for hands (engineers only) is 885 armor, not 800
Also, probably worth adding in the two new +170 stamina brewfest trinkets. The on use may not be worth all that much but having two trinkets for +340 stamina combined... That's kind of nice.
How does the Helm of Wrath (ilvl 245) compare to the other helms? I thought it would just be a novelty item, but it appears to be better than the T8.5 helm I have, and I'm wondering how it stacks up to the T9.5 helm, and/or the 75 badge helm...
How does the Helm of Wrath (ilvl 245) compare to the other helms? I thought it would just be a novelty item, but it appears to be better than the T8.5 helm I have, and I'm wondering how it stacks up to the T9.5 helm, and/or the 75 badge helm...
Here's a comparison: link. It has more avoidance than the other helms, and if you ignore the socket bonus when gemming (which you wouldn't on the others) it has more stam. Honorbound has expertise, which a lot of tanks will probably find they have in abundance when they don't really need it. Hellscream's has Block Value and more Strength, both of which I would trade in an instant for Stam and avoidance.
Use Honorbound for threat (if for some reason you need threat) and Hellscream's will gain value when you get the 4-set. Though I haven't seen maths on the 4-set, I would expect Wrath to still be superior.
The novelty resistance stats don't seem to negatively effect the item budget at all.
I'm interested in the uptime on the Quel proc, it's potentially a very nice weapon though I don't think it can beat the Ardent Guard unless it is really up for most of an encounter.
Thanks for the quick response! And you answered my other question without my asking -- I had wondered how much the resist cost in terms of item budget. Incidentally, we also pulled a [Signified Ring of Binding] , which none of us thought would be useful anywhere, but I'm now wishing one of our regular tanks had picked up after having compared the stats.
I have added the Onyxia gear (as well as the 3.2 gear) in Taliafears spreadsheet. It depends a bit on the boss profile selected, but with weighting set to 0.4 time-to-live, 0.4 burst survival, and 0.2 threat, I get Helm of Wrath to be just above the Honorbound helmet (without counting the resistance). It clearly beats Hellscream (but without set bonus added). It's, not surprisingly, clearly below Greathelm of Triumph (both 258 and 245 versions).
The onyxia ring is a very nice option. The 800 armor on that ring makes it a solid option. It's actually third in ranking with the weights set as above, after the heroic ToC 10 and 25 man rings. This is again without giving any value to the resistance on the ring.
I would also be very interested in some appropriate testing of the Burning/Gleaming Quel'serrar proc. Comments on wowhead indicate a very high proc chance without internal cd. I have modeled the proc with 50% uptime in the sheet. I then get it third (Burning that is) in ranking below Ogrim's Deflector and Ardent guard (using horde names). But the uptime on the buff can change this greatly. Also, just to make things clear, this is assuming you are above the def cap.
It's, not surprisingly, clearly below Greathelm of Triumph (both 258 and 245 versions).
The 258 version is better for sure, but I rate Wrath over the the 245 Hellscream. If you gem Hellscream's to make the socket bonus, you get 195 stam, if you socket Wrath with a 30 stam gem you get 212 stam. 2.7 avoidance on Wrath versus ~1.7 on gemmed Hellscream.
Is over 210 BV (bit more from the extra str) worth more than 17 stam and 1% avoidance? That seems unlikely, except maybe with the 4-set bonus included. Perhaps it's jus the weightings used, but I think Wrath edges Hellscream's here until you use Hellscream's for the bonus.
I'm not a big expert on modeling but this trinket has no proc effects, it's all passive on equip so shouldn't take that much modeling really.
+15 Fire resistance
+69 defense
+108 parry
+101 health / 5 seconds
The first two are pretty easy to put into the spreadsheet and just based on the defense and parry that makes it a pretty nice trinket. But wasn't sure best way to model the 101 hp5. At my gear level the calculated AEP conversion (WITHOUT the hp5) puts it as being close to as good as [Royal Seal of King Llane]. I also ignored the +15 fire resistance but it certainly has some non-zero value in fights w/ fire damage, possibly negligible but certainly non-zero.
I use it right now as part of my Avoidance set and I like it, but haven't determined how much value that 101 hp/5 actually is. In a 300 second fight it should work out to 6,060 healing over the course of the fight and 12,120 for a 10 minute encounter.
I'm interested in the uptime on the Quel proc, it's potentially a very nice weapon though I don't think it can beat the Ardent Guard unless it is really up for most of an encounter.
It's anecdotal and I haven't done any official testing, but I scanned my raid logs and found that the Quel proc has just over 50% uptime. It frequently self refreshes, so there is no internal cooldown.
If there's interest in getting more solid data on it I could do some testing. Given what I've seen, I'd say a 50% uptime is a decent estimate for modeling purposes until then.
Procodile gave me 30% uptime, based on a few nights raiding, which probably comes to about 40-50% on a straight up boss fight where you get to do your full rotation with no interruption. I was getting 40% on a test dummy using dps gear as well. I'd suggest it is likely to be a 40% uptime on most bosses, lower if you are not doing an uninterrupted cycle, and possibly as high as 50% if you use capped hit/expertise.
Again this is fairly anecdotal as I can't figure out the best way to test it (not enough rage to do a proper rotation on a test dummy, and most boss fights have periods where you aren't doing a proper rotation). I'd consider it a good weapon, but the uptime is not high enough to be reliable, you're just going to end up dead sometime when the proc is down when an Ardent Guard would have saved you.
I'm not a big expert on modeling but this trinket has no proc effects, it's all passive on equip so shouldn't take that much modeling really.
+15 Fire resistance
+69 defense
+108 parry
+101 health / 5 seconds
The first two are pretty easy to put into the spreadsheet and just based on the defense and parry that makes it a pretty nice trinket. But wasn't sure best way to model the 101 hp5. At my gear level the calculated AEP conversion (WITHOUT the hp5) puts it as being close to as good as [Royal Seal of King Llane]. I also ignored the +15 fire resistance but it certainly has some non-zero value in fights w/ fire damage, possibly negligible but certainly non-zero.
I use it right now as part of my Avoidance set and I like it, but haven't determined how much value that 101 hp/5 actually is. In a 300 second fight it should work out to 6,060 healing over the course of the fight and 12,120 for a 10 minute encounter.
In an avoidance set it's a great trinket of course. For a general purpose trinket it lacks stamina (if I model it in the spreadsheet with 0.4 time-to-live, 0.4 burst survival, 0.2 threat weighting, it comes out as quite bad, below the brewfest trinkets). But stamina is really king in the sheet with those kind of weightings, so that's no real surprise.
Modeling any type of hp effect (on use effects or hp/5sec) is tricky in the spreadsheet since there are no calculated AEP values for those stats. Guess a new AEP stat could be created for that but it would mean quite a lot of work so I haven't had the time to do so. What I do in the sheet is to use the calculated AEP-value of BV to estimate the value of hp/sec. I calculate the hp gained per second and then treat this as additional block value (but with all modifiers taken away and assuming a block chance of 100%). This is only for calculating an AEP value of the stat, there is no BV added when you equip the trinket. Also, it overvalues the stat a little bit due to that AEP with the weights as above also include some value from threat. Not a perfect way of modeling it I know, but it should give an approximate value. All in all this means that 101 hp5 is a quite ok stat, not extremely good, but not completely useless either. Talking about the value of that stat in particular, it's nowhere near the value of truly great trinket procs/use effects like the Black Heart or Eitrigg's Oath but on par with the use effects from Heart of Iron and Juggernaut's vitality.
I'm interested in the uptime on the Quel proc, it's potentially a very nice weapon though I don't think it can beat the Ardent Guard unless it is really up for most of an encounter.
I tested the 25man version a couple days back on target dummies in Og (3x10min tests). I used my standard prot spec, equiped my fury gear (for rage) and just spammed Devastate. The uptimes I got on the Sanctuary buff were 60% minimum. The mod I am using is emitpU. For comparison I was getting a mongoose uptime of 30-40% for the duration same tests. The raid uptime I've seen has been in the 40-50% range but admittedly I'm not always on a mob for the duration of combat so that could skew actual results.
I'll try test with Procodile for a longer period if I get time to verify the results.
About two weeks I asked Talia if I could take over the spreadsheet. I was told I could! So I finally managed to squeeze out enough spare time to put some work into it.
So far, I've got:
* Updated mechanics for 3.2.0 and 3.2.2
* Full loot tables for 3.2.0 and 3.2.2
* Some behind-the-scenes backend work to make maintenance easier
I'm hoping to improve it much further in the future, but this is a good first shot before I go breaking everything. Hosted on Rapidshare because I don't want to kill my server, future versions will be posted on my server if the load's low enough. Right now it's OpenOffice-only because I don't have Excel - go download OO if you don't have it, it's free, it's available on every platform, it doesn't bite.
Right now it's OpenOffice-only because I don't have Excel - go download OO if you don't have it, it's free, it's available on every platform, it doesn't bite.
You should be able to save it as .xls from OpenOffice; not sure how well it'll work, but it's worth a try... it'd help out those of us who want to access it from PC's where we don't have admin rights to install software. i.e. at work (during lunch break, honest!) or at a net cafe.
thanks for updating, I can see you put in a lot of work.
Quick note, the T9 tanking legs Wrynn's Legguards of Triumph have blue and red socket, not red and yellow (that's the dps legs)
edited to add:
Solid Dragon's Eyes are +51 stamina not +41. Probably need to go through all the dragon's eyes.
Other Values should be +34 (stats like strength, hit, haste) or +68 (for instance AP)
Also, it appears that Dragon's eyes are still being considered as prismatics. This is no longer the case. A blue Solid Dragon's eye in a red socket will NOT get the socket bonus.
- Added craftable Crusader Orb gear
- Highlighted equipped items
- Added delta-EP column
- Added flag columns for 5-man/solo, BoE, and Emblem gear
- Added prismatic gems
Now I find myself at a bit of a crux, needing some possible help with no less than three different items.
First, and easiest: I need some 3.1 and 3.2 boss damage output values. I don't really know how much DPS they throw out. Anyone know where I could get them? The spreadsheet is currently full of Naxx bosses. Woo.
Second, and most complicated:
The method the spreadsheet uses to calculate a "total character value" is, to say the least, iffy. In the Equip screen you do indeed get some useful numbers at the top, and at the bottom you get a "buffed AEP" number. Unfortunately the buffed AEP number is basically one step away from worthless. The calculation process for it is:
* Figure out how much of a bonus a single point of each stat will be
* Take the character as it stands, and count up how much of a bonus you'd get if you *doubled every point of gear on it*, and every point had that same amount of quality
* That's the AEP! Yay!
This breaks down hideously in any case where stats have nonlinear returns and WoW is just chockfull of nonlinear returns. For humor value, make a set of gear that is just barely below def cap, then raise it above def cap. Observe that the just-below-def-cap character has a much much higher total AEP.
So this is pretty crummy.
The solution I want to use is to total up the Four Important Numbers, with the provided weights, and then combine them in some way. I am not sure what an appropriate "some way" is, however, as it seems trivial to get into a situation where, no matter what you weight, one stat is just flat-out better than all the others, always. So if there's a standard weighting idea for this, let me know.
And finally, the Four Important Numbers. Right now, the spreadsheet uses DPS, Threat, TTL, and Burst, where Burst is how long you survive if the RNG hates you (crit chance tripled, dodge/parry chance removed.) I propose changing this to DPS, Threat, Mitigation, and Burst, where Mitigation is the amount of damage you'd have to take in order to *actually* take, on average, some fixed amount of damage. I'm suggesting this because "TTL" is essentially "burst * mitigation", and with the current spreadsheet layout there is absolutely no way to prioritize pure mitigation.
Suggestions welcome. If I don't get any I'll just go do stuff and see what happens
Originally Posted by Alithiel
You should be able to save it as .xls from OpenOffice; not sure how well it'll work, but it's worth a try... it'd help out those of us who want to access it from PC's where we don't have admin rights to install software. i.e. at work (during lunch break, honest!) or at a net cafe.
I'm a little hesitant to do so just because I'll have no bloody clue if it will work. However, I put it up here. Let me know if it's functional. If it is, I'll keep posting it. If it isn't . . . well, I won't
Originally Posted by Reeshet
thanks for updating, I can see you put in a lot of work.
Quick note, the T9 tanking legs Wrynn's Legguards of Triumph have blue and red socket, not red and yellow (that's the dps legs)
edited to add:
Solid Dragon's Eyes are +51 stamina not +41. Probably need to go through all the dragon's eyes.
Other Values should be +34 (stats like strength, hit, haste) or +68 (for instance AP)
I did, though it's going more smoothly now
Just saw this post after uploading this version, so fixes won't be in this version, but they'll be in next version. Thanks for letting me know. Should be obvious where to patch stuff on your own if you need the right numbers ASAP.
And finally, the Four Important Numbers. Right now, the spreadsheet uses DPS, Threat, TTL, and Burst, where Burst is how long you survive if the RNG hates you (crit chance tripled, dodge/parry chance removed.) I propose changing this to DPS, Threat, Mitigation, and Burst, where Mitigation is the amount of damage you'd have to take in order to *actually* take, on average, some fixed amount of damage. I'm suggesting this because "TTL" is essentially "burst * mitigation", and with the current spreadsheet layout there is absolutely no way to prioritize pure mitigation.
Suggestions welcome. If I don't get any I'll just go do stuff and see what happens
I agree with your definition of burst, but I think it would be especially useful to identify easily that point where you can survive multiple unblocked hits in a row. For instance, there should be a large "breakpoint" difference in a gear set that is:
Can only survive 2 hits in a row
27k armor - 61.8% mitigation of incoming physical damage to ~15k per hit (about 39.3k unmitigated)
44.5k health buffed
vs.
Can survive 3 hits in a row
29k armor - mitigates 63.5% of incoming physical damage to ~14.3k per hit (same 39.2k unmitigated hit)
43.5k health buffed
Obviously this is a bit skewed since there's almost always "additional" damage in the ToC and Ulduar encounters but the idea (to me) is, how long can you survive without heals if indeed things go bad.
One of the problems I've seen once I reached >50% on dodge and parry is I'll get a long string of parries/dodge/misses and the healers start to relax or one of the tank healers decides to help raid heal and then BAM, BAM, BAM dead and the raid wipes. Having the stamina poot to Be able to survive 3 hits in a row versus dying after two made a huge difference.
I'm a little hesitant to do so just because I'll have no bloody clue if it will work. However, I put it up here. Let me know if it's functional. If it is, I'll keep posting it. If it isn't . . . well, I won't
Thanks
I've had a brief look through; the only thing that appears not to be functioning correctly is the pull-downs on the "Equip" tab... which unfortunately is fairly fundamental as it's not possible to change the gear setup from the one that's already in it!
Not sure why those aren't working, though, as it only seems to be the gear and enchant pull-downs, both of which come from the "GearList" tab. The gems ones, which link from a different sheet, are working fine. The only thing I can think of is that it could be an issue with the conditionals you've used in your gear list to differentiate between Alliance and Horde items.
I've had a brief look through; the only thing that appears not to be functioning correctly is the pull-downs on the "Equip" tab... which unfortunately is fairly fundamental as it's not possible to change the gear setup from the one that's already in it!
Not sure why those aren't working, though, as it only seems to be the gear and enchant pull-downs, both of which come from the "GearList" tab. The gems ones, which link from a different sheet, are working fine. The only thing I can think of is that it could be an issue with the conditionals you've used in your gear list to differentiate between Alliance and Horde items.
Actually, I think I know what's going on there - one of the first things I did was look at all the hardcoded ranges, say "oh no way, I am not going to maintain that", and rig up a horrifying system so they maintained themselves as I added new gear items. It may be that Excel is not compatible with my gnarly code.
Unfortunately I have no idea how I'd go about fixing it in a manner that is not only compatible with both spreadsheets, but does not make me want to stab myself.
I could put a lot of time into trying to fix Excel but I'm gonna be honest here, every minute I spent trying to cajole Excel into cooperating is one less minute I spend on making OpenOffice awesome, and I kind of prefer my time to be spend on awesome. So . . . I guess, if there's a lot of people out there who absolutely rely on it running in Excel, let me know, and I'll try to figure out what my priorities are.
Also if you're fine with OO.o let me know about that also so I can get a sense of what percentage of the userbase I'm basically giving the finger to.
Originally Posted by Reeshet
For instance, there should be a large "breakpoint" difference in a gear set that is:
Can only survive 2 hits in a row
vs. Can survive 3 hits in a row
Obviously this is a bit skewed since there's almost always "additional" damage in the ToC and Ulduar encounters but the idea (to me) is, how long can you survive without heals if indeed things go bad.
One of the problems I've seen once I reached >50% on dodge and parry is I'll get a long string of parries/dodge/misses and the healers start to relax or one of the tank healers decides to help raid heal and then BAM, BAM, BAM dead and the raid wipes. Having the stamina poot to Be able to survive 3 hits in a row versus dying after two made a huge difference.
That's a pretty fair request. I think I would not implement this as a "goal", but rather as just a little informational tidbit - anything that granular is going to be near-impossible to "optimize for" in any sensible way, but I could certainly rig it so it gives a little display of whether you can soak 2.9 hits or 3.1 hits.
Nice work ZorbaTHut. I've got a 3.2.2 version of taliafears sheet that I'm maintaining for myself (haven't really felt that it was ready to be released yet though). I've done quite a few modifications to it, if I can find the time I'll take a look at your sheet to compare.
Originally Posted by ZorbaTHut
First, and easiest: I need some 3.1 and 3.2 boss damage output values. I don't really know how much DPS they throw out. Anyone know where I could get them? The spreadsheet is currently full of Naxx bosses. Woo.
Seconded, new boss profiles are desperately needed. I guess one could have a look at some posted logs to figure out things like dps and magic dps, but for weightings one would need input from people who are actually doing the fights (I'm not raiding endgame currently).
Second, and most complicated:
The method the spreadsheet uses to calculate a "total character value" is, to say the least, iffy. In the Equip screen you do indeed get some useful numbers at the top, and at the bottom you get a "buffed AEP" number. Unfortunately the buffed AEP number is basically one step away from worthless. The calculation process for it is:
* Figure out how much of a bonus a single point of each stat will be
* Take the character as it stands, and count up how much of a bonus you'd get if you *doubled every point of gear on it*, and every point had that same amount of quality
* That's the AEP! Yay!
Hmm, are you sure that's how it's done? That sounds very weird and not at all what I could gather. I think you have misread the formulae. If we, for simplicity, say that TTL is at 1.0 weight, the AEP is calculated in this way:
1. Figure out the increase in TTL from adding exactly 1 point of armor.
2. Figure out the increase in TTL from adding exactly 1 point of a given stat of interest.
3. Find the AEP value for the given stat by dividing the result from 2. by the one from 1.
If we have several weights, this is calculated separately and then combined to a joint number using the weights. The threat and dps figures actually use AP as normalizing factor instead of AC. Essentially this means that (if using equal weight for all of TTL/Burst/Threat/dps) 1 AP is worth the same as 1 AC. This might seem arbitrary (and in some ways it is), but it's really up to you to select weights that work.
Or were you actually talking about the total AEP values for your character? Those are completely uninteresting, they are not used for anything and can safely be removed. They are however not calculated as you write above either so I'm not sure what you're referring to.
I do agree that the AEP calculations break down at caps (in particular for def), but there is no easy way to solve it. Landsouls dps sheet has similar problems around caps. The only way to get around this would be to code macros that actually switch gear and compute gains/losses. Adding those kind of things is a very bad idea in my opinion, it makes sheets very unstable. The current sheet should be usable in Excel, adding such macros will make it completely impossible to use in Excel. You just have to understand what happens around caps with the sheet. For prot warriors the only really non-linear part is when you become crit-immune.
The solution I want to use is to total up the Four Important Numbers, with the provided weights, and then combine them in some way. I am not sure what an appropriate "some way" is, however, as it seems trivial to get into a situation where, no matter what you weight, one stat is just flat-out better than all the others, always. So if there's a standard weighting idea for this, let me know.
Not sure I get what is wrong with the current version? The importance of stats will change when you select different bosses as they should. You should be looking at the top of the 'GearList' page (at the Final AEP values provided there) rather than the 'Equip' page.
And finally, the Four Important Numbers. Right now, the spreadsheet uses DPS, Threat, TTL, and Burst, where Burst is how long you survive if the RNG hates you (crit chance tripled, dodge/parry chance removed.) I propose changing this to DPS, Threat, Mitigation, and Burst, where Mitigation is the amount of damage you'd have to take in order to *actually* take, on average, some fixed amount of damage. I'm suggesting this because "TTL" is essentially "burst * mitigation", and with the current spreadsheet layout there is absolutely no way to prioritize pure mitigation.
Well, I think TTL is a much better estimate on how well you will survive than a pure mitigation statistic. The weights that you can set by choosing different bosses (or modify on the fly by changing the one of the boss profiles) already let you weight these things differently. But not sure on this one, guess we'll see when you change it.
edit:
Actually, I think I know what's going on there - one of the first things I did was look at all the hardcoded ranges, say "oh no way, I am not going to maintain that", and rig up a horrifying system so they maintained themselves as I added new gear items. It may be that Excel is not compatible with my gnarly code.
Not that it's helping you now, but for future reference. You don't need to maintain the hardcoded ranges, by doing 'Insert row' on a row in the GearList page (it has to inside a particular slot list, not the first or last row), the new row will be added automatically to the hardcoded range. Maybe you're adding items in some smarter way than me though.
Hmm, are you sure that's how it's done? That sounds very weird and not at all what I could gather. I think you have misread the formulae. If we, for simplicity, say that TTL is at 1.0 weight, the AEP is calculated in this way:
1. Figure out the increase in TTL from adding exactly 1 point of armor.
2. Figure out the increase in TTL from adding exactly 1 point of a given stat of interest.
3. Find the AEP value for the given stat by dividing the result from 2. by the one from 1.
If we have several weights, this is calculated separately and then combined to a joint number using the weights. The threat and dps figures actually use AP as normalizing factor instead of AC. Essentially this means that (if using equal weight for all of TTL/Burst/Threat/dps) 1 AP is worth the same as 1 AC. This might seem arbitrary (and in some ways it is), but it's really up to you to select weights that work.
Or were you actually talking about the total AEP values for your character? Those are completely uninteresting, they are not used for anything and can safely be removed. They are however not calculated as you write above either so I'm not sure what you're referring to.
I am talking about the total AEP values, and my goal is to make them interesting. Right now the only thing the spreadsheet can tell you is whether one item is better than another (and it even gets that wrong sometimes), it doesn't have any numeric way to compare two different sets of gear and tell you which is better. I would like to fix that, both because it would be useful to be able to compare sets of gear more directly, and because it'll be more consistent for debugging
As for how it's calculated, lemme give a quick example. Pretend there are only two stats, AC and Def, and only one gear slot. The def cap is 100. Before the cap, each point of def is worth 5.0, after the cap each point of def is worth 0.5.
First, we put a Breastplate of Insufficient Defense in our slot, which has Def +90. The game calculates that def is worth 5.0, then looks at all of our gear. We have 90 defense, so 90+5.0 = 450, so our gear value is 450. Yay!
Now we swap it with a Breastplate of Sufficient Defense, which has Def +110. Def is now worth 0.5, so our gear is now worth 110*0.5 = 55.
So, calculate the value of each stat at our present time, then add up the values of all our stats as if they were being added to our current gear. If you're capped on hit, hit is worthless. If you're not capped on defense, your score skyrockets.
It's meaningless, and I'd rather make it meaningful. Luckily, this actually isn't all that hard to solve - this is just the first step at solving it.
Originally Posted by Gruntle
Well, I think TTL is a much better estimate on how well you will survive than a pure mitigation statistic. The weights that you can set by choosing different bosses (or modify on the fly by changing the one of the boss profiles) already let you weight these things differently. But not sure on this one, guess we'll see when you change it.
It is a very useful number, but it reduces the amount of control you have over the spreadsheet. Say I completely outgear Gundrak and am simply not worried about burst, but I want to make things easy on my competent, yet ridiculously undergeared, healer. He's not going to let me get bursted down, but he may have longevity or throughput problems. "TTL" still considers my raw hit point pool. "Mitigation" doesn't. "Mitigation" + "Burst" equals TTL, but the only way to get Mitigation out of TTL+Burst is to actually negatively weight Burst.
(Although now that I'm thinking about it it'll actually be easy to provide all five and just let people weight as they feel appropriate, so maybe I'll do that.)
Originally Posted by Gruntle
Not that it's helping you now, but for future reference. You don't need to maintain the hardcoded ranges, by doing 'Insert row' on a row in the GearList page (it has to inside a particular slot list, not the first or last row), the new row will be added automatically to the hardcoded range. Maybe you're adding items in some smarter way than me though.
I tried doing that once or twice and it melted down. Now that I'm thinking about it, I may have been adding the range in the wrong place, but that's a level of fiddliness that I would just rather not fight with. I don't entirely trust the spreadsheet's ability to autoadjust itself
It's meaningless, and I'd rather make it meaningful. Luckily, this actually isn't all that hard to solve - this is just the first step at solving it.
Ok, I see your point. But I think you're going to find that it's not easy at all to solve without using macros .
The current version of the sheet works fine when you're far away from caps (def, hit, expertise) even for comparing gear sets. But sure, if you can find an easy way of solving this it would be very good.
(Although now that I'm thinking about it it'll actually be easy to provide all five and just let people weight as they feel appropriate, so maybe I'll do that.)
Actually, I think I know what's going on there - one of the first things I did was look at all the hardcoded ranges, say "oh no way, I am not going to maintain that", and rig up a horrifying system so they maintained themselves as I added new gear items. It may be that Excel is not compatible with my gnarly code.
Unfortunately I have no idea how I'd go about fixing it in a manner that is not only compatible with both spreadsheets, but does not make me want to stab myself.
I could put a lot of time into trying to fix Excel but I'm gonna be honest here, every minute I spent trying to cajole Excel into cooperating is one less minute I spend on making OpenOffice awesome, and I kind of prefer my time to be spend on awesome. So . . . I guess, if there's a lot of people out there who absolutely rely on it running in Excel, let me know, and I'll try to figure out what my priorities are.
Also if you're fine with OO.o let me know about that also so I can get a sense of what percentage of the userbase I'm basically giving the finger to.
That explains things... knowing that I was able to re-map the ranges (took about 10 mins) so it all seems to work now.
I did find a few minor issues;
- No values for set bonuses on T9 gloves in the GearList (+6 sta in each case) causes major problems if you try to put in a gem which meets the socket requirement.
- You need more rows for sockets on shields; Boreal guard has 2 sockets, but theres only 1 row available.
- The ΔEP column on the GearList tab makes absolutely no sense, probably another function of your "gnarly code" not translating to Excel. I'm assuming this is supposed to represent the diference in AEP between each item and the one you have equipped in that slot. So why all the code? Surely it would be much simpler to deduct the AEP from the Equip sheet directly, since those cells are static, rather than having a long and complicated function?
In terms of running it on Excel, once I knew what you'd done with regards the coding of the data ranges it was a pretty simple exercise to get it working. If you can't justify doing an Excel release, then I'm happy taking the .xls output from OO and re-mapping it for my own use so long as you don't go overboard on the 'gnarly code'!
As for how it's calculated, lemme give a quick example. Pretend there are only two stats, AC and Def, and only one gear slot. The def cap is 100. Before the cap, each point of def is worth 5.0, after the cap each point of def is worth 0.5.
First, we put a Breastplate of Insufficient Defense in our slot, which has Def +90. The game calculates that def is worth 5.0, then looks at all of our gear. We have 90 defense, so 90+5.0 = 450, so our gear value is 450. Yay!
Now we swap it with a Breastplate of Sufficient Defense, which has Def +110. Def is now worth 0.5, so our gear is now worth 110*0.5 = 55.
I understand that this is just a simple ad hoc example you made up, but I'm still having difficulties in understanding the problem. Let me explain:
It goes without saying that estimating the worth of some given stats (AEP values) by just evaluating your current gear set can go horribly wrong (see your chest example above). But wouldn't the trivial solution be, to evaluate all your gear *except* the slot in question? This way you could assess the value of any given chest. Same goes for every other slot in question. In your above example the second chest would be worth: 90 (your def value) - 90 (the chest slot is being exempt from this value) + 110 (the def value on the "new" chest) = 110 def of which 100 are woth 5.0 and 10 are worth 0.5 (caps come into play at this stage). In total the chest would have 505 value, a clear upgrade to 450 of the old chest.
I'm sure you know that but I'm elaborating to stress a single point:
This naive method (see above) breaks when you try to optimize all your gear at once (say by implementing an "optimize my gear" button). But this is not something that can be solved non iteratively. Do *not* try to estimate AEP values which would be applyable to all of your gear. AEP values change on the fly when you gear up. And no matter what you do this restriction will not fall.
Exactly, for one slot the method described by suicuique is perfectly viable. For one slot it becomes trivial to use different AEP below and above the cap. For an entire gear set it's just not possible, you would be better suited using Rawr Protwar for that sort of thing.
There is a problem with the sheet around caps, but I fail to see how that is a big problem? You need to be above the def cap. The sheet might actually tell you that stamina is better than def even below the def cap, this is because the sheet uses average quantities (even the burst weighting uses average stuff). Having a 0.1% chance of being critted will wipe raids even though it may not have a huge impact on your TTL or even burst survival.
I also just realised that your example is flawed ZorbaTHut. Disregarding the basic (and, as suicuique points out, oversimplifying) assumption of one slot, the sheet is still not meant to be used in the way you write. Lets say that you have the Breastplate of Insufficient Defense with +90 def. The Breastplate of Sufficient Defense drops and you want to figure out if it is an upgrade for you. The sheet calculates an AEP value for this new item (in the GearList page) using the AEP factors determined when having the +90 def chest equipped. The sheet will correctly place the +110 def chest above the +90 one. So there is no problem here. After you equip the +110 chest rankings will be the same, just a completely different scale of AEP.
If you really want to have some kind of measure on total gear quality you should instead just use the 4 output quantities from the stats page and do a weighted sum of them (but the weights will be different since you're now adding absolute numbers rather than ratios). Getting that weighting right is not trivial.
09/22/09, 1:35 PM
Similar Threads
