WotLK DPS spreadsheet - Page 249

Rethlon · 10/30/09, 7:11 AM

The Heroic version of Armor of Shifting Shadows have "Colliseum-10" listed as its location. Should be "Colliseum-10 Heroic"

Rethlon · 10/30/09, 8:13 PM

The Sentinel Scouting Greaves (non-heroic version) have haste 32 should be 39.

Resuu · 10/31/09, 4:50 PM

I was wondering if there is way to enable macros for a Mac-based Excel. It seems to disable them for me and I can't update/load/change anything. The .xlms and other recommended "Save-As" templates are only causing errors when trying to open them and not really solving any problems.

Meltorvyen · 11/01/09, 6:22 AM

Hi guys

Sorry if this has been mentioned before but I can't seem to find anything about it. In a previous version of the spreadsheet there was an option to 'assume hit capped' or something along those lines. I personally think that's a brilliant idea especially the way spreadsheet seems to treat hit for me.

I am 72 over the hit cap and yet hit is, according to the overview tab, more valuable to me than agility or any other stat for that matter. I have tried everything I can think of to fix it. The only thing that seems to work is giving myself custom gear with ridiculous amounts of hit (actually it has to be > 10^19). Then the attribute value jumps from 1.6 down to zero and the Needed to Hit Cap value can't be dislayed (you just get a cell full of #####). Then ofc, the recalculate dps button needs debugging as the line

hitToCap = Worksheets("Overview").Range("HitRatingToCap").value

from the regemmer macro can't be understood. Using the debugger to change hitToCap = 0 just broke the gear planner completely but using a negative number seemed to work. Using a large number in the order of 10^4 again broke the line but after testing with various different powers of 10 it doesn't seem to make a difference to the gear planner. Any negative number gives the same results. But not in the way I'd expect. I've ended up using -1 and -9999. Items with hit didn't become less of an upgrade in fact mostly the opposite happened. My neck, for example, [Sparkling Onyxia Tooth Pendant] was given a better delta value than before. Another interesting thing happened to the trinkets. Suddenly [Bandit's Insignia] and [Gnomish Lightning Generator] are the two BiS with undisplayably large delta values. Another thing to note is that although changing the line in the regemmer code does then allow the recalculate dps button to work, it does seem to have broken parts of what I assume are the regemmer. Basically the gear planner no longer shows 'Current Item with Regem'.

As far as I understand it hit vs. a lvl 83 boss isn't a soft cap. Once you have 0% chance to miss surely that's it, having more hit is useless. Ofc, I know very little about the inner workings of this spreadsheet, but I assume that these attribute values help calculate dps upgrades in the gear planner? So I thought you might like to know. Sorry if its working as intended and I'm actually an idiot but if anyone has the time I'd love someone to explain why this is happening... just for interest's sake.

Thanks

Shandara · 11/01/09, 11:15 AM

Well assuming you had the boss still at level 83 you need a lot of +hit to compensate the level difference. And it's not precisely a situation the sheet can cope with (or was designed to cope with).

Using it out of the bounds defined for use (i.e. being level 80 attacking a level 83 boss) is not something I've really kept up-to-date or bugfixed, sorry. It is very likely a lot of the calculations will bug out or show undesired results.

Meltorvyen · 11/01/09, 1:42 PM

Sorry I didn't mean to sound ungrateful for all your work. I am using the spreadsheet on all of its default settings except for the odd test I've done trying to tweak things, which I've described above. (Ofc I don't except it to be able to coupe with my messing around with the code. I was just telling you what I'd tried). As I understand hit, you need 8% +hit in order not to miss against a lvl 83 boss. I have over 8% and the spreadsheet seems to be telling me I need more. I was just wondering if there was a way to stop this, as a previous version had the option to disregard hit.

Shandara · 11/01/09, 2:54 PM

Sorry, I misread your post.

Do not mistake the numbers on the overview page. The +hit value doesn't cap out and go to 0. It's the average value +1 hit going from 0 hit to hit-capped. The gear planner doesn't use those values though since it actually tries out each item.

diadren · 11/03/09, 2:28 AM

Taking my current gear into account, the sheet tells me to optimize the gemming by dropping the nightmare tear into Twin's Pact (+4 agi) instead of the triumph legs (+6 agi).

Rethlon · 11/03/09, 4:28 AM

There seem to be some weird bug in excel.
Below is the Rotation test from one of my rotation tests.

Start 	Shot		Time U	Cast E	CD until	
0.00	Serpent Sting	1.65	1.65	21.15	
1.65	Chimera Shot	1.65	3.30	11.80	
3.30	Aimed Shot	1.65	4.95	13.45	
4.95	Steady Shot	1.79	6.74	6.60	
6.60	Steady Shot	1.79	8.39	8.25	
8.25	Steady Shot	1.79	10.04	9.90	
9.90	Steady Shot	1.79	11.69	11.55	
11.80	Chimera Shot	1.65	13.45	21.95	(Waited 0.25s) 
13.45	Aimed Shot	1.65	15.10	23.60	
15.10	Steady Shot	1.79	16.89	16.75	
16.75	Steady Shot	1.79	18.54	18.40	
18.40	Steady Shot	1.79	20.19	20.05	
20.05	Steady Shot	1.79	21.84	21.70	
21.95	Chimera Shot	1.65	23.60	32.10	(Waited 0.25s) 
23.60	Steady Shot	1.79	25.39	25.25	
25.25	Aimed Shot	1.65	26.90	35.40	
26.90	Steady Shot	1.79	28.69	28.55

The "CD until" for the Aimed Shot is listed as 23.60.

The code in VB that makes the control is

 If haveShot = 0 And ShotTable(i, 2) <= currentTime + And ShotTable(i, 1) <> "None" Then

Using "<=" it should pick the Aimed shot... but it does not.

Iamcal, that made the last major update to the Rawr wow simulator (major Update made in the v2.2.14 release), where he made the hunter module copy the spreadsheets calculations as close as possible (to the current v91e spreadsheet) noticed the same challenge while programming in C#.

He solved it by an "ugly" hack.

Snipet from Rawr code

// this is a horrible round hack, i know. the issue probably comes
// somewhere from a float->double cast.
// the issue is that we have currentTime at (e.g.) 65.35 and steady shot CD is at 65.3500000001
// so we skip forward by 0.1 seconds when we didn;t really need to.
if (!haveShot && s.time_until_off_cd <= currentTime+0.00001)
{

Adding the "+0.00001" to the spreadsheets code seem to solve the problem as well.

 If haveShot = 0 And ShotTable(i, 2) <= (currentTime + 0.00001) And ShotTable(i, 1) <> "None" Then

I have no idea what causes the problem. When debugging the VB code and adding watches... both variables shows 23.6.

I also tried to have the currenttime variable dim'ed as variant as well... but that did not help.

Rethlon · 11/03/09, 4:29 AM

Additional info my shot priority was

Serpent
Chimera
Aimed
Steady

Rethlon · 11/03/09, 5:03 AM

Apparently comparing doubles or floats for equality is difficult since the "=" tend to never be true if the values have been manipulated in a different way.

Link talking about the problem. Compare double in VBA precision problem - Stack Overflow

Better to compare if the difference is within a certain delta... i.e rely on < or > comparisons only ... and add a delta to the value being compared to.

Shandara · 11/03/09, 5:38 AM

Originally Posted by Rethlon

Apparently comparing doubles or floats for equality is difficult since the "=" tend to never be true if the values have been manipulated in a different way.

Link talking about the problem. Compare double in VBA precision problem - Stack Overflow

Better to compare if the difference is within a certain delta... i.e rely on < or > comparisons only ... and add a delta to the value being compared to.

Cheers for the heads up. This issue had reared its head in the past in another module as well (attribute calculations) but it had not been apparent to me in the rotation test.

Cranch · 11/03/09, 7:57 PM

No, the actual problem is due to the fact that 0.01 (and most of its multiples) have no exact representation in binary floating point (as does 0.1, etc.) So if one computation gets to 23.6 by adding some number of multiples of .01 and another computation gets to 23.6 by adding a different number of multiples of .01, the two results generally will not match.

The simplest way to fix the problem is just to multiply the times by 100 or 1000 (i.e. work in integral milliseconds.) Older spreadsheets used to support BCD numbers; I couldn't find any reference to that in the Excel documentation.

mistla · 11/04/09, 12:08 PM

I am looking at 92.b of the spreadsheet. One the Calculations worksheet in the Armor Penetration block. The calculation for the armor Constant looks correct when compared to wowwiki, but the Level_Selected parameter appears to be set to 80 when the boss level is set to 83. The result is a Constant of 15232.5 for a level 83 boss.

This quote is from wowwiki

For a level 80 target, C=15232.5. For a level 83, C=16635.

I am wondering if wowwiki has it wrong, I have my spreadsheet setup wrong, or there is a possible bug with the calculation. Can anyone else confirm or deny?

I searched the forums for armor + pen + constant before posting and only saw a quote of the wowwiki page I am referencing with no rebuttal of the information presented there. If this has been previously reported, I apologize.

baklava · 11/05/09, 7:41 AM

I'm having problem with Lock and Load ability at the moment.

If I don't use Lock and Load I get 11k+ dps, if I use 1/3, it decreases dps to 10k's.

 IF(AND(ExplosiveInRotation;LALExplosiveFrequency>0);VLOOKUP("Explosive Shot";ShotPriorityList;9;FALSE)-VLOOKUP("Explosive Shot";ShotPriorityList;17;FALSE);0)

I think this comes to true even LALExplosiveFrequence is 0 somehow.

there is high possibility that the problem is around here or problem with my excel, I couldnt be sure about it.

Could you check that you have the same problem, Shandara?