Jump to content


Photo

Rogue DPS Simulation Spreadsheet


  • Please log in to reply
767 replies to this topic

#1 Mavanas

Mavanas

    Great Tiger

  • Members
  • 818 posts

Posted 26 March 2009 - 02:53 AM

Rogue DPS Simulation Spreadsheet

Requirements: Excel 2003 or later. Only tested in Windows OS. Prior versions of Excel are not advisable due to deficient RNG.
If you are using release version of Excel 2003, you might have to run a hotfix to RNG available here: The RAND function returns negative numbers in Excel 2003.
The file does not work in Standard Open Office, but may work in GO Open Office with some additional changes.


File Description:
Gear - enter your gear, socket bonuses, gems, enchants etc. Only close to best in slot items are included.
Inputs - enter buffs, choose food buffs and flasks, choose the number of iterations (max 1000), choose fight duration (max 300sec)
DPS Strat - choose the rules that govern your choice of abiltities, choose the combo point builder, MH and OH poison - more on DPS rules below
TalentsGlyphs - choose talents and glyphs, feel free to use the presets


DPS Results and Statistical Significance
You operate the simulation from the Inputs tab, using the provided macros. Because this is a simulation, your dps is determined as a result of using RNG to simulate combat a set number of times. At the end of the simulation, the spreadsheet displays the average DPS value over those tries in the field called Latest DPS. In order to compare DPS under two different setups, first run the simulation with setup A and Save it as Baseline. Then run it with setup B. You can now compare the dps of the Baseline DPS and Latest DPS to determine which one is higher. However, keep in mind that it is a simulation and you need to take into account not only the average DPS, but also the statistical significance of the results. Below the DPS values in the Inputs tab you can see percent difference in dps and a statistical p-value which serves as a measure of confidence in the results. Sometimes, Baseline and Latest DPS will be different only because of RNG and not because one setup is superior to the other. In that case, the p-value is large and is colored in grey. In statistical language, the difference between the two dps values is said to be not statistically significant. However if the p-value is small enough, it will be colored in bright orange. If the p-value is 5%, it means you can be 95% sure that the setup you just determined as superior will indeed produce higher DPS, i.e. the outcome cannot be explained by mere RNG. To find out more about the t-test of statistical significance visit this wikipedia webpage.


A guide to DPS rules:
The rules can be roughly split into restrictions, exceptions and other rules.

- Restrictions limit when you use a particular ability based on your energy and combo points. They can be used to imitate rotations and implement energy pooling. For instance setting "do not use if combo points are below" values at 3, 5, 5 imitates a 3+/5/5 Xs/Yr/Zt cycle familiar to Combat spec users. If you want to pool energy to 60 before eviscerate, set "Do not use if energy is below" at 60 for eviscerate. Envenom also has a restriction based on DP stacks.

- Exceptions are special rules that override the restrictions when certain conditions apply. For instance, for HaT build, you may normally want not to use eviscerate below 4 combo points, but if energy is capped, you may want to allow to use eviscerate anyway, even if you have fewer than 4 combo points. The best way to do that is to set "Override if energy capped (overrides combo point check)" to 1 for eviscerate. Other overrides are used for situations when you are combo point capped and when your envenom, snd and rupture timers are about to drop.

- Priority governs which ability is going to be used if conditions for more than one ability are satisfied. For instance, if based on your restrictions and exceptions you can use both eviscerate and rupture, you can give more priority to rupture by setting its number to a lower value. Make sure that every ability is ranked the way you want it to. Note that most of the time, which ability you use is going to be governed by your restrictions and exceptions, and only in limited cases it will come down to this priority list to decide which ability to use.

- Refresh timer allows you to refresh snd, rupture and envenom before it runs out to avoid a situation when you are stuck without SND for a second during a global cooldown.

- other special rules govern the timing of finishers: you can choose to time envenoms to DP ticks, and you can also choose to put other finishers on hold when rupture is about to expire. You can also refresh SND early if it's due to expire at the same time as rupture.

You can think about all these rules in two ways: a) if you want to model your personal dps, choose the rules that best describe your own combat style; B) following the discussion in this thread, learn which DPS rules tend to increase your dps and adopt them in your own rotation.

You can find templates of rules for each spec at the bottom of DPS Strat tab, but I strongly encourage you to play with these restrictions to find an optimal rule for your playstyle and gear.

Gear Selection Q&A:

How do I add a new piece of gear?
- go to the section that matches the type of your new item. Select a row with an existing item that has the same or close item level as the item you want to add. Copy an entire row and insert it below. Then change the item name, stats, number of sockets, socket colors and socket bonuses.

How do I add a new trinket?
- you can only add a trinket if there already exists one with similar proc characteristics in the sheet by changing the stats and proc values of that trinket. The template trinket has to proc the same stat, the proc has to have same duration, internal cooldown and proc rate. The template trinket also has to proc from same attacks. Generally there are three main types of proc mechanics: 1) autoattack hits and applications of special abilities (e.g. Mjolnir Runestone), 2) autoattack and special crits (e.g. Needle-Encrusted Scorpion), and 3) autoattack hits, applications of special abilities, poison hits, poison dot ticks and rupture dot ticks, basically all damage (e.g. Whispering Fanged Skull). If the new trinket has the same characteristics as the template one, then you can change the stats and proc value and start using it.

How do I choose gear based on the simsheet?
- The best way to compare two items is to run the simsheet once with one item on, save DPS, then change the item and run the simulation again. You can now compare the DPS and determine the statistical significance of the result. See a note above statistical significance in "DPS Results and Statistical Significance" section above.
- If you want to determine several potential upgrades, I can also recommend running the EP value macro and apply the dps/point values to gear stats. To do that, after running the EP macro, find DPS/point values in row 10016 of DPS Output, copy them and paste as values into the appropriate range at the top of the Gear tab. The DPS values of each item in the Custom column will then reflect your dps/point stats, so you can select gear with highest stats. You can also see the EP values that correspond to dps/point stats in the EP Value section of the Gear Tab in the row labeled "Custom." Keep in mind that dps/point estimates also have RNG variance embedded in them, so after you do yout preliminary selection, you need to run the simsheet with the items you selected to confirm your choice by swapping in some close alternatives. Also items with procs cannot be selected by this method. Finally you cannot choose between weapons with different dps because this method only evaluates the stats not the weapon dps.

What does replacing capped stats do?
- If you keep your expertise at the expertise cap by gemming, then selecting a new item with more expertise will not add more expertise to your gear, but will instead allow you to regem your gear with another stat. The best alternative for expertise right now is AP, so by choosing this option, expertise will be valued as AP in the "DPS value of Gear" section. The same can be done with hit by replacing its DPS value inflated by crit capping and spell-hit capping with that of Haste.


The latest version of the Simulation Spreadsheet:
Simsheet 3.3.3a - changed for patch 3.3.3a, with HAT support, including shadow dance.

Filefront mirror

#2 Sarah

Sarah

    Glass Joe

  • Members
  • 21 posts

Posted 26 March 2009 - 03:35 AM

Thanks for your work on this.

I have a question regarding some of the `DPS Strat' options. What exactly does `Override if envenom buff dropped' do? Does it drop the combo points requirement or the deadly poison stacks requirement? Or both? And the `Refresh if this many ticks are remaining' option, is that an override as well (i.e. it drops the deadly poison stacks/combo points requirements) or is it an additional requirement before envenom may be used?

#3 Mavanas

Mavanas

    Great Tiger

  • Members
  • 818 posts

Posted 26 March 2009 - 04:21 AM

Thanks for your work on this.

I have a question regarding some of the `DPS Strat' options. What exactly does `Override if envenom buff dropped' do? Does it drop the combo points requirement or the deadly poison stacks requirement? Or both? And the `Refresh if this many ticks are remaining' option, is that an override as well (i.e. it drops the deadly poison stacks/combo points requirements) or is it an additional requirement before envenom may be used?


1. Override if envenom buff dropped - currently only overrides the DP stack check, but I can see how you might want it to override both checks, so I might change it for next version.
2. Refresh if this many ticks are remaining - you could think of it as an override too. By default, SND and Rupture are not used if they are still up, however this option allows you to refresh SND and rupture even if they have not expired yet (there is no check for "a more powerful spell is already active" errors). For envenom, this option is used in conjuncture with "override if envenom buff dropped" option. When the check for envenom buff dropping is done, if "this many ticks of envenom are remaining," it will flag envenom for a refresh and will do so in spite of the DP stack requirement.

#4 Mavanas

Mavanas

    Great Tiger

  • Members
  • 818 posts

Posted 30 March 2009 - 03:50 PM

I have reworked the override if envenom buff dropped into a more universal override which will cause to ignore energy and combo point restrictions if the corresponding timer drops (or about to drop as determined by refresh timer). It applies to envenom, slice and dice and rupture. For envenom it also overrides the DP stack requirement.

Also I have simplified weapon selection, so you only need to choose your weapon in the Gear tab and everything else will automatically go through.

For 3.1 version, I have implemented most changes that affect DPS on the current PTR version. The list of implemented changes can be found in a tab inside the file. Obviously 3.1 version is subject to change.

The links in the main post have been updated.

#5 Mavajo

Mavajo

    Glass Joe

  • Members
  • 2 posts

Posted 30 March 2009 - 06:57 PM

Looks like a pretty impressive spreadsheet. I took a quick run through with it, and assuming I did everything right, it estimated my DPS within ~50 DPS of my last Patchwerk report. I'll have to take a deeper look at it as time allows, but it looks sharp so far.

#6 Sarah

Sarah

    Glass Joe

  • Members
  • 21 posts

Posted 31 March 2009 - 07:37 AM

How do Sockets and Socket Bonuses work exactly?

I'm assuming the list of sockets corresponds with those listed for the gear, but how do I specify what gem to use in a socket? And for the Socket Bonuses, where is specified what the socket bonuses are? And how are the socket bonuses linked to the items that provide them?

#7 ieatpaperbag

ieatpaperbag

    Piston Honda

  • Members
  • 230 posts

Posted 31 March 2009 - 02:14 PM

How do Sockets and Socket Bonuses work exactly?

I'm assuming the list of sockets corresponds with those listed for the gear, but how do I specify what gem to use in a socket? And for the Socket Bonuses, where is specified what the socket bonuses are? And how are the socket bonuses linked to the items that provide them?


The sheet will automatically adjust the number of sockets not counting your meta, belt buckle, and blacksmithing sockets you may have. To specify what gem you have you enter the stats into the corresponding columns (similar to the way the gear is done above) so if I wanted to put in a Delicate Scarlet Ruby, I would just put in "16" in the Agility column next to socket (starting in row 135). The meta, belt buckle, and blacksmithing slots are in the 5 rows above.

For socket bonuses, you will have to keep track the number you have, and put them in similarly by entering stat bonus in the corresponding columns (starting in row 121).

For all of the above make sure you have a "1" in the Active column.

#8 Aorin

Aorin

    Glass Joe

  • Members
  • 11 posts

Posted 31 March 2009 - 02:47 PM

For socket bonuses, you will have to keep track the number you have, and put them in similarly by entering stat bonus in the corresponding columns (starting in row 121).

For all of the above make sure you have a "1" in the Active column.


Might I recommend doing with socket bonuses what Aldrianna has done on his sheet? Just add the bonus into the base gear piece stats. As it stands now, every socket bonus is worth grabbing ever since they eliminated the STA/Dodge/etc. bonuses that used to be on gear. So, it's currently always worth it to pick up the socket bonus on every piece of gear, because it is a net DPS increase over not matching the bonus.

#9 Baerbel

Baerbel

    Glass Joe

  • Members
  • 19 posts

Posted 31 March 2009 - 05:54 PM

So, it's currently always worth it to pick up the socket bonus on every piece of gear, because it is a net DPS increase over not matching the bonus.


This is not true, example:
You always have to check carefully at blue sockets if the socket bonus is worth it.

#10 Aorin

Aorin

    Glass Joe

  • Members
  • 11 posts

Posted 31 March 2009 - 06:28 PM

This is not true, example:
You always have to check carefully at blue sockets if the socket bonus is worth it.


Yes, I stand corrected. I was thinking of the when posting that and didn't think of all of the other items with blue sockets which are still not worth matching. So, would it make sense on the spreadsheet to build in the socket bonuses which are worth matching? Again, this is just a thought to help make the spreadsheet more user-friendly and to minimize the amount of manual calculations that need to be done.

#11 Mavanas

Mavanas

    Great Tiger

  • Members
  • 818 posts

Posted 31 March 2009 - 11:11 PM

I certainly do not think socket bonuses should be included with the gear stats because there are more than one example where matching the color of the socket to get the bonus is a DPS decrease. It is possible to implement a smart system which will know what socket bonuses are for each piece and will check the color of the gem you are putting in to decide if the bonus is active. I do think that the gear sheet in its current form is sufficiently user friendly for most users and therefore this improvement is not very high on my to do list.

#12 Burgah

Burgah

    Glass Joe

  • Members
  • 6 posts

Posted 03 April 2009 - 01:42 AM

Have you thought of the addition of armory profile loading? From the few spreadsheets I've used with an option like such it has really increased the user friendliness of the simulator/spreadsheet in general. Which would help someone who is not very comfortable or knowledgeable when it comes to using such tools.

#13 Stealthcat

Stealthcat

    Glass Joe

  • Members
  • 20 posts

Posted 03 April 2009 - 10:33 AM

Nice tool, although the simulator takes long when your swapping just 1-2 items for comparison :)

Sheet 3.1 DPS Strat Tab cell G3 says Sinister Strike costs 35 energy with the talent and without 4xT7. Is this to reflect the additional CP from the Glyph?

It goes to 33.25 with 4xT7.

#14 Manigra

Manigra

    Glass Joe

  • Members
  • 4 posts

Posted 03 April 2009 - 11:53 AM

Have you considered moving this out of the excel domain into something like Java or C++ to improve the performance?

I have been developing something similar in Java but focusing on getting the GUI side completed first before delving into the simulation code. My current simulation is running 200 iterations of a 6 minute fight in around 0.5 seconds although this is only simulating auto attacks with deadly poison and instant poison applied.

Currently I'm struggling more with the GUI though as I need to present the user with a nice way of defining a play style, you have giving me a bit of insipiration with your spreadsheet and how you define your DPS Strat.

You can take a look here at JRogueDPS if you are interested in the code or just to take a look at how the GUI is shaping up.

#15 Mavanas

Mavanas

    Great Tiger

  • Members
  • 818 posts

Posted 03 April 2009 - 12:52 PM

Sheet 3.1 DPS Strat Tab cell G3 says Sinister Strike costs 35 energy with the talent and without 4xT7.


It's a bug that I have fixed in 3.1f and 3.0.9b. It's supposed to be 40 energy with 2 points in Improved Sinister Strike. Thanks.

I did consider wowarmory import, and gonna figure out how to do it sometime. Obviously first I will make sure the content of the simulation does not have any bugs and the results are reliable before I am going to pretty up the interface.

As for Excel vs C++, it's a matter of preference. I prefer to work with Excel, and it is also a software of choice of many other theorycrafters. It's slower but it's also more visual, you can see the results of a single fight immeditely without even running the macros.

#16 Shaithis

Shaithis

    Von Kaiser

  • Members
  • 94 posts

Posted 03 April 2009 - 01:21 PM

By the way, I will point out that there is no real statistical value of ever going over 35 iterations.

#17 biberon

biberon

    Glass Joe

  • Members
  • 6 posts

Posted 03 April 2009 - 11:32 PM

While this spreadsheet is interesting to play with, I'm seeing considerable variability in terms of the output dps. Running the dps simulation 20 times I've observed the finished dps swing from 4500 to 5200. To eliminate potential user error, I downloaded the spreadsheet again under a different file name. Making no changes to default settings I ran 20 simulations and came up with a 600 dps spread; 5,333 for a high, 4,724 for a low.

Your sheet is no small undertaking and I certainly mean no disrespect, but the output variability is far greater than the dps contribution of any given piece of gear. I feel that to obtain consistent results the number of iterations would need to be very high.

#18 Genre

Genre

    Von Kaiser

  • Members
  • 44 posts

Posted 03 April 2009 - 11:58 PM

While this spreadsheet is interesting to play with, I'm seeing considerable variability in terms of the output dps. Running the dps simulation 20 times I've observed the finished dps swing from 4500 to 5200. To eliminate potential user error, I downloaded the spreadsheet again under a different file name. Making no changes to default settings I ran 20 simulations and came up with a 600 dps spread; 5,333 for a high, 4,724 for a low.

Your sheet is no small undertaking and I certainly mean no disrespect, but the output variability is far greater than the dps contribution of any given piece of gear. I feel that to obtain consistent results the number of iterations would need to be very high.


The number of iterations per run will without a doubt increase the stability of your results. Just so we are clear on what we are talking about. A simulation is composed of a series of iterations (realisations if you will); taking the average of these should be what a simulation returns. Obviously, if you are running with a very low iteration count, you will induce volatility and your values will be all over the place between 2 different simulations.

#19 sp00n

sp00n

    Bald Bull

  • Members
  • 1,836 posts

Posted 04 April 2009 - 12:00 AM

While this spreadsheet is interesting to play with, I'm seeing considerable variability in terms of the output dps. Running the dps simulation 20 times I've observed the finished dps swing from 4500 to 5200. To eliminate potential user error, I downloaded the spreadsheet again under a different file name. Making no changes to default settings I ran 20 simulations and came up with a 600 dps spread; 5,333 for a high, 4,724 for a low.

Your sheet is no small undertaking and I certainly mean no disrespect, but the output variability is far greater than the dps contribution of any given piece of gear. I feel that to obtain consistent results the number of iterations would need to be very high.


I assume this is why Malvanas stated that higher number of iterations = more precise results in the 'How to Use' section of the sheet?

Increasing number of iterations (max is 1000) will increase running time but can increase precision of the results. 300 iterations appeared practical for my previous calculations.


Of course, if you're seeing a 600 DPS spread while doing 20 simulations with 300 iterations each, that sounds like a real problem. If you're just running 1 simulation with 20 iterations though, this is an expected result. Just like in WoW itself, RNG > any gear upgrade in a relative small amount of swings (which honestly, 20x3minutes or whatever still is).

Stopped Playing


#20 biberon

biberon

    Glass Joe

  • Members
  • 6 posts

Posted 04 April 2009 - 12:08 AM

The number of iterations per run will without a doubt increase the stability of your results. Just so we are clear on what we are talking about. A simulation is composed of a series of iterations (realisations if you will); taking the average of these should be what a simulation returns. Obviously, if you are running with a very low iteration count, you will induce volatility and your values will be all over the place between 2 different simulations.


Agreed, however I did use the default of 300 iterations, so it was 20 runs @ 300. On second look I'm not so sure it's as simple as running this particular simulation enough times to find a solid mean, as I just bumped the iterations up to 2,000 and the first two runs produced 4,780 dps followed by 5,527. No further runs at 2000 iterations were made.

I have no mind for programming and only tinker with excel so I'm afraid I'm only as useful as reporting observations.

Edit: I didn't catch the max iterations being 1,000. Re-ran 10 runs @ 1,000 iterations:
No changes were made to the downloaded spreadsheet aside from setting iterations to 1000.

Max dps - 5411
Min dps - 4803

Individual results:
0) 5411
1) 5184
2) 4803
3) 5035
4) 5270
5) 5092
6) 4927
7) 4852
8) 5099
9) 5181




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users