Mavanas

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 works in GO Open Office.


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


The other tabs are used for calculation. 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. When comparing dps under different setups, you should make use of statistical significance to filter out cases when the differences you observe could be due to RNG as opposed to differences in the setups. Statistical p-value in Inputs tab is colored in grey when Baseline and Latest DPS are not statistically significantly different and is colored in bright yellow when they become different beyond what can be explained simply by RNG with 95% confidence.

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.

The latest version of the Simulation Spreadsheet:
3.2.2e - Simulation 3.2.2e - faster calculation time, no major changes
mirror - Download Rogue Simulation 3.2.2c.xls

 

Sarah

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?

 

Mavanas

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.

 

Mavanas

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.

 

Mavajo

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.

 

Sarah

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?

 

ieatpaperbag

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.

 

Aorin

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.

 

Baerbel

This is not true, example: [Favor of the Dragon Queen]
You always have to check carefully at blue sockets if the socket bonus is worth it.

 

Aorin

Yes, I stand corrected. I was thinking of the [Valorous Bonescythe Helmet] 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.

 

Mavanas

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.

 

Burgah

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.

 

Stealthcat

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.

 

Manigra

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.

 

Mavanas

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.

 

Shaithis

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

 

biberon

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.

 

Genre

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.

 

sp00n

I assume this is why Malvanas stated that higher number of iterations = more precise results in the 'How to Use' section of the sheet?
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).

 

biberon

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

 

Mavanas

Biberon-

I am not observing what you are reporting. The differences from run to run that you are reporting are not normal, so you need to explain to me which setup you are using. Even though I have ran the file several thousand times by now and I have not seen anything like that, I just ran it 3 more times out of curiosity and got three results within 10dps of each other which is totally normal.

Does anyone else see the kind of behavior Biberon is observing?

Also I disagree with Shaithis. More iterations decrease variance of the mean value, so it does help to run more, but as I said 300 has been a good enough number for my purpose. The variance of the mean value is proportional to 1/n where n is the number of iterations. At some n the variance is low enough so that further increases of n are not practical because they increase calculation time too much.

 

biberon

Edit: I carried my testing over onto the laptop which is running Excel 2002 and found much more consistent results (albeit at a snails pace). Turns out the spreadsheet doesn't play nicely with Excel 2003 in its release version, and fleshing it out with SP3 has cured the anomoly.

 

Manigra

Is it not possible in excel to provide a seed for the random number generator in excel?

For instance for iteration 1 you seed the rng with the value 1, for iteration 2 seed it with value 2 and so on. This would provide you with the same result each time you calculated your dps (that is until you changed a stat)

Mani

 

sp00n

The RAND function was changed in Excel 2003, and there was a bug in early versions which could result in negative numbers. Your observations may be related to that.

Description of the RAND function in Excel 2007 and in Excel 2003
The RAND function returns negative numbers in Excel 2003

 

Mavanas

No, excel does not do that. Also I now remember that at the very beginning of my programming of RNG in Excel, I observed those negative random numbers and found the same hotfix on Excel website. Once you apply it, your Excel should work perfectly fine. Thanks Sp00n.