Redcape's Ret DPS Calculator 3.3. (Fully updated for 3.2 patch)

Redcape · 05/01/09, 10:34 PM

I have created this new thread to link to my newest version of my ret dps calculator. I would like the thread to remain about spreadsheets and simulators, modelling of fights and mechanics. Please use the other threads in this forum for questions about rotations, glyphs, or complaints/questions about ret in general.

Please use this thread for discussion of how to improve modelling, possible errors or needed improvements for any spreadsheet or discussion on the possible differences of opinion between the various ways of modelling ret damage available. This should be a forum for spreadsheet designers and users, not just a second ret questions thread.

That said, if you do have anything to contribute or suggestions for improvement for my spreadsheet please do step up and post. Also, feel free to discuss other spreadsheets or modelling programs here, this isn't meant to be exclusive to my sheet in any way.

New hosting! Thanks to psychosomatic for hosting my sheet now.

The two documents are 2.5 (last version compatible with Open Office) and 3.3 (Current best version)

Redcape's Spreadsheet

Some notes about the new version:

Changed modeling for Berserk based on new data, assumed uptime: 75%.

Added option on Talents and Buffs page to enable or disable Heroism modelling.

Removed SoB/SoM modelling, made the front page assume SoV use.

Currently modelling JoR as proccing seals, and JoC/JoV as not doing so.

Added support for tier 9 set bonuses, new glyph and new librams.

Added the ability to save and load gearsets in the Gear section.

Added additional information on overall performance to back pages to reduce clicking around.

Updated for increased profession perks in 3.2.

Changed CS, Judgement and Seal information to match 3.2 models.

Changed default delays to match new rotation styles.

Enjoy!

TheBacon · 05/02/09, 9:07 AM

As a Mac user I'm a little unclear on what the best course of action would be. Should I just disable the macros and use this version or continue to use the older 2.5?

Kethion · 05/02/09, 9:25 AM

It looks like there is a bug with the weapon speed option for test gear. Alterations to speed on the main page seem to be functioning correctly. Slower weapons with the exact same stat allocation are showing a significant DPS loss, while fast weapons are showing a massive DPS boost. I also occasionally get a "division by zero" error when I input the weapon speed as 1.

Here's am example of what I'm getting: http://img23.imageshack.us/img23/8121/bugxyi.png

For reference, the stats are for the Furious Gladiator weapons. When using the proper 3.6 speed it's showing a loss of 285 DPS, where in v2.5 of the spreadsheet it was ranked around -0.8 DPS.

Redcape · 05/02/09, 1:40 PM

Originally Posted by TheBacon

As a Mac user I'm a little unclear on what the best course of action would be. Should I just disable the macros and use this version or continue to use the older 2.5?

I would suggest using the 2.5 version. Aside from the ArPen changes it works fine, and you can pretty easily approximate those changes by lowering the value of ArPen by 3% or so.

Originally Posted by Kethion

It looks like there is a bug with the weapon speed option for test gear. Alterations to speed on the main page seem to be functioning correctly. Slower weapons with the exact same stat allocation are showing a significant DPS loss, while fast weapons are showing a massive DPS boost. I also occasionally get a "division by zero" error when I input the weapon speed as 1.

Here's am example of what I'm getting: http://img23.imageshack.us/img23/8121/bugxyi.png

For reference, the stats are for the Furious Gladiator weapons. When using the proper 3.6 speed it's showing a loss of 285 DPS, where in v2.5 of the spreadsheet it was ranked around -0.8 DPS.

Edit: I found the bug, some of the DPS calculations were referencing the test gear stat list instead of standard, which causes no issues unless you have a different speed entered in the test gear section. Bug squashed, 3.01 posted now and should work fine.

Edit2: Putting in speed of 1 still generates an error in 3.01. I fixed that internally and will release it with the next update. No real need update the download yet since there really isn't any reason we need to model a speed 1 weapon. Other speeds all work fine.

gouldukat · 05/02/09, 6:56 PM

Just to know why you used the Microsoft office support of macro instead of the Open Office one who can use all people whit out hassle ?

Ssateneth · 05/02/09, 7:45 PM

There still seems to be a bug as far as test weapons go. Was trying to figure out the difference between Ironsoul, Jawbone, and Betrayer of Humanity, but as I was plugging in the test info, my net DPS gain did not change considerably when entering in WDPS. I experimented, putting in widely varying values. I'd think that having a 10k dps weapon would give me a ridiculous gain at least, but nada.

• promdates · 05/03/09, 6:00 AM

Would it be possible to have it download from the Armory, and potentially even separate gear/gems/enchants better? Sort of like drop down menus to show each item, enchant, gem?

Ssateneth · 05/03/09, 12:44 PM

Originally Posted by stabbymcgee

Would it be possible to have it download from the Armory, and potentially even separate gear/gems/enchants better? Sort of like drop down menus to show each item, enchant, gem?

I'm going to guess this was mentioned earlier.. It's probably impractical to apply. I find it faster to manually put in the stats anyways instead of picking through a huge list of items to find the right one anyways.

Redcape · 05/03/09, 1:47 PM

Originally Posted by gouldukat

Just to know why you used the Microsoft office support of macro instead of the Open Office one who can use all people whit out hassle ?

I first built my sheet in open office, but since I acquired Excel I find I like Excel hugely more. While I am usually the sort of person who is against the evil empire I find that Excel is really a product that is superior to its competition. The old versions continue to work, and although there is the possibility I will update them at some point I just don't have the spare time that I used to. Excel and its macros have decreased development time so much I can't really imagine going back unless I suddenly become unemployed.

Originally Posted by Ssateneth

There still seems to be a bug as far as test weapons go. Was trying to figure out the difference between Ironsoul, Jawbone, and Betrayer of Humanity, but as I was plugging in the test info, my net DPS gain did not change considerably when entering in WDPS. I experimented, putting in widely varying values. I'd think that having a 10k dps weapon would give me a ridiculous gain at least, but nada.

This was a similar bug to the previous weapon speed one. I have fixed it too and will be pushing out a new version today with all (I hope) corrections made. Also, the new version will have proper support for librams instead of the hacked together mess I had before.

Originally Posted by stabbymcgee

Would it be possible to have it download from the Armory, and potentially even separate gear/gems/enchants better? Sort of like drop down menus to show each item, enchant, gem?

Possible, but honestly I find searching through all the gear on Rawr to be tedious in most cases. I doubt I will implement any kind of gear system that searches through all available gear from a web source.

Originally Posted by Ssateneth

I'm going to guess this was mentioned earlier.. It's probably impractical to apply. I find it faster to manually put in the stats anyways instead of picking through a huge list of items to find the right one anyways.

Yes. I have some ideas for improving the gear interface like saving equipment and having drop downs for enchants and such, but since enchants are almost completely static (barring icewalker/AP to boots based on hit rating cap) there really isn't a big incentive to change that chunk of the sheet. An improved gear interface is likely going to be my focus over the next while, but don't expect anything soon.

Redcape · 05/03/09, 3:15 PM

All right, 3.1 is up now. Weapon speed bug and weapon damage bug (from the test gear section) are gone. Librams now properly supported, located in buffs and talents section. Hit rating and expertise rating returns now indicated in red when you are above the caps, hit rating returns above the soft cap are in brackets. This last is because hit rating still helps exorcism and consecrate dps for some time after the melee hit soft cap is reached.

• promdates · 05/03/09, 5:25 PM

Originally Posted by Redcape

Possible, but honestly I find searching through all the gear on Rawr to be tedious in most cases. I doubt I will implement any kind of gear system that searches through all available gear from a web source.

I didn't mean checking for upgrades through the armory... I meant to download your current gear from the armory. Since there's a few other spreadsheets with that function that you can just click a button and get your current gear set from armory, instead of going through the work of actually putting in each item, separating the gems and enchants from it and putting them in a section section.

One of the things that pushes me away from using this sheet is the information set for importing your gear isn't easily laid out. Yes, you have a section for "Main Gear Sets" and a section for "Enchants and Belt Gem". Where do you find out the information for each trinket, where do you put your gems at (I assume in the enchant section)? If you're comparing two items, do you put the enchant and gem numbers in the test section as well, because that's also not clearly defined.

There just seems to be no documentation on how to use this, which could push newer people away from using this one and more towards using another one. Or just staying with Rawr (which I dislike, personally).

• frmorrison · 05/03/09, 6:14 PM

Originally Posted by stabbymcgee

Where do you find out the information for each trinket, where do you put your gems at (I assume in the enchant section)? If you're comparing two items, do you put the enchant and gem numbers in the test section as well, because that's also not clearly defined.

There just seems to be no documentation on how to use this, which could push newer people away from using this one and more towards using another one. Or just staying with Rawr (which I dislike, personally).

I just added up all the enchants/gems on each item. Trinkets you just approx. the value. Sure the sheet isn't that user friendly, but once you enter your current item's stats in the fields it isn't too bad.

Redcape · 05/04/09, 9:04 PM

Originally Posted by stabbymcgee

I didn't mean checking for upgrades through the armory... I meant to download your current gear from the armory. Since there's a few other spreadsheets with that function that you can just click a button and get your current gear set from armory, instead of going through the work of actually putting in each item, separating the gems and enchants from it and putting them in a section section.

One of the things that pushes me away from using this sheet is the information set for importing your gear isn't easily laid out. Yes, you have a section for "Main Gear Sets" and a section for "Enchants and Belt Gem". Where do you find out the information for each trinket, where do you put your gems at (I assume in the enchant section)? If you're comparing two items, do you put the enchant and gem numbers in the test section as well, because that's also not clearly defined.

There just seems to be no documentation on how to use this, which could push newer people away from using this one and more towards using another one. Or just staying with Rawr (which I dislike, personally).

The functionality to download your gear from armory is missing largely because I don't know how to code it! I am just teaching myself spreadsheets and VBA as I go, so while that sort of thing is certainly possible to build, I have not gotten far enough along to do it.

I think adding in a gems section is something I may do, but I don't think it is nearly as complicated right now as you are making it out to be. There is a section labelled "Enchants and Belt Gem" where enchants obviously go. You just add the gems and set bonuses in to your item stats and enter them into each item slot as appropriate. Setting the gem bonuses aside for each piece of gear is something I could implement but it isn't going to save you any time.

As far as generating numbers for each trinket goes, I really do expect people to do that on their own. For any trinket you can easily look up its stats on wowhead and figure out what expected uptime is on procs to generate a good approximation.

Trying to make the gear section a little more readable and intuitive is something I will definitely be working on for my next patch, though I just don't know what form it will come in. I will probably build something in to allow you to more easily swap out gear and save pieces of gear.

sag_ich_nicht · 05/08/09, 4:15 PM

Originally Posted by Redcape

The functionality to download your gear from armory is missing largely because I don't know how to code it! I am just teaching myself spreadsheets and VBA as I go, so while that sort of thing is certainly possible to build, I have not gotten far enough along to do it.

Perhaps take a look at other spreadsheets (possibly even pre-WotLK ones) to get information on how to do that, if I remember correctly the Rogue DPS spreadsheet had quite the nice armory crawler.

You could also look at Bellator's spreadsheet for some of the features some people have been requesting, I'm sure some of it is quite salvageable.

Ssateneth · 05/11/09, 1:20 AM

I greatly appreciate your hard work, Redcape. It makes it vastly easier to decide on rolling on an item or not when it drops. Now you'll have to excuse me for a bit if this sounds dumb. To me, it feels 'right' to have stat values normalized to strength, which I've implemented in all revisions of your spreadsheet. In the latest revision though (3.1) there seems to be something off. Something was changed as far as the stat value goes for Hit rating. I'm assuming the large value is while you are not melee hit capped, and the small number (to me it is small since I'm melee hit capped) is spell hit capped.

I'm assuming this has something to do with macros or something, because I can't determine where this value is coming from. I traced it to I9 on Ability Use Information, and that cell just has a number, no references to other cells for calculations. I don't suppose you can help me pulling out the calculations for both of those numbers? I have no clue if macros are enabled on my computer. I use OpenOffice 3.0.1 (build 9379)

Redcape · 05/11/09, 9:01 PM

The numbers that tell you which stat is best for you are derived by having a macro check your dps, add a fixed amount of the stat to be tested to your character, check total dps again and subtract. If you change values on your sheet, do the stat return values change? If so, things are working fine for you. If not then macros aren't working for you.

You are correct about hit, the large number is how good hit it assuming no melee cap and the small number is how good hit is above melee cap but below spell cap. That small number only appears when you go beyond the melee hit cap.

Ssateneth · 05/16/09, 5:11 AM

Originally Posted by Redcape

If you change values on your sheet, do the stat return values change?[/u][/b] If so, things are working fine for you. If not then macros aren't working for you.

No... The numbers from "% DPS returns by stat" area does NOT change when I change numbers in the "Main Gear Stats" area on the gear tab or "Bonus Stats for testing" area on the Main tab. They used to change all the time on 2.xx versions. Maybe openoffice is not compatible with the new format?...

edit: Seems openoffice isn't compatible. I tried excel 2007 on a different computer and there were new grey square buttons on some of the sheets. The % dps returns also changes when I changed my gear stats. All I want is a value in one box for under melee hit cap, and another for over melee, under spell hit cap in another box. There's a line I found in what seems to be the complicated mess of macros for each value (Can barely make heads or tails of it.. I'm not fluent in excel) which seems to have what I want, but I can't figure out how to output the two values to seperate boxes.

Sheets("Ability Use Information").Range("i9") = Round(Sheets("Ability Use Information").Range("i9"), 0) & " (" & Round((Sheets("Ability Use Information").Range("d56").Value - DPSDiff3), 0) * (2) & ")"

I'm fine with whatever is there, I just want to add in something around that line that will, say, put under melee hit cap in main h20 and over melee hit cap in main h21, preferably without being rounded so I can get exact numbers. Buuuuuuuut... like I said, I'm not fluent and can't figure out how to do that. I apologize if I seem annoying.

Edit2: Never mind the mess up above. 2 aspirin later and a small break, I managed to get what I wanted.

        DPSDiff1 = Sheets("Ability Use Information").Range("d54").Value
        DPSDiff2 = Sheets("Ability Use Information").Range("d55").Value
        DPSDiff3 = Sheets("Ability Use Information").Range("d56").Value
        DPSDiff4 = Sheets("Ability Use Information").Range("d57").Value
        Sheets("Calculations").Range("o33").Value = 50
        Sheets("Main").Range("h20") = Sheets("Ability Use Information").Range("i9")
        Sheets("Ability Use Information").Range("g9") = Round(Sheets("Ability Use Information").Range("g9"), 0) & " (" & Round((Sheets("Ability Use Information").Range("d54").Value - DPSDiff1), 0) * (2) & ")"
        Sheets("Ability Use Information").Range("h9") = Round(Sheets("Ability Use Information").Range("h9"), 0) & " (" & Round((Sheets("Ability Use Information").Range("d55").Value - DPSDiff2), 0) * (2) & ")"
        Sheets("Ability Use Information").Range("i9") = Round(Sheets("Ability Use Information").Range("i9"), 0) & " (" & Round((Sheets("Ability Use Information").Range("d56").Value - DPSDiff3), 0) * (2) & ")"
        Sheets("Ability Use Information").Range("j9") = Round(Sheets("Ability Use Information").Range("j9"), 0) & " (" & Round((Sheets("Ability Use Information").Range("d57").Value - DPSDiff4), 0) * (2) & ")"
        Sheets("Main").Range("h21") = (Sheets("Ability Use Information").Range("d56").Value - DPSDiff3) * (2)
        Sheets("Calculations").Range("o33") = ""

Now I have two seperate, unrounded values. Wooooooooo.

.... On a sidenote, while working on base values, I noticed something off concerning base mana. The spreadsheet was giving me more mana than what I had in WoW, even though intellect was the same and base mana was correct. I think that the first 20(?) intellect doesn't count towards mana or something. As it stands, I have 98 intellect and 5584 mana untalented ungeared. That leads to 4114 base mana, but when I judge, I gain 1099 mana back (25% base mana) which leads point to 4396 (After retalenting, no gear). That's a difference of 282 mana, or about 19 intellect. I am a human. This is the -exact- difference in the spreadsheet.

Redcape · 05/16/09, 1:58 PM

I will check into the int issue shortly. It isn't usually very critical, but it is certainly worth fixing for the next edition. Good catch.

My apologies for those who don't have access to macros due to using openoffice, but given my lack of time for development these days I needed to switch to using them heavily to reduce the time needed to update the sheet as new discoveries/changes are made. The old sheet still gives pretty good values at the moment though if you want to use it.

Sharrow · 05/23/09, 8:21 AM

I believe that I have found a minor error in the sheet. The "Mana In / Sec" total on the main page does not include mana from The glyph of Seal of Blood, even though it is listed on the page, due to the total's calculation being incorrect. Cell H49 should read "=SUM(H42:H47)" rather than "=SUM(H42:H46)".

Redcape · 05/23/09, 8:11 PM

Originally Posted by Sharrow

I believe that I have found a minor error in the sheet. The "Mana In / Sec" total on the main page does not include mana from The glyph of Seal of Blood, even though it is listed on the page, due to the total's calculation being incorrect. Cell H49 should read "=SUM(H42:H47)" rather than "=SUM(H42:H46)".

Thanks for catching that. Not that I expect people to use the SoB glyph much, but this will be fixed in the next update.

Redcape · 05/31/09, 1:36 PM

I would like to start a discussion here on improving the way we define our contribution to the raid's success as far as gearing goes. Specifically, our current models are designed to model a ret paladin with full buffs bashing on a single boss level target. While this is clearly a useful model, it does not come close to actually modeling our contribution to the raid as a whole and is in fact a terrible model for gearing for many encounters.

For example, Str is clearly a good dps stat. Once hit capped it is the best dps stat against a single boss, closely followed by expertise. However, there are many complications that regularly change this formula we are not currently accounting for. In particular, Str increases the power of our Sacred Shield and JoL. Also, expertise counts for double in some situations (hitting bosses from the front, hitting adds a lot of the time) and counts for nothing in many other situations (while expertise capped at 5% against Freya adds).

On some fights like Vezax the single boss mob model works fine in terms of dps (but does not at all model the huge power of JoL and SS) but on fights on Freya our current baseline model is absolutely terrible for dps.

Of course on fights with randomly debuffed mobs of varying levels and facings any model we construct is going to be at best an approximation and at worst quite wrong. That said, I think we can do a lot better at modeling the wide variety of fights in Ulduar than our current standards (my spreadsheet included) are doing.

I am asking here then for some input into modeling on several fronts:

1. How much do we value SS and JoL? How big a deal are putting shields on the tank and healing the raid? Clearly they are relevant, but HOW relevant per point?

2. How do we value expertise and hit considering we are often attacking from the front or attacking mobs that are not boss level? Part of this equation is of course how much dodge/parry/miss level 80, 81, 82 mobs have, so if you have good data on their baseline dodge/parry/miss levels I would appreciate knowing that.

3. What parts of the various hardmodes we face in Ulduar are really important? For example, we could talk about modeling trash vs. boss dps benchmarks on Razorscale, but since that fight is really quite trivial for any decent group there simply isn't much point. However, balancing adds vs. boss dps on Freya or Yogg is very relevant and may change gearing choices. On Freya you should just win if you get past all the add phases (you CAN wipe afterwards, but if your raid is intact you sure shouldn't) but maximizing dps on adds in the Yogg fight is a tricky proposition.

If you have useful input (or good numbers for trash mob avoid values) please post so we can discuss this. I would ask that you refrain from useless statements like "improving JoL does nothing because it is all overheal" because clearly improving JoL is sometimes junk (sometimes it is awesome), but we should try to find some way to place a sensible value on that benefit, even if it is low.

Redcape · 06/13/09, 11:50 PM

A few people have contacted me to say that my file is no longer accessible on filefront. Apparently it got deleted by filefront due to inactivity. Unfortunately filefront is being terrible tonight and I can't get it to upload anything. I will endeavour to get things reposted asap.

Redcape · 06/14/09, 1:45 PM

Filefront finally cooperates, new 3.11 version uploaded. Very minor changes to fix a few small bugs, new 3.12 with a few more changes should be coming in the next little while.

Redcape · 06/15/09, 9:11 PM

New hosting now, see link above. 3.12 is out, the only change is a small bugfix involving expertise valuation against multiple targets. If you don't care about that, 3.11 is just as good.

Scorpiodrush · 07/10/09, 3:38 AM

I Just used your 3.12 spreadsheet and I don't know two things. A. on the gear section do I add the +21 agi from the metagem into my Helm Data, or does the 3% on the buff section include the 21 Agi?

Second, after inputting y Data, the first page highlights Hit Rating on the Stat weights in Red says.. 217 (14) in red.
I'm guessing that means I'm Over hit value (because I am). whats the (14) mean?