MMO News and theorycrafting for advanced MMO gamers. News and articles that relate to your gameplay. World of Warcraft, SWTOR, Guild Wars 2, Rift, TERA, Eve Online, Star Wars the Old Republic, Diablo3, The Secret World and all Western AAA MMOs

Hello There, Guest! Register

 DPS Excel calculator for Sorcerer / Sage
01-11-2012, 10:20 PM (This post was last modified: 01-12-2012 01:17 AM by lostdummy.)
Post: #27
 lostdummy Respected Member Posts: 100 Joined: Nov 2011 Reputation: 1
RE: DPS Excel calculator for SI Sorcerer
(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 -----------------------```
 « Next Oldest | Next Newest »

 Messages In This Thread DPS Excel calculator for Sorcerer / Sage - lostdummy - 01-06-2012, 10:16 PM RE: DPS Excel calculator for SI Sorcerer - CaseyTheRetard - 01-07-2012, 04:35 AM RE: DPS Excel calculator for SI Sorcerer - Kaedis - 01-07-2012, 04:45 AM RE: DPS Excel calculator for SI Sorcerer - CaseyTheRetard - 01-07-2012, 05:42 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-07-2012, 08:53 AM RE: DPS Excel calculator for SI Sorcerer - CaseyTheRetard - 01-07-2012, 10:14 AM RE: DPS Excel calculator for SI Sorcerer - Kaedis - 01-07-2012, 10:51 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-07-2012, 09:21 PM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-08-2012, 02:50 AM RE: DPS Excel calculator for SI Sorcerer - Kaedis - 01-08-2012, 04:34 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-08-2012, 04:42 AM RE: DPS Excel calculator for SI Sorcerer - Kaedis - 01-08-2012, 05:20 AM RE: DPS Excel calculator for SI Sorcerer - Kaedis - 01-08-2012, 09:11 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-08-2012, 10:28 AM RE: DPS Excel calculator for SI Sorcerer - Kaedis - 01-08-2012, 10:30 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-08-2012, 11:00 AM RE: DPS Excel calculator for SI Sorcerer - Kaedis - 01-08-2012, 12:05 PM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-08-2012, 08:46 PM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-11-2012 10:20 PM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-08-2012, 09:43 PM RE: DPS Excel calculator for SI Sorcerer - Pestulan - 01-09-2012, 04:37 PM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-09-2012, 07:09 PM RE: DPS Excel calculator for SI Sorcerer - Lucai - 01-10-2012, 03:32 PM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-10-2012, 07:10 PM RE: DPS Excel calculator for SI Sorcerer - frmorrison - 01-10-2012, 04:09 PM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-10-2012, 07:35 PM RE: DPS Excel calculator for SI Sorcerer - Pestulan - 01-11-2012, 12:39 PM RE: DPS Excel calculator for SI Sorcerer - Erasmian - 01-12-2012, 05:27 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-12-2012, 08:31 AM RE: DPS Excel calculator for SI Sorcerer - Kaedis - 01-12-2012, 05:36 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-13-2012, 01:03 AM RE: DPS Excel calculator for SI Sorcerer - Saitta - 01-13-2012, 04:30 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-13-2012, 06:30 AM RE: DPS Excel calculator for SI Sorcerer - frmorrison - 01-13-2012, 07:05 AM RE: DPS Excel calculator for SI Sorcerer - Lucai - 01-13-2012, 07:21 AM RE: DPS Excel calculator for SI Sorcerer - CaseyTheRetard - 01-13-2012, 07:46 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-13-2012, 08:08 AM RE: DPS Excel calculator for SI Sorcerer - CaseyTheRetard - 01-13-2012, 08:47 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-13-2012, 09:18 AM RE: DPS Excel calculator for SI Sorcerer - CaseyTheRetard - 01-13-2012, 09:24 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-13-2012, 10:26 AM RE: DPS Excel calculator for SI Sorcerer - CaseyTheRetard - 01-14-2012, 12:51 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-14-2012, 01:36 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-14-2012, 09:36 PM RE: DPS Excel calculator for SI Sorcerer - Kaedis - 01-15-2012, 01:48 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-16-2012, 07:29 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-19-2012, 11:08 PM RE: DPS Excel calculator for SI Sorcerer - DPSFTW - 01-20-2012, 03:09 AM RE: DPS Excel calculator for SI Sorcerer - frmorrison - 01-20-2012, 06:07 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-20-2012, 11:31 PM RE: DPS Excel calculator for SI Sorcerer - CaseyTheRetard - 01-21-2012, 05:24 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-21-2012, 06:38 AM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-24-2012, 11:58 PM RE: DPS Excel calculator for SI Sorcerer - Caltiom - 01-25-2012, 06:06 PM RE: DPS Excel calculator for SI Sorcerer - lostdummy - 01-25-2012, 07:15 PM RE: DPS Excel calculator for Sorcerer / Sage - Kaedis - 01-26-2012, 01:29 AM RE: DPS Excel calculator for Sorcerer / Sage - Armageddus - 01-26-2012, 10:37 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 01-26-2012, 07:47 PM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 01-26-2012, 08:43 PM RE: DPS Excel calculator for Sorcerer / Sage - runlevelsix - 01-27-2012, 12:13 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 01-27-2012, 12:52 AM RE: DPS Excel calculator for Sorcerer / Sage - haldirgoth - 01-26-2012, 11:47 PM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 01-27-2012, 01:08 AM RE: DPS Excel calculator for Sorcerer / Sage - frmorrison - 01-27-2012, 05:55 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 01-27-2012, 07:16 PM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 01-31-2012, 03:33 AM RE: DPS Excel calculator for Sorcerer / Sage - Khadroth - 01-31-2012, 11:55 PM RE: DPS Excel calculator for Sorcerer / Sage - Armageddus - 01-27-2012, 10:49 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 01-28-2012, 04:37 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 01-30-2012, 11:41 PM RE: DPS Excel calculator for Sorcerer / Sage - frmorrison - 02-01-2012, 03:35 AM RE: DPS Excel calculator for Sorcerer / Sage - Khadroth - 02-01-2012, 03:49 AM RE: DPS Excel calculator for Sorcerer / Sage - zii - 02-02-2012, 08:06 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 02-02-2012, 09:13 AM RE: DPS Excel calculator for Sorcerer / Sage - zii - 02-02-2012, 12:47 PM RE: DPS Excel calculator for Sorcerer / Sage - frmorrison - 02-02-2012, 03:47 PM RE: DPS Excel calculator for Sorcerer / Sage - Bored - 02-02-2012, 11:01 PM RE: DPS Excel calculator for Sorcerer / Sage - zii - 02-03-2012, 04:18 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 02-03-2012, 05:00 AM RE: DPS Excel calculator for Sorcerer / Sage - Erasmian - 02-08-2012, 03:58 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 02-08-2012, 04:07 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 02-09-2012, 08:28 PM RE: DPS Excel calculator for Sorcerer / Sage - Paavo - 02-07-2012, 01:30 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 02-07-2012, 02:44 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 02-07-2012, 09:57 PM RE: DPS Excel calculator for Sorcerer / Sage - frmorrison - 02-07-2012, 02:45 AM RE: DPS Excel calculator for Sorcerer / Sage - Kaedis - 02-07-2012, 05:12 AM RE: DPS Excel calculator for Sorcerer / Sage - frmorrison - 02-08-2012, 03:18 AM RE: DPS Excel calculator for Sorcerer / Sage - Kaedis - 02-09-2012, 08:47 PM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 02-09-2012, 09:09 PM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 02-15-2012, 01:47 AM RE: DPS Excel calculator for Sorcerer / Sage - semirage - 02-16-2012, 08:26 PM RE: DPS Excel calculator for Sorcerer / Sage - alencooperr - 02-16-2012, 09:13 AM RE: DPS Excel calculator for Sorcerer / Sage - Kaedis - 02-16-2012, 09:10 PM RE: DPS Excel calculator for Sorcerer / Sage - semirage - 02-16-2012, 09:41 PM RE: DPS Excel calculator for Sorcerer / Sage - Lucai - 02-17-2012, 03:13 AM RE: DPS Excel calculator for Sorcerer / Sage - Armageddus - 02-17-2012, 10:03 PM RE: DPS Excel calculator for Sorcerer / Sage - lacerated - 02-17-2012, 11:50 PM RE: DPS Excel calculator for Sorcerer / Sage - tyfon - 02-18-2012, 08:18 AM RE: DPS Excel calculator for Sorcerer / Sage - frmorrison - 02-18-2012, 08:48 AM RE: DPS Excel calculator for Sorcerer / Sage - Kaedis - 02-18-2012, 08:28 AM RE: DPS Excel calculator for Sorcerer / Sage - kutmulc472 - 02-25-2012, 01:29 AM RE: DPS Excel calculator for Sorcerer / Sage - Kaedis - 02-25-2012, 07:46 AM RE: DPS Excel calculator for Sorcerer / Sage - odlaw - 03-12-2012, 01:44 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-19-2012, 11:27 PM RE: DPS Excel calculator for Sorcerer / Sage - sipylus - 03-14-2012, 12:51 PM RE: DPS Excel calculator for Sorcerer / Sage - Kaedis - 03-14-2012, 03:48 PM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-19-2012, 11:43 PM RE: DPS Excel calculator for Sorcerer / Sage - Kaedis - 03-20-2012, 12:08 AM RE: DPS Excel calculator for Sorcerer / Sage - frmorrison - 03-20-2012, 01:25 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-20-2012, 04:12 AM RE: DPS Excel calculator for Sorcerer / Sage - Kaedis - 03-20-2012, 02:23 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-20-2012, 06:32 AM RE: DPS Excel calculator for Sorcerer / Sage - TheDarkness - 03-20-2012, 12:46 PM RE: DPS Excel calculator for Sorcerer / Sage - Kaedis - 03-20-2012, 04:36 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-20-2012, 04:40 AM RE: DPS Excel calculator for Sorcerer / Sage - Kaedis - 03-20-2012, 04:48 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-20-2012, 05:22 AM RE: DPS Excel calculator for Sorcerer / Sage - Kaedis - 03-20-2012, 06:44 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-20-2012, 07:13 PM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-21-2012, 01:50 AM RE: DPS Excel calculator for Sorcerer / Sage - sipylus - 03-20-2012, 07:51 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-20-2012, 06:45 PM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-21-2012, 05:10 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-21-2012, 05:31 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-21-2012, 06:10 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-22-2012, 12:12 AM RE: DPS Excel calculator for Sorcerer / Sage - Caltiom - 03-22-2012, 12:42 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-22-2012, 01:08 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-22-2012, 01:26 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-22-2012, 01:35 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-22-2012, 01:12 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-22-2012, 01:49 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-22-2012, 02:04 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-22-2012, 03:17 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-22-2012, 04:13 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-22-2012, 04:58 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-22-2012, 06:12 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-22-2012, 06:56 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-22-2012, 07:14 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-22-2012, 07:56 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-22-2012, 08:33 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-22-2012, 07:20 PM RE: DPS Excel calculator for Sorcerer / Sage - Caltiom - 03-22-2012, 08:35 PM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-22-2012, 09:21 PM RE: DPS Excel calculator for Sorcerer / Sage - Caltiom - 03-22-2012, 10:40 PM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-23-2012, 12:24 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-23-2012, 01:14 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-23-2012, 02:11 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-23-2012, 04:37 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-23-2012, 06:14 AM RE: DPS Excel calculator for Sorcerer / Sage - CaseyTheRetard - 03-23-2012, 07:20 AM RE: DPS Excel calculator for Sorcerer / Sage - lostdummy - 03-23-2012, 06:58 PM RE: DPS Excel calculator for Sorcerer / Sage - Torinth - 07-11-2012, 05:22 AM RE: DPS Excel calculator for Sorcerer / Sage - Atreas - 03-13-2013, 12:27 AM

Forum Jump:

User(s) browsing this thread: 1 Guest(s)