DPS Excel calculator for Sorcerer / Sage

[lostdummy]

This is excel DPS calculator that was made based on info available on this and other sites.
It can be used both by Empire side Sorcerers and Republic side Sages.

(Ver 1.51 - 21.03.2012)

  swtor_sorc_151.xlsx (Size: 167.14 KB / Downloads: 1512)

There is explanation inside excel on how to use it, but here is some short summary of what it was intended for:

- calculate DPS of sorcerer on boss fight, for any selected build (madness, lightning, hybrid, custom)
- compare DPS of different builds, and find which skill talents are best for different builds
- compare DPS between different gear sets (Current vs BiS for example)
- find relative values of stats ( to answer "what is better, 5 will or 10 crit" ) - for each build
- quick item value comparison calculator , using above stat values
- find value of different skills/talents on DPS (for each build)
- automatically select spells to use in rotation for selected build (based on skill availability and DPCT) - but can be finetuned by user
- also can calculate stat value for sorcerer companion tank (to maximize survivability, not DPS- useful to see if defense is better than endurance etc )
- can select Empire/Republic names for talents, spells, companions

All above DPS and all other calculation can be based on actual player stats (that can be entered on excel, usually in 'Current' gear set), and player defined builds (existing presets in exel can be changed, or just change 'Custom' preset).


Tested to work on Excel 2007 and later. Circular references are used in excel, and they are enabled in document by default at: Options -> Formulas -> Calculation Options -> Enable iterative calculations

[CaseyTheRetard]

I think the sheet greatly underestimates the frequency of LS casts necessary to maintain Conduction. Conduction is a side effect of Forked Lightning, which is a 30% proc from Lightning Strike. The sheet assumes that 1 LS cast every 10 seconds is enough to keep Conduction up 100% of the time, when math tells us it will take closer to 3 casts every 10 seconds on average.

Also, I see you don't apply the cost reduction from Lightning Induction to Affliction, Crushing Darkness (which has some Lightning Effusion weirdness in its cost calc), and Creeping Terror. Is that the result of testing, or just an omission?

[Kaedis]

Quote:Also, I see you don't apply the cost reduction from Lightning Induction to Affliction, Crushing Darkness (which has some Lightning Effusion weirdness in its cost calc), and Creeping Terror. Is that the result of testing, or just an omission?

Affliction, CT, and CD do not benefit from Electric Induction for some unknown reason.

[CaseyTheRetard]

I changed the sheet to use the standard Sorceror stats from Kor's post here and added a line into the rotation to model upgrading FL to Wrathed CL for the 0/13/28 build (download here). With those stats, 0/13/28 models about 3% higher DPS than Lightning.

Notably, 0/13/28 models no DPS advantage over 1/12/28. The differential damage from upgrading 1.5 seconds of FL to wrathed CL is fairly low - about 270 damage - and the differential force cost so high - 44.4 force - that CL just can't be used often enough to make an impact. Unless the simulator shows some unforeseen stochastic effects I think the future is not bright for the Chain Lightning build.

Also lostdummy, the reason your Madness build runs out of force with DF in the rotation is because you have 2 points in Convection (3/7/31) instead of in Reserves (3/7/31). The latter build is not force-limited.

Kudos for making an incredible tool, by the way.

[lostdummy]

(01-07-2012 05:42 AM)CaseyTheRetard Wrote:  I changed the sheet to use the standard Sorceror stats from Kor's post here and added a line into the rotation to model upgrading FL to Wrathed CL for the 0/13/28 build (download here). With those stats, 0/13/28 models about 3% higher DPS than Lightning.

Notably, 0/13/28 models no DPS advantage over 1/12/28. The differential damage from upgrading 1.5 seconds of FL to wrathed CL is fairly low - about 270 damage - and the differential force cost so high - 44.4 force - that CL just can't be used often enough to make an impact. Unless the simulator shows some unforeseen stochastic effects I think the future is not bright for the Chain Lightning build.

Also lostdummy, the reason your Madness build runs out of force with DF in the rotation is because you have 2 points in Convection (3/7/31) instead of in Reserves (3/7/31). The latter build is not force-limited.

Kudos for making an incredible tool, by the way.

Good point about using Wrath for CL - I only used Wrath for CD and TB if present.

But CL in general seems to be very force problematic - as you noticed. Even when used with just LightningStorms, and now I added to my excel that same line for Cl that was used for LightningStorms is now used for Wrath CLs also.

But in your modified excel you forgot to update 'used time' for those 9 Cls that you set there - with those it will even more reduce DPS, ie even more confirm that CL is to force intensive to really increase DPS.

Also returned DeathField to be used with Madness, so if it happen to hog mana it can always be finetuned.

Also, I noticed you increased your willpower and crit for 5% both - I assume that is accounting for player buffs? If so, that is something that I also did not think to add (assumed it will show already included in 'willpower' on info screen), but now added just in case 'buff%' fields on character screen.

BTW, as you mentioned 'unforeseen stohastic effects', this excel calculator is especialy sensitive to CL in that regard - since number of possibly used CLs reduce available force, which reduce number of spells that can trigger insta CL (like FL and LS), and that can have flip-flop effect. But I tried to use some limiting formulas to force convergence in such cases ;p

I updated original post with updated version for:
- used also those player stats from Kor's post as default
- added Wrath to CL use
- re-allowed DF on madness builds

---

(01-07-2012 04:35 AM)CaseyTheRetard Wrote:  I think the sheet greatly underestimates the frequency of LS casts necessary to maintain Conduction. Conduction is a side effect of Forked Lightning, which is a 30% proc from Lightning Strike. The sheet assumes that 1 LS cast every 10 seconds is enough to keep Conduction up 100% of the time, when math tells us it will take closer to 3 casts every 10 seconds on average.

Also, I see you don't apply the cost reduction from Lightning Induction to Affliction, Crushing Darkness (which has some Lightning Effusion weirdness in its cost calc), and Creeping Terror. Is that the result of testing, or just an omission?

Yes, I found on forum posts that Induction does not affect those spells, and also tested for those I have.

As for Conduction, can you post formula or reasoning behind "3 every 10sec", so I can update if needed?

I assumed that once you set max number in stack (3), getting even one Conduction every 30sec would be enough to refresh stack at 3. And to get 1 conduction in 30sec, I assumed 1 LS in 10sec would be enough.

Was that correct assumption, and if not, what part was wrong?

[CaseyTheRetard]

(01-07-2012 08:53 AM)lostdummy Wrote:  But in your modified excel you forgot to update 'used time' for those 9 Cls that you set there - with those it will even more reduce DPS, ie even more confirm that CL is to force intensive to really increase DPS.

BTW, as you mentioned 'unforeseen stohastic effects', this excel calculator is especialy sensitive to CL in that regard - since number of possibly used CLs reduce available force, which reduce number of spells that can trigger insta CL (like FL and LS), and that can have flip-flop effect. But I tried to use some limiting formulas to force convergence in such cases ;p

As for Conduction, can you post formula or reasoning behind "3 every 10sec", so I can update if needed?

I assumed that once you set max number in stack (3), getting even one Conduction every 30sec would be enough to refresh stack at 3. And to get 1 conduction in 30sec, I assumed 1 LS in 10sec would be enough.

My method is a bit of a hack: I put the line below FL, so that it could consume as nearly as possible the excess force leftover after FL filler. It doesn't consume time because it's replacing FL cast time with CL cast time - hence the damage and force costs being computed as differential to 1.5 seconds of FL.

I simply assume that Wrath is present every time CL is used. A proc chance of 30% per FL tick results in (1 - (1 - 30%)^4) = 76% chance of Wrath proc per full FL channel. Any fight of reasonable length is going to be way more mana limited than Wrath limited as far as CL is concerned. For the 5 minute fight, 104 FL casts will average around 80 Wrath procs of which 29 are used.

"3 every 10 seconds" was me conflating the 10 second duration of Subversion with the 30 second duration of Conduction. It takes (1 / proc%) casts per buff_duration on average to maintain a buff from a proc, so the average interval between casts must be the reciprocal of that: proc% * duration = 30%*30 = 9 seconds instead of your 10. So a bit more LS, not a ton more LS.

[Kaedis]

Quote:"3 every 10 seconds" was me conflating the 10 second duration of Subversion with the 30 second duration of Conduction. It takes (1 / proc%) casts per buff_duration on average to maintain a buff from a proc, so the average interval between casts must be the reciprocal of that: proc% * duration = 30%*30 = 9 seconds instead of your 10. So a bit more LS, not a ton more LS.

Unfortunately, 1 every 9 seconds would only result in a 70% uptime on the buff (average stacks would take a slightly more complex calculation, too tired atm). If we're aiming for 99% uptime, we'd require a cast every 2.32 seconds.

In other words, at only a 30% proc chance, there's no way to really reliably maintain the buff. The better bet would be to ignore the buff (in terms of your ability priority) and treat it instead as a perk, using Lightning Strike simply as a filler (plus enough to keep Subversion active).

[lostdummy]

(01-07-2012 10:51 AM)Kor Wrote:  

Quote:"3 every 10 seconds" was me conflating the 10 second duration of Subversion with the 30 second duration of Conduction. It takes (1 / proc%) casts per buff_duration on average to maintain a buff from a proc, so the average interval between casts must be the reciprocal of that: proc% * duration = 30%*30 = 9 seconds instead of your 10. So a bit more LS, not a ton more LS.

Unfortunately, 1 every 9 seconds would only result in a 70% uptime on the buff (average stacks would take a slightly more complex calculation, too tired atm). If we're aiming for 99% uptime, we'd require a cast every 2.32 seconds.

In other words, at only a 30% proc chance, there's no way to really reliably maintain the buff. The better bet would be to ignore the buff (in terms of your ability priority) and treat it instead as a perk, using Lightning Strike simply as a filler (plus enough to keep Subversion active).

You are correct there Kor about that 70% chance with 1 LS every 9-10sec. It seems to be quite complicate to calculate actual average stack ...since that 70% is not actually uptime but merely chance to refresh buff.

Your suggestion to avoid explicit casting of LS to achieve Conduction seems good in light of above issue, but I have same problem there with how to calculate:

When we know number of LS used during fight, how to calculate average stack coverage ?

I tried to get up with some easy formula, but its not so easy - unless we cast one or less LS per 30sec , when it become simple.

If any of you can come up with some formula that can answer above, even if it is close approximate, it would help ;p

In meantime I will make small simulation to get few sample data points (with different number of used LS , but also different fight duration) , and will use those with some form of interpolation - I assume any difference between actual and interpolated values would be small enough.

[lostdummy]

Updated few versions to 1.28, notes from changelog:

Quote:1.27 07.01.2012
removed LS from rotation casted specifically to keep Conduction up (since it needs much more than 1 per 10sec)
Conduction effect now calculated based on number of LS actually used , and interpolated from data table ( data still approximate, should be updated)
changed in rotation that ChainLightning is now used with Wrath in addition to Lightning Storm (and still CL is force limited and oscilate results)
changed default Balanced build to one with bit more DPS (use few more Madness talents instead of LightEffusion, assume not force limited)
added CPCT (coeficient per cast time) column on 'Spells' tab (J) : show how well spell scale with willpower (for example, CD scale twice better than LS )

1.28 07.01.2012
Increased chance for LS to be selected as filler : now compare DPCT of FL with "improved" DPCT fo LS (ie increased by presumed LightningStorm+Cl effect, and presumed Conduction buff)
can still force FL as filler in finetuning (if regular FL DPCT > regular LS DPCT), if you put anything <>1 in FL "USE" . For example, 0.99 will use FLs

1.29 07.01.2012
force limited CLs for Balanced scenarios also (ie when FL is used with CL)
in Madness default build used Reserves instead of Convection, result in less force limited case
Shock as filler vs LS now also compare its DPCt to "improved" LS DPCT

Set few default build point improvements from Casey suggestions.

Improved chance for LS to be selected as filler over FL (or Shock). So far it compared pure DPCT, and even in lightning build, LS had lower DPCT than FL ... and by consulting new column that I added (CPCT) it would only get worse with future improved gears ... ie willpower coeficient per cast time is better for FL than LS.

BUT fact is that LS has some sideeffects if used that improve overall DPS if not its own: it enable more CLs when Wrath is not present (ie when LightningStorm is only one to give instants), and it improve Conduction bonus (even at 2%, it would be bonus to ALL spells not only LS)

So I calculated "improved" LS DPCT taking those two into consideration, and based filler decision on that value - and now in Lightning builds it chose LS over FL 9ie no FL used at all), and it shows small DPS increase.

BTW, Lightning build is very interesting in that many choices for spells are both very close in DPS to each other (FL vs LS, CL or not CL all give similar DPS), and many of those choices involving CL are close to force limits.

[Kaedis]

I modified my old Harnessed Darkness simulator to check on Conduction stacks, and it apparently is a very chaotic system. The program itself lets you specify a range of casting intervals, plus a step interval along that range (range must be evenly divisible by the step interval). It will output the results to a file for easy access.

I ran the simulation at 100000 iterations per step, from a cast interval of 1.5 seconds (spammed) to 9 seconds, and I ran that same simulation 5 times, averaging the results. Here are the results for average number of stacks at each interval:

Code:

intv   average      iterations----------------------------------------------
1.5    2.7732160    2.883590    2.564990    2.878130    2.780830    2.758540
3.0    2.2315580    2.524880    2.338520    2.256720    1.808420    2.229250
4.5    1.8453120    1.622170    2.570910    1.772710    1.593010    1.667760
6.0    1.8588320    2.025820    1.858880    2.138890    1.438090    1.832480
7.5    1.0137358    1.133050    1.182150    1.187140    0.791657    0.774682
9.0    0.9493062    0.972273    1.283470    0.846955    0.888158    0.755675

Here's the code for the simulator:

Code:

// PROVIDED BY: Kor
// CONTACT E-MAIL: [redacted]
// FILE: conduction_simulator.cxx
// VERSION: 1.0.2.r
// PURPOSE: Simulate the SI Sorc talent Conduction for the MMO SW:TOR

// DIRECTIVES -----------------------------------------------------------------
#include <cstdlib>
#include <iostream>
#include <fstream>
#include <cstring>
#include <cmath>
#include <cassert>
#include <ctime>
using namespace std;
// ----------------------------------------------------------------------------

// GLOBAL CONSTANTS -----------------------------------------------------------
const size_t MINLENGTH = 180;    // Minimum iteration length in seconds
const size_t MAXLENGTH = 300;    // Maximum iteration length in seconds
const double INTERVAL = 0.5;    // Simulation step interval
const double CASTTIME = 1.5;    // Cast time of spell

const size_t MAXSTACKS = 3;        // Maximum stack size
const double CHANCE = 0.3;        // Chance of proc
const double DURATION = 30;        // Duration of proc
// ----------------------------------------------------------------------------

// MODELS ---------------------------------------------------------------------
// Holds all information for a single simulation iteration.  Also used for
// minimum, maximum, and average
struct iteration
{
    double duration;                // Duration in seconds of the iteration

    double stacks[MAXSTACKS + 1];    // Duration in seconds at each stack count
};
// ----------------------------------------------------------------------------

// FUNCTION PROTOTYPES --------------------------------------------------------
iteration sim(size_t duration, double interval);
// ----------------------------------------------------------------------------

// ============================================================================
int main()
{
    ofstream file;
    
    // Clear file before writing to it
    file.open ("conduction_sim_output.txt");
    file.close();
    
    file.open ("conduction_sim_output.txt", ios::out | ios::app);
    size_t numits;
    double minspread;
    double maxspread;
    double interval;
    size_t duration;
    double avg = 0;
    
    // Request number of iterations
    cout << "Number of iterations? ";
    cin >> numits;
    
    if(numits <= 0)
    {
        cout << "Can't have zero iterations!" << endl;
        exit(0);
    }
        
    // Request minspread
    cout << "Minimum spread in seconds? ";
    cin >> minspread;
    
    if(minspread < CASTTIME)
    {
        cout << "Minimum spread too small!" << endl;
        exit(0);
    }
    
    // Request maxspread
    cout << "Maximum spread in seconds? ";
    cin >> maxspread;
    
    if(maxspread >= DURATION)
    {
        cout << "Max spread too large!" << endl;
        exit(0);
    }
    
    // Request maxspread
    cout << "Spread interval? ";
    cin >> interval;

    if(interval > 0 && (maxspread-minspread)/interval != floor((maxspread-minspread)/interval))
    {
        cout << "Interval not even divisor of spread range." << endl;
        exit(0);
    }
    
    size_t steps = int((maxspread-minspread)/interval + 1);
    
    iteration average[steps], min[steps], max[steps], its;
    
    // Repeat test as necessary for interval
    for(size_t j = 0; j < steps; ++j)
    {
        // Run simulation specified number of times
        for(size_t c = 0; c < numits; c++)
        {
            // Generate a random duration between the minimum and maximum (inclusive)
            duration = MINLENGTH + (rand()%(MAXLENGTH - MINLENGTH + 1));
            
            its = sim(duration, minspread + j*interval);
            
            if(!c)
            { // Initialize on first run
                min[j].duration = its.duration;
                max[j].duration = its.duration;
                average[j].duration = its.duration;

                for(size_t k = 0; k <= MAXSTACKS; ++k)
                {
                    min[j].stacks[k] = its.stacks[k];
                    max[j].stacks[k] = its.stacks[k];
                    average[j].stacks[k] = its.stacks[k];
                }
            }
            else
            { // Track Min, Max, and Average
                if(its.duration < min[j].duration)
                    min[j].duration = its.duration;
    
                if(its.duration > max[j].duration)
                    max[j].duration = its.duration;

                average[j].duration = ((average[j].duration * c) + its.duration)/(c + 1);

                for(size_t k = 0; k <= MAXSTACKS; ++k)
                {
                    if(its.stacks[k] < min[j].stacks[k])
                        min[j].stacks[k] = its.stacks[k];

                    if(its.stacks[k] < max[j].stacks[k])
                        max[j].stacks[k] = its.stacks[k];

                    average[j].stacks[k] = ((average[j].stacks[k] * c) + its.stacks[k])/(c + 1);
                }
            }
        }
    }
    

    // Output to file
    cout << "Simulation complete, check conduction_sim_output.txt for results" << endl;

    file << "Cast interval range: " << minspread << " - " << maxspread << "(stepsize " << interval << ")" << endl;
    file << "Iterations per interval: " << numits << endl;
    file << "Results format: MINIMUM | AVERAGE | MAXIMUM" << endl;
    for(size_t c = 0; c < steps; ++c)
    {
        file << "--------------------------------------" << endl;
        file << "Cast Interval:   " << minspread + interval*c << endl;
        file << "Duration:        " << min[c].duration << " | " << average[c].duration << " | " << max[c].duration << endl;

        avg = 0;
        for(size_t k = 0; k <= MAXSTACKS; ++k)
        {
            file << "Sec at " << k << " Stacks: "  << min[c].stacks[k] << " | " << average[c].stacks[k] << " | " << max[c].stacks[k] << endl;
            avg += average[c].stacks[k]*k;
        }
        file << "Average stacks: " << avg/average[c].duration << endl;
    }
    
    
    file.close();
    
    return 0;
}
// ============================================================================

// ----------------------------------------------------------------------------
iteration sim(size_t duration, double interval)
{
    iteration it;
    size_t stacks = 0;
    double stackdur = 0;
    bool cast = false;
    
    it.duration = 0;
    for(size_t k = 0; k <= MAXSTACKS; ++k)
        it.stacks[k] = 0;
    
    // Seed random
    srand(time(NULL));
    
    while(it.duration < duration)
    {
        cast = ((rand()%32768)/32767.0 < (INTERVAL/interval));
        
        it.duration += (cast ? CASTTIME : INTERVAL);    
        
        if(stackdur > (cast ? CASTTIME : INTERVAL))
        {
            it.stacks[stacks] += (cast ? CASTTIME : INTERVAL);
            stackdur -= (cast ? CASTTIME : INTERVAL);
        }
        else
        {
            it.stacks[stacks] += stackdur;
            it.stacks[0] += (cast ? CASTTIME : INTERVAL) - stackdur;
            stackdur = 0;
            stacks = 0;
        }
        
        if(cast && (rand()%32768)/32767.0 < CHANCE)
        {
            stackdur = DURATION;
            if(stacks < MAXSTACKS)
                stacks++;
        }
    }
        
    return it;
}
// ----------------------------------------------------------------------------