DPS Spreadsheet - Page 46

Dontmindme · 05/17/08, 11:17 AM

Originally Posted by Axiss_Lothar

Open Office 2.4 has completely broken the spreadsheet. There are Err: 529 in several key locations. Apparently there was a change in how OO handles text, numerics and sums. I've attempted to trace the problems back, thus far unsuccessfully. The problem seems to revolve around weapon enchants as far as I can tell, though there may be other issues.

Has anyone had luck resolving this Open Office problem?

I just opened the 2.4.1OO version in Open Office 2.4 and am not seeing any issues with the default gear and changing some things, especially weapon enchants. Are you using the most current version of the spreadsheet?

It's also possible that Open Office doesn't like particular pieces of code. If you can isolate the issue to a specific enchant or piece of gear, that might be helpful.

Axiss_Lothar · 05/17/08, 3:38 PM

Originally Posted by Dontmindme

I just opened the 2.4.1OO version in Open Office 2.4 and am not seeing any issues with the default gear and changing some things, especially weapon enchants. Are you using the most current version of the spreadsheet?

It's also possible that Open Office doesn't like particular pieces of code. If you can isolate the issue to a specific enchant or piece of gear, that might be helpful.

Every version from rogue.raidcal.com I've opened has the problem. As well as old versions I had used until they broke with the OO 2.4 update. I'm using the Open Office 2.4.0 version of Calc that's in the latest Ubuntu linux repositories. So, perhaps it's a problem specific to the linux source used in the build.

The problem is not specific to any one enchant or item, but a complete general failure. There are err:529s on the Buffed DPS sheet which then impact the Gear Buffs sheet. These errors on Buffed DPS are in I9, I13-15 and B8 which causes D8 to error. My tinkering started with I13 because it's the simplest formula. I can get rid of the I13 error by changing Wep_Enchants S16 to any number, but I've been unable find the root problem that's causing the cell to be blank to begin with. I must admit I've never used spreadsheets to the sophistication of this one and very rarely at all. So, my debugging isn't that good.

If someone could help me trace what's causing Wep_Enchants S15-19 to be blank I might be able to get it functioning from there.

Dontmindme · 05/17/08, 4:58 PM

Originally Posted by Axiss_Lothar

Every version from rogue.raidcal.com I've opened has the problem. As well as old versions I had used until they broke with the OO 2.4 update. I'm using the Open Office 2.4.0 version of Calc that's in the latest Ubuntu linux repositories. So, perhaps it's a problem specific to the linux source used in the build.

The problem is not specific to any one enchant or item, but a complete general failure. There are err:529s on the Buffed DPS sheet which then impact the Gear Buffs sheet. These errors on Buffed DPS are in I9, I13-15 and B8 which causes D8 to error. My tinkering started with I13 because it's the simplest formula. I can get rid of the I13 error by changing Wep_Enchants S16 to any number, but I've been unable find the root problem that's causing the cell to be blank to begin with. I must admit I've never used spreadsheets to the sophistication of this one and very rarely at all. So, my debugging isn't that good.

If someone could help me trace what's causing Wep_Enchants S15-19 to be blank I might be able to get it functioning from there.

Looking at I13...
First thing you can try is filling in all the blanks in Column P of the Wep_Enchants sheet with 0. I'm assuming Q32 is not errored out. Many of them are blank because the particular enchant is handled elsewhere. For example, a stat enchant like +20 agility is just added to the agility total so there is no additional proc effect which is what is represented in S13-S16. It's possible that your build for 2.4 isn't handling data types very well and doesn't want to call a blank as equivalent to 0.

I'm pretty sure I downloaded my OO directly from OpenOffice.org.

Karmon · 05/17/08, 5:16 PM

The problem seems to be the cell Q29 on the sheet "Buffed DPS".
In the 2.4.1.2_OO issue on rogue.raidcal this cell contains two invalid
reference; in OO they come out as " $'DPS Calc'.$#REF!$#REF! "
For me at least removing those two terms corrected the problem.
But I dont know what the purpose of that reference to the DPS_Calc sheet was.

Dontmindme · 05/17/08, 5:50 PM

Originally Posted by Karmon

The problem seems to be the cell Q29 on the sheet "Buffed DPS".
In the 2.4.1.2_OO issue on rogue.raidcal this cell contains two invalid
reference; in OO they come out as " $'DPS Calc'.$#REF!$#REF! "
For me at least removing those two terms corrected the problem.
But I dont know what the purpose of that reference to the DPS_Calc sheet was.

I went back to an old version to figure this out.
The problem can be fixed by changing the #REF! to B19. Apparently the Buffed DPS sheet had previously been pulling an unbuffed value into that cell instead of the related buffed value located at B19. It went #REF when I removed the unbuffed references.

Of course, I'm assuming the formula knew what it was doing. Didn't take the time yet to break down the formula. This also seems to affect Cold Blood used for instants in all versions, not just Open Office.

Karmon · 05/18/08, 6:01 AM

There are further quirks with OpenOffice since removal of the unbuffed sheets.
OO seems to dislike illegal references in formulas even if those never are executed.
This affects all the dpsuXXX values in the global sheet (since they depend on an IF(..;$Unbuffed..;$nexstat2) )
This in turn renders the AEP values on the talentsheet unusable ( because they use "unbuffed" values").
And this in turn makes the upgrade value columns in the indivudal sheets useless for OO users (sadly).

Also there is a general problem with trinket upgrade suggestions.
Since moving to proc mechanics, at least for those trinkets IMO the value in the "Bonus" colum (column W in "Trinkets" sheet) should be removed (that would be for all but Romulos Posion Vial).
At the moment it counts the proc twice; once via the proc mechanics, and once via the old bonus approximation.

DarkKnight388 · 05/18/08, 1:50 PM

Hmm... the website that hosts the DPS sheet seems to be down. Can someone give a mirror link for the new version, please?

• sp00n · 05/19/08, 8:10 AM

Small note, I adapted the current sheet to work with avoidance tanking:

RogueDPS_2_4_2_1.Tanking.rar

http://elitistjerks.com/750419-post437.html

Changed
-------
Talents
- Added (Points) Left for talent points
- Integrated talents and defense into parry rate
- Integrated defense into miss rate
- Replaced Armor with Total Avoid
- Replaced Expertise with Parried and swapped with Dodged

Gear_Buffs
- Def rating added (AP2)
- Added Defense as a stat
- Dodge details added (M1-M7)
- Replaced Resil with Parried (K5)
- Integrated def rating into dodge chance
  G4: =MAX((-0,59+E141/20+(K141/dorate)+(FLOOR(M141/4,0)/defrating_def))/100+0.2%*(CharLevel-BossLevel),0%)

Buffed DPS
- Added Parry % for MH/OH/Specials
- Added bMHpar

Global Var
- Added BaseParPerc (5% base, 3% increase per level, so total 14% against bosses)

Equipment
- Added Glyph of the Defender (+16def +17dod) (recplaced 8 Agility)
- Added Greater Inscription of Warding (15dod 10def) (replaced 10 Sta)
- Addded Slikk's Cloak of Placation (replaced Auchenai Death Shroud)
- Added 15 Defense to Chest (replaced 10 Sta)
- Added 12 Defense to Bracers (replaced 12 Sta)
- Added Nimble-foot Treads (replaced Black Featherlight Boots)
- Added Delicate Eternium Ring (replaced Band of Eternity 1st)
- Added Elementium Band of the Sentry (replaced Band of Eternity 2nd)
- Added Moroes' Lucky Pocket Watch (replaced Kiss of the Spider)


Problems
--------
- Parry rating on items doesn't exist in the sheet
- Only added a few tanking equipment pieces (those I planned with), missing others
- Untested in terms of compatibility with other sheets/imports/etc
- Some macros may not be working - untested (though I adapted the nextstat macro)
- Only Excel Version, no OO

Dontmindme · 05/19/08, 1:00 PM

I'll definitely have a look at that. I had dreams of someday adding avoidance stats but based on available time and the primary purpose of the spreadsheet, it was going to be a long time coming.

The only issue I see looking at the notes is parry chance. I've done a little baseline testing in the past and from what I recall parries seem to go away vs. level 60 mobs leading me to believe the skill adjustment is 0.5% per level. I also recall doing relatively small tests vs. level 72's leading me to believe the parry chance is linear to that point. Now, we all know "something" happens vs. bosses (73). My best hypothesis is that we have some formula where when weapon skill vs. defense > 10 there is an increased rate similar to how miss rate rises to 9%.

• sp00n · 05/19/08, 1:53 PM

Damn, just noticed an error with defense in my sheet, uploaded new version.
Also will have to re-evaluate my gear now.

As far as parry goes, 0.5% per level may be true against lower level mobs, but certainly not for higher. The parry chance seems to be more in the ballpark of ~12-15%, and 3% increase per level just seemed to fit fine. Also, you wouldn't need the exact formula for mobs level 71 and 72, since there's not much sense in having 100% avoidance against those, but lacking it against bosses.

Capek · 05/19/08, 11:10 PM

Apologies if i'm overlooking something blindingly obvious but I'm rather tired.
I cannot see the wastewalker helm in the headgear list but the 4pc set bonus is listed on the right.

Dontmindme · 05/20/08, 1:11 AM

Originally Posted by Capek

Apologies if i'm overlooking something blindingly obvious but I'm rather tired.
I cannot see the wastewalker helm in the headgear list but the 4pc set bonus is listed on the right.

Yeah, looks like the Helm list is truncated 2 slots leaving off Wastewalker and the (Unmodeled) option is all helms are enabled. A work around would be to turn off Hyjal or Black Temple which will shorten the list and include the Wastewalker Helm in the working range. I'll fix that with next update.

Dontmindme · 05/20/08, 1:13 AM

Originally Posted by sp00n

Damn, just noticed an error with defense in my sheet, uploaded new version.
Also will have to re-evaluate my gear now.

As far as parry goes, 0.5% per level may be true against lower level mobs, but certainly not for higher. The parry chance seems to be more in the ballpark of ~12-15%, and 3% increase per level just seemed to fit fine. Also, you wouldn't need the exact formula for mobs level 71 and 72, since there's not much sense in having 100% avoidance against those, but lacking it against bosses.

Well its certainly not accurate for bosses, but I recall that it was looking correct vs. 72's which were showing a dodge rate about equal to the parry rate.

Dontmindme · 05/20/08, 4:12 PM

052008beta

I've cleaned up some of the tanking/avoidance stuff and implemented some other bug fixes. Just an FYI for those adding enchants, the Item ID code you need is the "enchant code". The best way to find this is to go to Wowhead, click on the enchant text and look at the number in the parentheses.

There is now an Option box for attacking from the Front or the Back so that the main sheet can be dual use for avoidance.
This unfortunately also means that Option importation may have some issues again from previous sheets (which right not would be any others).

I haven't done any extensive testing yet, so as with all changes there may be some bugs.

Open Office users can check to see if I resolved those issues. You will probably have to delete the Copy boxes which interfere with the pulldowns.

Cryingleaf · 05/21/08, 3:30 PM

Hey all, I have a little dilemma and I'm looking for some input/help. My guild kills Felmyst every week now and sooner or later the pants [Leggings of the Immortal Night] will drop and I will most likely have first dibs.

Now the problem I have is, those legs vs. the ones I'm currently wearing (T6) have a huge amount of hit on them and most times we have a Boomkin in our raids. Now, my problem is, according to the spreadsheet, for max dps with the Boomkin in the raid I would have to resocket about 5 [Glinting Pyrestone] with [Delicate Crimson Spinel] which would total at about a 65 dps upgrade. But... if the Boomkin isn't there it is only about a 13 dps upgrade and keeping my current sockets would yeild about 20 dps w/o Boomkin and about 43 dps with Boomkin.

So my question is do any of the other rogues that raid with a boomkin always gem as if the boomkin will always be in the raid? or do you gem somewhere in between the hit cap with and without boomkin?

Trazhenko · 05/21/08, 5:19 PM

Well if you are running higher than 316 hit rating with 5/5 precision and a boomkin, you're giving away free stats. I guess the big question is how far over are you going to be, and can you easily swap down without regemming items.

Start with food. It doesn't make any difference if you gem hit to 363 and eat agi food or swap 20 hit worth of gems for 20 agi worth of gems, then eat hit food. Those last 3% only matter for bosses, and you're going to be eating buff food, so there's no reason to ever gem your hit over 343. 27 rating to go.

Swap items. If your ranged weapon has hit, you may be able to swap in an old one that doesn't and come out ahead while the boomkin is there. Same goes for rings. Maybe you drop Stormrage Signet Ring for something with no hit, say... the ZA ring. Alone the ring isn't as good, but if all that hit on Stormrage is useless, the ZA ring is better.

If you can't get all the way down to 316 with a couple easy swaps, you might have to change some gems to make best use of the moonkin, but if you have old gear with no hit lying around, you might be able to avoid sucking up those spinels.

Xaoc · 05/21/08, 6:09 PM

Not sure if you're doing this already, but but you could swap out your cloak for Dory's. Trading some hit and AP for crit and ArP. And, like Trazhenko mentioned, start out with simple things like food, and work your way up from there. Of course, you could always have 2 sets of the same gear--1 geared for Boomkin, 1 not--but not everyone can do that.

Left · 05/21/08, 6:20 PM

Originally Posted by Trazhenko

Swap items. If your ranged weapon has hit, you may be able to swap in an old one that doesn't and come out ahead while the boomkin is there. Same goes for rings. Maybe you drop Stormrage Signet Ring for something with no hit, say... the ZA ring. Alone the ring isn't as good, but if all that hit on Stormrage is useless, the ZA ring is better.

Originally Posted by Xaoc

Not sure if you're doing this already, but but you could swap out your cloak for Dory's. Trading some hit and AP for crit and ArP. And, like Trazhenko mentioned, start out with simple things like food, and work your way up from there. Of course, you could always have 2 sets of the same gear--1 geared for Boomkin, 1 not--but not everyone can do that.

Rather than gemming out two sets of gear, you should be able to just carry around two different items for specific slots. For example, carry around both [Band of the Eternal Champion] and [Stormrage Signet Ring] (or any other combination of one ring with +hit and one without). Use the better one (which has +hit) when you don't have a Boomkin; swap in the other when you do have a Boomkin. Other slot options include [Shattered Sun Pendant of Might] versus [Choker of Endless Nightmares] or even [Warp-Spring Coil] versus [Shard of Contempt] or [Ashtongue Talisman of Lethality].

And of course you always have the option to just gem for the hit cap with the Boomkin there, eat +hit food when you don't have a Boomkin, and not worry about the truly small amount of DPS lost when you don't have one there.

• Vulajin · 05/21/08, 6:27 PM

Consider this:

- The hit cap for bosses with both Precision and Imp Faerie Fire is 316.
- The hit cap for level 72 trash with only Precision is 316.

If you gear yourself for the 316 hit cap (effectively assuming that the moonkin is always there), you get the best possible combination. If the moonkin is there, you don't waste hit on bosses, and while you do waste some hit on trash, the probability of the moonkin missing Imp FF on a mob here and there is pretty good over a trash clear. Same applies to any adds on a boss fight. If the moonkin isn't present, none of your stats are wasted on a boss, and on trash you get slightly better performance than if you had hit above 316.

Karmon · 05/22/08, 8:11 AM

Issues with 052008beta:

OpenOffice:
OpenOffice AEP are bogus: this due to the fact that the AEP calculation on the "Buffed DPS" sheet has constants in the cellrange B27:B34

Trinketupgrade column in sheet "Tinkets" shows errors. This can be fixed by changing the two cells AF70:71 on the gear sheet to look like that:

AF70: 
=$D70*dpsStr+$E70*dpsAgi+$G70*dpsCrit+$H70*dpsHit+$I70*dpsAP+$L70*dpsExp+$P70*dpsHaste+$Q70*dpsArmor+N(VLOOKUP($B70;TrinketStats;22;FALSE()))

AF71:
=$D71*dpsStr+$E71*dpsAgi+$G71*dpsCrit+$H71*dpsHit+$I71*dpsAP+$L71*dpsExp+$P71*dpsHaste+$Q71*dpsArmor+N(VLOOKUP($B71;TrinketStats;22;FALSE()))

Note the use of the function N(..) for the result of the table lookup.

General problem:
The sheet still shows a 5pt eviscarete dmg even for cycles that do not contain eviscerate at all.
Problem is cell V18 in the "Buffed cycxle" sheet. Could be that it is a leftover/improper coding when the way AR improved DPS was changed.

Dontmindme · 05/22/08, 12:20 PM

Open Office AEP is currently "offline" meaning that it needs to be rebuilt. The old OO AEP calculations were so error-ridden, that I felt it best to disable it until this could be done. Comparing them to the sheet calculated AEPs, they were becoming different enough, they weren't very useful. They were also not conducive to the new modeling changes, in fact the improved implementation of extra attack mechanics caused some values to go negative and caused the spreadsheet to error out.

The same really also applies to the trinket upgrade column on the hidden "Trinket" sheet. These values are not going to be accurate until the OO AEP is rebuilt. This said, the spreadsheet is still as accurate in Open Office, it's just that one needs to switch the trinkets on the Gear _ Buffs sheet to see how this affects one's DPS. I suspect the Trinkets Upgrade column was never all that accurate as it was.

As to Eviscerate, that is displaying an average damage for eviscerate. It defaults to the damage of a 5-pt Eviscerate for cycles that don't use it. You will notice that the DPS in the Buffed DPS sheet still shows 0 for Eviscerate (or it should). This is as intended. This way, for cycles that do not contain eviscerate but where one has Adrenaline Rush it has damage to work off of to create one Eviscerate DPS.

Basically, even though it shows damage, if this damage is multiplied by 0 eviscerates per second, the resulting DPS will still be 0 no matter what shows in the eviscerate damage box.

Dontmindme · 05/22/08, 2:48 PM

New beta available...

052208beta

Changes since 052008beta...
More unbuffed cleanup of formulas especially the OO AEP Upgrades on the hidden item sheets.
Added the 2 new Sunwell items.

As usual found at http://rogue.raidcal.com

Karmon · 05/22/08, 6:33 PM

Originally Posted by Dontmindme

As to Eviscerate, that is displaying an average damage for eviscerate. It defaults to the damage of a 5-pt Eviscerate for cycles that don't use it. You will notice that the DPS in the Buffed DPS sheet still shows 0 for Eviscerate (or it should).

That is what I thought. I just downloaded the new beta sheet. It advertises a 4s5r cycle yet the row 10 in the "buffed DPS" sheet shows nonzero Evis dmg(E10, I10 J10).

Dontmindme · 05/22/08, 11:01 PM

I'm assuming you have Adrenaline Rush? The spreadsheet is currently assuming that with AR, you will be fitting in Eviscerate into your cycles when you pop that. Unfortunately the Europe armory is redirecting me to an unrelated main page at the moment so I can't check your spec.

Karmon · 05/23/08, 5:45 AM

Ok I see where I jumped to conclusions.
I saw the value of Eviscarate Dmg in Cell E10 but somehow I never bothert to check if it really counts as DPS in cells I10 ,J10.
Maybe use arush*$E$33 in said cell V18?
Saves some trouble with the unwary user...