Jump to content


Photo

Warlock Spreadsheet (3.3.5)


  • This topic is locked This topic is locked
186 replies to this topic

#21 Mupki

Mupki

    Glass Joe

  • Members
  • 15 posts

Posted 20 January 2010 - 07:45 PM

There are still things I need to fix that are high on my priority list. Nibelung procs, Reign of the Dead, and some other trinket procs. I also need to include Black Magic for those that want to see the differences. TNS also doesn't scale pets yet.

If you have any formulas that would help me with these things, it would be much appreciated.

#22 Hamlet

Hamlet

    Mike Tyson

  • • Guide Author
  • 11579 posts

Posted 20 January 2010 - 08:36 PM

No need to split out the binomial in step 1. Mean procs per Corruption cast is 6*0.12 = 0.72.

(Also, your sum is wrong--you need to include the binomial coefficent 6Cn. And multiply by the number of procs n in each case. The correct sum is:
\sum_{n=0}^6{n\binom{6}{n}0.12^n*0.88^{6-n}}, which equals 0.72)

#23 Hamlet

Hamlet

    Mike Tyson

  • • Guide Author
  • 11579 posts

Posted 20 January 2010 - 10:07 PM

Anyway, on to the problem from the OP. Best I can tell (I don't really know anything about Warlocks), Incinerate cast time before haste is 2.5/1.3 = 1.92s. Corruption tick time is 3s before haste. Both decrease equally with haste, so that ratio shouldn't change.

Simplified outlook to start: all cast time is spent on either Shadow Bolt or Incinerate.

At any given moment, Molten Core will be up if it has procced within the amount of time it takes to cast 3 Incinerates. Given a bit of reaction time, etc., let's say that there always 2 Corruption ticks in the time it takes to chaincast 3 Incinerates. In other words, if at time T, one of your last two Corruption ticks has procced MC, you'll still be casting Incinerate. Otherwise, you'll be casting SB (since two Corruption ticks is the time it takes to use up your 3 MC charges).

One of your two Corruption ticks will have procced MC with probability (1-0.88^2)= 22.6%. So a good first-order estimate is that you'll be casting Incinerate 22.6% of the time, and Shadow Bolt the rest of the time. Curiously, this is constant with respect to haste, which may be counterintuitive at first but makes sense when you think about it.

Note that 22.6 is very close to Mahida's above empirical estimate of 27%*82%.

-------

The big wrench in the works is all the execution time spent on things other then Incinerate or SB (including Corruption itself). I think the best way to deal with this is to average in the cast time spent on anything other than nukes in the form of added Corruption ticks (since, from the perspective of the Immolate/SB question, the time spent casting other stuff only matters insofar as it provides extra time during which Corruption may have ticked). Essentially if you spend a fraction X of your time casting things other than SB/Incinerate, perform the same computation I did above, but imagine that Corruption ticks 1/(1-X) times more quickly.

So, above, I assumed that the ratio of Incinerate cast time to Corruption tick time (call this R) is 2/3. Here let's start with the real value of R=C/3 (where C is the pre-haste cast time of a buffed Incinerate, 25/13). And, using the approximation from the last paragraph, we'll instead model the ratio as R=C/(3*(1-X)).

Now basically repeat what I did in the top half of the post. Since Corruption will tick R times during one Incinerate cast, it will tick 3R times during 3 Incinerate casts. So, at any time, you'll find yourself casting Incinerate instead of SB if any of the last 3R Corruption ticks have procced MC. This leads to a time spent casting Incinerate (as a fraction of total time spent casting nukes) of:

1-0.88^{3R}, or

1-0.88^{\frac{C}{(1-X)}}, where C = 25/13 (pre-haste cast time of Incinerate).

#24 Vidiz

Vidiz

    Glass Joe

  • Members
  • 14 posts

Posted 21 January 2010 - 12:07 AM

Hey there - first of all may I say very well done and I comment you for your hard work and effort!

However, when I try loading my character from the armory into the spreadsheet it says:

" 48667 is not a supported Back please use Custom Gear for it "

Why is this error message being displayed (maybe I just don't understand what it's asking me to do) and how do I fix it?

Many thanks
Vidiz

#25 Mupki

Mupki

    Glass Joe

  • Members
  • 15 posts

Posted 21 January 2010 - 12:17 AM

It means that the item isn't on the spreadsheet, it loads items based on their ID. I'm still working on adding every item I can for the next update, but it takes awhile. There are a lot of alliance items that aren't in there from ToC and probably some others I have missed.

#26 Hamlet

Hamlet

    Mike Tyson

  • • Guide Author
  • 11579 posts

Posted 21 January 2010 - 12:30 AM

Nibelung procs


Base damage range for normal Nibelung is 1591-1785. It's affected by level-based partial resists, and by caster debuffs on the target (Earth and Moon, Imp. Scorch, Totem of Wrath). 50% crit bonus, 0% base crit rate, 0% miss. Casts on average 17 Smites per proc if it stays still. 1% proc rate, but a bunch of weird talents and things proc it; you'll have to experiment some yourself.

#27 Guest_Acabar_*

Guest_Acabar_*
  • Guests

Posted 21 January 2010 - 07:04 AM

I get the same error for a couple of my armor pieces.

My main concern is I'm getting a ton of #N/A errors in cells where there's supposed to be DPS/Damage numbers. Anyone else having this issue?

Here's a picture of the spreadsheet after I import my armory to avoid confusion:
Posted Image

#28 Cerula

Cerula

    Von Kaiser

  • Allied Members
  • 42 posts

Posted 21 January 2010 - 07:51 AM

You're getting those errors because you have 2 items that aren't in the spreadsheet's database. Also ran into a bunch of bugs with loading a character...Ill try and fix all this by tomorrow.

#29 Vidiz

Vidiz

    Glass Joe

  • Members
  • 14 posts

Posted 21 January 2010 - 08:15 AM

I understand how it could be hard to add every single item. For me, the item in question was the Shawl of the Devour Crusader - the 258 cloak from ToC 10 HC Insanity.

#30 Guest_Acabar_*

Guest_Acabar_*
  • Guests

Posted 21 January 2010 - 04:55 PM

You're getting those errors because you have 2 items that aren't in the spreadsheet's database. Also ran into a bunch of bugs with loading a character...Ill try and fix all this by tomorrow.


That's what I figured - just wanted to report in case it was something else.

The two items for me were: Bindings of Dark Essence - wrists from ToC 25 and the Icehowl Cinch from ToGC 10.

#31 Dalmasca

Dalmasca

    Glass Joe

  • Members
  • 20 posts

Posted 21 January 2010 - 08:32 PM

Since I was in the process of updating my own copy of the sheet the other day, I'll share with you some of my thoughts.

Firstly, I'd like to compliment you on the great changes visually and in terms of organization. They were long overdue in my opinion.

There are a few things I need some clarification on though just so I can give proper feedback on if the models are functioning correctly:

1) In the T10 4pc bonus, where does the ^3.3 come from in the calculation? Is this somehow representing the proc chance on the 5 UA or Immo ticks without detailing the math behind it?

As I understand it, this would assume the proc downtime after 3.3 ticks/chances, which is obviously won't always be the case:

Chance to not proc = .85

Therefore, taking an excerpt from the formula (1-(1-.15)^3.3), after 3.3 chances to proc:
.85^3.3 = .58 chance to have not procced still. ** I might add that the "1-.15" is a bit superfluous when you can do that one in your head, lol**

I'm just confused about this assumption. In a case of bad RNG, you could theoretically go an entire fight without the proc occuring- it's just very unlikely; specifically it's y = .85^x with y approaching 0 as X approaches infinity. In other words, the chance of such a bad RNG string happening go exponentially down closer to zero but never reaches it (a limit function).

2) For the Dislodged Foreign Object proc, what is the 2.5 multiplier at the end representing? As far as I can tell, nothing in the formula accounts for the proc chance (10%) unless that does somehow. Using the Sundial of the Exile- a trinket already in the spreadsheet with a similar proc- as an example, shouldn't it look something like this instead?:

=577.5*(20/(45+Models!$B$27/.10))

I suppose you could also include the IfErrorWDS() function like so:

=577.5*(20/(45+IfErrorWDS(Models!$B$27,0)/.10))


Basically, this same approach can be taken for similar trinkets as well. Not sure yet about Phylactery though because it's only periodic damage and that will change greatly with the Quick Decay glyph I imagine.

If I think of anything else, I'll let you know.

#32 Mupki

Mupki

    Glass Joe

  • Members
  • 15 posts

Posted 22 January 2010 - 01:04 AM

1) The 3.3 is how many immolate/ua ticks happen in the duration of 10 seconds. Unless I'm doing it wrong, I was using uptime formula as 1-(1-proc chance)^(duration of buff/frequency of the spell that procs it). I think that's what it is anyways. If that's wrong, any help would be appreciated.
2) The Models!B27 is your average cast time, the 2.5 represents 40% proc rate, which some say it is. I've also seen 10%, but from what I've seen, it procs every 50s or so which seems more like 40% to me.

#33 Syphilisia

Syphilisia

    Glass Joe

  • Members
  • 9 posts

Posted 22 January 2010 - 09:49 AM

Can I ask the correct way to add custom gear?

Some of my items are *ahem* lower than what you may add, namely my OH which is from naxx. When I add custom gear, I am still getting the "N/A" errors, which mean nothing is computing correctly. My attempts at adding custom gear/tweaking have no resolved this.

Meanwhile, +rep for an excellent spreadsheet. Looking very much forward to the final version for 3.3.

#34 Cerula

Cerula

    Von Kaiser

  • Allied Members
  • 42 posts

Posted 22 January 2010 - 04:09 PM

Tilly64 - This spreadsheet uses a lot of features from Excel and some macro functions from 2007. It cannot be ported to anything else

Syphilisia - We're working on getting all of the items added in.

#35 Cerula

Cerula

    Von Kaiser

  • Allied Members
  • 42 posts

Posted 22 January 2010 - 05:50 PM

Released a new version. If you downloaded the previous version just overwrite it with this one. Check the main page for the changelog.

#36 rockaround

rockaround

    Glass Joe

  • Members
  • 1 posts

Posted 24 January 2010 - 08:05 AM

I got two item IDs not implemented errors :

ID 47663
ID 49187

#37 Furyio

Furyio

    Glass Joe

  • Members
  • 1 posts

Posted 25 January 2010 - 04:40 AM

Dont know if you wanted the bugs reported here or some email, but i had this open so Ill do here


ItemID 49307 is not implemented, asked to be reported.

Because of this I'm unable to calculate for total dps etc, something similar to screenshot above.

Cheers and thanks for the hard work ;)

#38 Cerula

Cerula

    Von Kaiser

  • Allied Members
  • 42 posts

Posted 26 January 2010 - 04:16 AM

New version released. Check the initial post for the changes.

#39 Datalord

Datalord

    Glass Joe

  • Members
  • 16 posts

Posted 26 January 2010 - 08:31 AM

I hate to spread panic, but my Symantech Antivirus immeditely quaranteened and alerted me when I enabled the macros in the spreadsheet

AN INFECTION HAS BEEN DISCOVERED!

Scan type: Auto-Protect Scan
Event: Risk Found!
Security risk detected: W97M.Cooldown
File: (hidden)\AppData\Roaming\Microsoft\Excel\~ar2CB4.xar
Location: (hidden)\AppData\Roaming\Microsoft\Excel
Computer: (hidden)
User: (hidden)
Action: Pending Side Effects Analysis : Access denied
Date: 26. januar 2010 09:00:15



This is listed as an alias of MSWord.Cooldown on Kaspersky, and is rated as a severe threat from Micrrosoft.

#40 LemmiLemming

LemmiLemming

    Glass Joe

  • Members
  • 4 posts

Posted 26 January 2010 - 02:48 PM

Im trying to get it to work for several hours now ... got everything enabled etc. but always get the error when i click on the "Load Character from the Armory" button:

Systemerror &H80072EFD (-2147012867)

anyone got a idea how to fix this




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users