Warrior DPS Calculation Spreadsheet - Page 59

Machinator · 05/18/09, 12:13 PM

Originally Posted by NobleHelium

I'm not sure why the normal chance of the swing being a heroic strike matters. Yes, the sheet may say on average you are heroic striking 90% of the time, but in the case of low rage, you are not heroic striking at all. If you mean you had a heroic strike queued up and then realized you need rage to continue your cycle and thus cannot actually afford the heroic strike that has been queued, what's to stop you from unqueuing the heroic strike?

It matters because the sheet does not differentiate between stopping HS and continuing the rest of the cycle, or stopping the whole cycle for a percent of the time. Either you have slip or you don't.

And I am curious how many people have a macro to unqueue a HS and would be able to effectively use it in the timespan between a miss and the next swing. These streaks can happen in less than the time of 1 HS, and the sheet does not account for such things anyway.

Now that I think about it, would there not be rotation slip from an avoidance streak of 1 if the sheet intends to calculate HS and the rest of the cycle separately?

catch22atplay · 05/18/09, 12:14 PM

In cell AH45 remove Z46*C64*15/4/AH51* from the equation. You'll then notice dps does not change. In fact the only change i can see is that cell AH45 and cell AH46 have changed. That's it. That looked to be an area where rage calculations are in the formula. But it has no effect except to change 2 cells answers. Cell AH46 gets the amount of rage from cell AH56. Which is AH54-AH55. So no there is no subtracting of the rage loss due to using a Heroic Strike and not landing a melee white.

Machinator · 05/18/09, 12:25 PM

Originally Posted by catch22atplay

In cell AH45 remove Z46*C64*15/4/AH51* from the equation. You'll then notice dps does not change. In fact the only change i can see is that cell AH45 and cell AH46 have changed. That's it. That looked to be an area where rage calculations are in the formula. But it has no effect except to change 2 cells answers. Cell AH46 gets the amount of rage from cell AH56. Which is AH54-AH55. So no there is no subtracting of the rage loss due to using a Heroic Strike and not landing a melee white.

DSP2.404Excel07
I removed that part of AH45 and it increased my total DPS.

Erfinda · 05/18/09, 12:45 PM

Originally Posted by catch22atplay

In cell AH45 remove Z46*C64*15/4/AH51* from the equation. You'll then notice dps does not change. In fact the only change i can see is that cell AH45 and cell AH46 have changed. That's it. That looked to be an area where rage calculations are in the formula. But it has no effect except to change 2 cells answers. Cell AH46 gets the amount of rage from cell AH56. Which is AH54-AH55. So no there is no subtracting of the rage loss due to using a Heroic Strike and not landing a melee white.

AH46 adds AH45 in the denominator, which accounts for the rage loss from MH swings...

catch22atplay · 05/18/09, 1:04 PM

Except that it's broke and doesn't work correctly. Again verify rage with cell Z29's dps by using (15*dps value)/(320.6*4)=X rage per second against cell AH52. AH52 should match your results. That is the perfect verification. If it does not match then something is wrong.

Machinator · 05/18/09, 1:06 PM

They should not be the same. Z29 is white dps after HS have been taken out, AH52 is rage before HS have been taken out.

catch22atplay · 05/18/09, 1:11 PM

And there in lies the problem. AH52 needs this rage taken out. This is currently not happening and the whole point i'm trying to make. Because this is not taken out you have more rage to Heroic Strike with.

Another fix would be to add another cell. Put it between AH52 and AH53. Call it rage loss if you want. Use the cell for the purpose of subtracting the rage loss due to not landing a white hit because you used a Heroic Strike. You then add up AH51, 52 and then subtract the new cell. But i don't know if this will become chaotic or not. I'm just hypothesizing. But if this is done the results can be verified by subtracting the rage loss in this new cell from AH52. This total should then match Z29 and the formula i've previously shown.

Machinator · 05/18/09, 4:42 PM

Originally Posted by catch22atplay

AH52 needs this rage taken out.

This is wrong.

Originally Posted by catch22atplay

Another fix would be to add another cell. Put it between AH52 and AH53. Call it rage loss if you want. Use the cell for the purpose of subtracting the rage loss due to not landing a white hit because you used a Heroic Strike.

The cell you suggest adding already exists at AH45.

This is getting repetitive now. I think you misunderstand what AH52 is, because it is calculated before heroic strikes, and the rage loss from a HS replacing a white hit is calculated and properly subtracted. My example above is a basic parallel to how the sheet works and the math checks out from both directions. I think you need to explain how that is wrong before getting into the spreadsheet itself.

Marathon · 05/18/09, 4:51 PM

Why not contact Landsoul directly? None of us are Landsoul; none of us can update the spreadsheet for you. No-one can answer your question more accurately than Landsoul, because he knows the spreadsheet better than all of us.

LodeRunner · 05/18/09, 6:03 PM

catch22atplay and Erfinda, if you two can't play nice I'm taking my ball and I'm going home.

Breuger · 05/18/09, 6:47 PM

So, I get this series of errors when I attempt to use the 2007 compatible spreadsheet
"Excel found unreadable content in 'WarriorDPS2.404Excel07.xlsm' Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."
Clicked yes, the enabled macros and I get this.
"The Visual Basic Applications (VBA) macros in this workbook are corrupted and have been deleted. The macro corruption most likely exists in the current file. To recover the macros, open a backup copy of this file if you have one."

And now with the other version, an error box appears with, "This workbook has lost its VBA project, ActiveX controls and any other programmability-related features." in it. I click on the link labeled "Click to view log file listing repairs:" and this pops up.
" <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error049480_01.xml</logFileName>
<summary>Errors were detected in file 'C:\Users\Mason\Downloads\WarriorDPS2.404Excel97.xls'</summary>
- <additionalInfo>
<info>This workbook has lost its VBA project, ActiveX controls and any other programmability-related features.</info>
</additionalInfo>
</recoveryLog>"
Not sure if any of that information is useful, but I hope it's useful in fixing the issue.

skdal · 05/18/09, 8:30 PM

Originally Posted by Breuger

So, I get this series of errors when I attempt to use the 2007 compatible spreadsheet
"Excel found unreadable content in 'WarriorDPS2.404Excel07.xlsm' Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."
Clicked yes, the enabled macros and I get this.
"The Visual Basic Applications (VBA) macros in this workbook are corrupted and have been deleted. The macro corruption most likely exists in the current file. To recover the macros, open a backup copy of this file if you have one."

And now with the other version, an error box appears with, "This workbook has lost its VBA project, ActiveX controls and any other programmability-related features." in it. I click on the link labeled "Click to view log file listing repairs:" and this pops up.
" <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error049480_01.xml</logFileName>
<summary>Errors were detected in file 'C:\Users\Mason\Downloads\WarriorDPS2.404Excel97.xls'</summary>
- <additionalInfo>
<info>This workbook has lost its VBA project, ActiveX controls and any other programmability-related features.</info>
</additionalInfo>
</recoveryLog>"
Not sure if any of that information is useful, but I hope it's useful in fixing the issue.

Last time I got a Visual Basic error with the spreadsheet I had to redownload it and it worked fine. Have you tried that yet?

samot · 05/19/09, 11:15 AM

Assume, that there is a new version of this great sheet or the Arms calculation crashes, because you deleted an item / enhancement / gem instead of setting it to none (or whyever this happens sometimes).

In both cases you'll get the sheet fresh from the internet, enter your name / server / site and import form the armory. This would be fine, in the most cases, but you made some modifications regarding the buffs (f.e. to fit your usual Ulduar-10-group), Phoney Stats and replaced some of the current gems / items to check, if they are better. So you have to make all your manual changes again in the new sheet, to get the same results.

Being bored of this, I wrote a macro, that will import all the manual modified stats (Items, gems, enhancements, skills, buffs, debuffs, phoney stats, flask, food, boss type / level / armor, number of T7/T8 and even your race / lag / reaction / name / realm / site) from another sheet.

All you have to do (at least until Landsoul include it in his sheet) is to copy the following macro into a module of the NEW sheet (by pressing Alt-F11, double click onto f.e. Module 11 and paste it at the begin or end of the module). After this you could switch back to the Arms or Fury (works for both) sheet, press Alt-F8 and run the new SheetLookup macro.

The macro will first ask for the Excel file, that you want to import, opens it (if not already open) and import all the stuff listed above.

Sub SheetLookup()
' Imports items, stats, buffs etc. from another XLS-File (f.e. after a new version)
    Dim varTemp     As Variant
    Dim i           As Long
    Dim strPath     As String
    Dim strFile     As String
    Dim blnOpen     As Boolean
    Dim wrkDest     As Worksheet ' Destination (current workbook / sheet)
    Dim wrkSource   As Worksheet ' Source
    
    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog

    Set fd = Application.FileDialog(msoFileDialogOpen)
    Set wrkDest = ActiveSheet

    With fd
        .Filters.Add "Excel", "*.xls; *.xlsm; *.xlsa"
        .AllowMultiSelect = False
        If .Show = -1 Then
            strPath = .SelectedItems(1)
        Else
            GoTo ExitHere ' Cancel
        End If
    End With
    Set fd = Nothing
     
    For i = Len(strPath) To 1 Step -1
        If Mid(strPath, i, 1) = "\" Then Exit For
    Next i
    strFile = Mid(strPath, i + 1)
    
    blnOpen = True
    For Each varTemp In Workbooks
        If varTemp.Name = strFile Then
            blnOpen = False
            Exit For
        End If
    Next varTemp
    If blnOpen Then Workbooks.Open Filename:=strPath
    Set wrkSource = Workbooks(strFile).Worksheets(ActiveSheet.Name)
    wrkDest.Activate
    
    Application.Iteration = False
    EnableCalculation = False
'   Items
    For i = 6 To 21
        For Each varTemp In Array("c", "k", "o", "t", "ab", "af")
            If Not (wrkDest.range(varTemp & i).value = wrkSource.range(varTemp & i).value) Then
               wrkDest.range(varTemp & i).value = wrkSource.range(varTemp & i).value
            End If
        Next varTemp
    Next i
'   Buffs
    For Each varTemp In Array("J25", "J27", "J30", "J32", "J37", "J40", "J42", "J45", "J47", "J50", _
                              "K25", "K27", "K30", "K32", "K35", "K37", "K40", "K42", "K45", "K47", "K50", _
                              "P25", "P27", "P30", "P32", "P35", "P37", "P39", "P42", "P45", "P48", _
                              "U25", "U27", "U30", "U32", "U34", "U37", "U39", "U44", "U46", "U49", "U51", _
                              "V25", "V27", "V30", "V32", "V34", "V37", "V41", "V39", "V44", "V46", "V49", "V51", "V53")
        If wrkDest.range(varTemp).value <> wrkSource.range(varTemp).value Then
           wrkDest.range(varTemp).value = wrkSource.range(varTemp).value
        End If
    Next varTemp

'   Phoeney Stats, Flask, T-Level, T-Type etc.
    For Each varTemp In Array("C23", "C24", "C25", "C27", "C28", "C29", "C30", "D32", "D33", "D34", "C35", "C36", _
                              "C37", "C38", "C39", "C40", "C41", "C45", "C46", "C47", "C48", "C49", "C50", _
                              "C51", "C52", "C53")
        If Not wrkDest.range(varTemp).value = wrkSource.range(varTemp).value Then
           wrkDest.range(varTemp).value = wrkSource.range(varTemp).value
        End If
    Next varTemp
    
'   Skills
    For i = 6 To 21
        For Each varTemp In Array("al", "ao", "ar")
            Select Case varTemp & i
              Case "al21", "ap12", "ap19", "ap20", "ap21" ' DoNothing (empty cells)
              Case Else
                    If Not (wrkDest.range(varTemp & i).value = wrkSource.range(varTemp & i).value) Then
                       wrkDest.range(varTemp & i).value = wrkSource.range(varTemp & i).value
                    End If
            End Select
        Next varTemp
    Next i

'   Glyphes
    wrkDest.range("y23").value = wrkSource.range("y23").value
    wrkDest.range("y24").value = wrkSource.range("y24").value
    wrkDest.range("y25").value = wrkSource.range("y25").value

'   Name / Server / Site
    wrkDest.range("ak2").value = wrkSource.range("ak2").value
    wrkDest.range("ak3").value = wrkSource.range("ak3").value
    wrkDest.range("ak4").value = wrkSource.range("ak4").value
    
    wrkDest.range("p4").value = wrkSource.range("p4").value ' Race
    wrkDest.range("n3").value = wrkSource.range("n3").value ' Reaction time
    wrkDest.range("n4").value = wrkSource.range("n4").value ' Lag
    

    Application.Iteration = True
    EnableCalculation = True
    Application.Calculate
ExitHere:

End Sub

@Landsoul: for my testings I made a smaller arrow next to the "Import from armory" button and named it "Import from sheet". Just as suggestion, if you conclude to implement this macro in an offical version.

PS: I didn't test it below Excel 2007 (do not have an older version aviable) ...
PPS: You don't need to import form armory, before using the import from sheet (would be only a waste of time). For this reason the Import from sheet will be also great, if you are offline or behind a fire wall.

Feryar · 05/19/09, 11:56 AM

I have a question regarding the SEP values, I am basically wondering if they are "adjusted" for the bonuses to ArP and Str gained by arms and fury respectively.

Let's for the sake of an example imagine I am looking at SEP values in my arms-sheet, and both Str and ArP have a value of 1. Would this make ArP better due to the battle stance bonus (meaning 1 ArP from a gem is actually 1.1 after the bonus) or has this already been accounted for in the sheet?

deathpandas · 05/19/09, 12:01 PM

Just wanted to thank you for making such a great tool. I got a better idea of how to go about getting my ArP stats and what not. I was having a real hard time to collaborate what is what for ArP and it's benefits.