DPS Excel calculator for Sorcerer / Sage

[lostdummy]

(01-08-2012 12:05 PM)Kor Wrote:  That could simply be deviation in the randomness. Theoretically, any multiple of 30 seconds should have roughly the same values, with longer fights coming closer to the actual value (since we're starting at 0 stacks).

I revisited Conduction average stack calculation, since I found how to calculate values without simulation, meaning results are exact accurate numbers.

It is based on my initial idea of recursive calculation, which in simplified pseudocode would look like:

Code:

double SpellsPerBuff, SpellsPerFight;  //globals, to simplify code
// recursive proc
double avgStack( int SpellsRemain, SpellsNoProc, Stack){
   if (SpellsRemain<=0) return 0;
   newStackProc = min(Stack+1,3);
   ResProc=  newStackProc/SpellsPerFight  + avgStack( SpellsRemain-1 , 1, newStackProc);
   newStackNoProc = ( SpellsNoProc < SpellsPerBuff ) ? Stack:0 ;
   ResNoProc=  newStackNoProc/SpellsPerFight  +avgStack( SpellsRemain-1, SpellsNoProc +1, newStackNoProc);
   return 0.3* ResProc + 0.7*ResNoProc;
}

// average stack if LS casted every 10sec in 300sec fight:
SpellsPerBuff=30/10; // buff duration 30sec divided by spell cast every 10sec
SpellsPerFight=300/30*SpellsPerBuff; // number of spells used over whole fight
averageStack = avgStack( SpellsPerFight, 0, 0);

Above is fairly simple, although actual code gets bit more complicated when you want to support buff and fight durations not in exact multiples of spell recast time (also my actual code is pascal, I just rewrote this as simplified pseudocode, so maybe I introduced some error, but it shows general idea). Important thing is, it results in exact result for given input values of spell frequency and fight duration, ie based on probability math rather than simulation.

Reason why this was only my 'initial idea' is fact that used like this it would be too slow to calculate for any longer fight - it double number of function calls every step, meaning total number of function calls is around 2^steps. And it has 'SpellsPerFight' steps, so in case of cast every 1.5 sec over 300sec fight, that is 2^200 steps (~10^60), making it uncalculable. Even smaller cases (cast every 10sec over 300sec) is over billion steps.

But now I figured how to optimize/speed that up, and good news is that our simulation numbers are correct ;p This also confirms that numbers are slightly different for different fight duration, but basically numbers that I posted for 180sec, 300sec and 450sec simulation fights are correct on 2nd decimal.

I had idea to write this function in VBA code and call that instead of using interpolation, but I don't know how to set initial global values in excel VBA (ie before calculation iterations start), so my optimization would not be doable - and that would mean unusably slow VBA function. Not to mention that including VBA code result in that security 'Enable macro' question every time you open document.

So, instead of adding VBA function, I extended interpolation to be "two dimensional" interpolation, ie to cover different fight durations, as well as already covered different LS frequencies (in version 1.35).

BTW, example of calculated numbers:

Code:

Cst     Cst/      Avg     @ 300s
every     buff   stacks
-----------------------
01.5    20.00    2.910
03.0    10.00    2.662
04.5    06.67    2.267
06.0    05.00    1.827
07.5    04.00    1.494
09.0    03.33    1.352
10.0    03.00    1.101
15.0    02.00    0.682
20.0    01.50    0.540
25.0    01.20    0.457
30.0    01.00    0.300
-----------------------