+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 29
Like Tree5Likes

Thread: Gear/Calculation Spreadsheet (focused on Inquisitor, for now)

  1. #1
    Soulwalker
    Join Date
    Aug 2011
    Posts
    11

    Default Gear/Calculation Spreadsheet (focused on Inquisitor, for now)

    I’ve been working on a spreadsheet (originally a C# application, but I converted it to a spreadsheet for ease of consumption/review) to fully capture all of the Rift calculations. Most of the analyses that I’ve seen were fairly simple and hand-wave a lot of the details. The goal of this spreadsheet is to capture all of these details so that we can understand how Rift’s calculations works. I decided to initially focus on inquisitor, but I plan to eventually expand it to the rest of the cleric souls (though I don’t plan to expand it for other classes since I only play cleric; if someone else wants to do that, please go ahead).

    This is a work-in-progress, so the data/calculations may not be correct. You’ve been warned!


    ============================================

    Google Spreadsheet Link

    You will probably want to make a copy of the spreadsheet so that you can save your own equipment. In order to do so, go to File -> Make a copy (this may require a Gmail account in order to be enabled).

    ============================================


    Basic Spreadsheet Usage

    As a rule of thumb, you should only modify the blue cells; the spreadsheet is kind of fragile since there are a number of links between sheets.

    On the Main sheet, you can enter your equipment/bonuses/buffs and see how they affect your stats, CP/SC/CP ratios, and Inquisitor tooltip damage. The spreadsheet is currently hardcoded to use the 61/11/4 Inquisitor/Warden/Defiler spec.

    You should populate the equipment section with your current equipment. Click on the blue cell for the slot you’d like to change, then click on the light blue arrow on the right of the cell to see the drop-down list, and then select an item. If the item you’re looking for is not in the list, you’ll need to go to the specific sheet for that slot and enter the details in the provided row at the top (in the blue cells).

    Populate the buffs that you want to have, depending on if you want calculations to be done in a raid or non-raid environment. Your current equipment and buffs matter, because they change the SP/SC/CP ratios, which in turn can change the equipment rankings. For example, CP becomes more useful when you have raid buffs, raising the relative rankings of pieces with CP. As such, EP should only be used as a relative comparison to other items at your given equipment/buffs.

    The SP/SC/CP weight calculation is incomplete, so it may not be accurate. If you have your own values for the SP/SC/CP weights, you can enter them in the Override section so that they will be used for gear rankings.

    On the Upgrades sheet, you’ll see the top updates (up to five) for each slot. This sheet was inspired by Ellipson’s spreadsheet, which was originally by Fluxions. You should not change any of the cells here. If you want to change your current equipment, you should go back to the Main sheet and update it there. If anyone knows how to make a two-way binding between cells in a Google spreadsheet, please let me know.

    If there are more updates than can be shown, you’ll need to go to the specific sheet for that slot. Do note that the sort doesn’t update automatically, so the listed order doesn’t necessarily match the rank. If anyone knows how to automatically sort based on a changing column, please let me know.


    Calculations

    If you just want to use the spreadsheet, you don't need to continue reading; the rest of this post is intended for number junkies.

    There are 3 hidden sheets, EquipStats, Spec/Buffs, and Inquisitor. I figure that only number junkies would really care about those sheets, so I’ve hidden them by default. To view these sheets, go to View in the menu, then Hidden Sheets, then the sheet you wish to view.

    General Stat Formulas:
    (Please refer to the Spec/Buffs sheet for these calculations)

    Health = 4470 + Endurance * 10 + BonusHealth
    Mana = MIN(17000, 5000 + Wisdom * 5 + Intelligence * 5)
    Wisdom = BonusStatMultiplier * (83 + EquipWisdom) + BonusWisdom
    Intelligence = BonusStatMultiplier * (44 + EquipIntelligence) + BonusWisdom
    Endurance = BonusStatMultiplier * (127 + EquipEndurance) + BonusEndurance
    SpellPower = BonusSPMultiplier * (0.75 * Wisdom + 0.25 * Intelligence +EquipSP) + BonusSP
    SpellCrit = 0.5 * Wisdom + 0.5 * Intelligence + EquipSC
    CritPower = EquipSC + BonusCP
    CritChance = SpellCrit / 126 + BonusCritChance
    % bonus damage on crit = FLOOR(CritPower / 28, 0.1)

    The following is what I currently have as affecting the various bonuses (please let me know if I’m missing anything):

    BonusHealth:
    CQ – Readiness 1 (245)
    CQ – Readiness 2 (485)
    CQ – Readiness 3 (730) (Note: CQ bonuses do stack, so 1, 2, and 3 can all apply at the same time)
    Feast of Domination (975)

    BonusStatMultiplier:
    Bard – Resonance (5%) (Note: Only applied to core stat values, so they don't include other bonuses such as PA)

    BonusWisdom:
    PA – Wis (60)
    Prismatic Glory (10)
    Stellar Brightsurge Vial (55)
    Excellent Brightsurge Vial (50)
    Bard – Fanfare of Power (70)
    Archon – Vitality of Stone (70)

    BonusIntelligence:
    PA – Int (60)
    Prismatic Glory (10)
    Stellar Brightsurge Vial (55)
    Excellent Brightsurge Vial (50)
    Bard – Fanfare of Power (70)
    Archon – Vitality of Stone (70)

    BonusEndurance:
    PA – End (60)
    Prismatic Glory (10)
    Bard – Fanfare of Vigor (70)
    Archon – Shared Vigor (70)

    BonusSP:
    PA – Weapon SP (96)
    PA – Wand SP (48)
    CQ – Readiness 1 (20)
    CQ – Readiness 2 (40)
    CQ – Readiness 3 (60) (Note: CQ bonuses do stack, so 1, 2, and 3 can all apply at the same time)

    BonusCritChance:
    Armor of Awakening (5%)
    Bard – Motif of Bravery (1%)
    Tempest – Focused Fire (5%) (Note: This is only up 50% of the time, so I currently calc this as 2.5%)
    Archon – Earthen Barrage (5%)
    Assassin – Lethal Poison (5%)

    Inquisitor Calculations

    I will start out with the raw damage formulas, excluding any bonuses/modifiers. The raw damage fomulas look like they are just functions of SpellPower that follow linear regressions (meaning that the only difference between spells are the SP coefficients and the constants).

    (Please refer to the Inquisitor sheet for these calculations)

    Scourge = 0.9x + 2187
    SanctionHeretic_Min = 0.285x + 692.5
    SanctionHeretic_Max = 0.315x + 765.5
    Sanction = 0.3x + 729
    BoltOfRetribution_Min = 0.475x + 1154.25
    BoltOfRetribution_Max = 0.525x + 1275.75
    BoltOfDepravity_Min = 0.57x + 1385
    BoltOfDepravity_Max = 0.63x + 1530.9
    NysyrsRebuke = 2.7x + 9840

    These formulas may not be exact, but they are extremely close if they aren’t; I had to tweak some of the constants with weird fractional parts to make them match my data.

    On top of these raw formulas, there are a number of SP multiplers and total damage multipliers based on spec/buffs. The following multiplers are based on the 61/11/4 Inquisitor build.

    TotalDamageMultiplier:
    61 Inquisitor gift (0.61)
    Inquisitor - Corporal Punishment (0.05)
    Inquisitor – Agent of Affliction (0.1) – Only for DOT damage
    Diligence (0.12) (Note: I think the tooltip is wrong; it seems to gives 0.4 instead of 0.3 per level)
    11 Warden gift (0.055)
    Warden – Boundless (0.05)
    4 Defiler gift (0.04)
    Defiler – Horrible Visage (0.04)

    SPMultiplier:
    Concentration (0.09) – Only for BoR and NR
    Mental Resilience (0.45) – Only for Scourge and Sanction
    Righteous Indignation (0.15) – Only for BoR and BoD

    On top of that, there’s the 10% bonus damage from Armor of Awakening.

    Factoring all of these multipliers in, the tooltip damage for Scourge at, say, 4428 SP, would be:

    AoAMultipler * (1 + TotalDamageMultipler) * ((1 + SPMultipler) * 0.9 * SpellPower + 2187)
    = 1.1 * (1 + InquisitorGift + CorporalPunishment + AgentOfAffliction + Diligence + WardenGift + Boundless + DefilerGift + HorribleVisage) * ((1 + MentalResilience) * 0.9 * SpellPower + 2187)
    = 1.1 * (1 + 0.61 + 0.05 + 0.1 + 0.12 + 0.055 + 0.05 + 0.04 + 0.04) * ((1 + 0.45) * 0.9 * SpellPower + 2187)
    = 1.1 * 2.065 * (1.45 * 0.9 * SpellPower + 2187)
    = 1.1 * 2.065 * (1.45 * 0.9 * 4428 + 2187)
    = 18093.72411

    While the tooltip may say that it does 18093 damage, this won’t necessarily match the actual damage on an enemy because this does not take Resist into account. It took me some time to figure out the damage mitigation formula since I didn't know what form it would take, but this does seem to match all of my data points, even though the constants seem wonky.

    Damage mitigation for -2 levels: Resist / (Resist + 2650)
    Damage mitigation for -1 level: Resist / (Resist + 2820)
    Damage mitigation for even level: Resist / (Resist + 2947)
    Damage mitigation for +1 level: Resist / (Resist + 2948)
    Damage mitigation for +1 level: Resist / (Resist + 2949)

    Even though the player is 2 levels below the level 62 target dummy, it seems to have the same mitigation as the level 60 target dummy, so I think that Trion is just treating the level 62 and 60 target dummies as the same level as level 60 players. All of this testing was done on the target dummies, so I don't know if raid bosses have different resist.

    The target dummies’ inherent resist seems to mitigate damage by 15%, putting them at about 520 resist.

    With 2 points in Cunning Analysis, this becomes 364 resist, which translates to 10.99366% damage mitigation.

    When Spiritual Scrutiny is used, this falls to 104 resist, which translates to 3.4087% damage mitigation.

    So, that scourge that says 18093 on the tooltip will actually do 18093.72411 * (1 - 0.1099366) = 16104 damage under normal conditions and 18093.72411 * (1 - 0.034087) = 17476 damage under Spiritual Scrutiny.

    The aforementioned numbers are only for normal attacks, not critical hits. The base critical hit damage is just 1.5 times the normal damage. Then, CritPower will add a multiple of the normal damage (not the critical one) to the base critical hit damage.

    One curious thing about CritPower is that it is floored to the thousandths place (or the tenth place if viewed as a percentage). I originally thought this was simply how it was displayed in the character window, so I kept the entire decimal in my calculations. However, it was slightly off, so I floored to the thousandths place, which then matched my data. This makes the CritPower function a bit of a step function, meaning that 1 additional CritPower doesn’t necessarily translate to any effect.

    For example, if I currently have 230 CP, that means my % bonus damage on crit is FLOOR(230/28, 0.1) = FLOOR(8.2142857, .01) = 8.2%. If I were to somehow gain 1 more CP, my damage would be the exact same because my % bonus damage on crit is still 8.2% (FLOOR(231/28, 0.1) = FLOOR(8.25, .01) = 8.2%). In fact, I would have to gain 3 more CP for my damage to change at all (233 CP = 8.3%). For the purposes of the +1 CP analysis, I use the entire decimal since I figure it’ll average out in the end.

    Anyways, back to the critical hit damage calculation.

    CriticalDamage = NormalDamage * (1.5 + FLOOR(CP / 2800, 0.001))

    To calculate the expected damage, it would just be:
    ExpectedDamage = CritChance * CriticalDamage + (1 – CritChance) * NormalDamage



    < To be finished at a later time >

  2. #2
    Ascendant Primalthirst's Avatar
    Join Date
    May 2011
    Posts
    5,832

    Default

    You amaze me.
    This is brilliant work.
    Are Hunt Rifts good exp?
    Last edited by Primalthirst; 12-31-2012 at 08:57 PM.
    Nope.

  3. #3
    Soulwalker
    Join Date
    Aug 2011
    Posts
    11

    Default

    Quote Originally Posted by Primalthirst View Post
    You amaze me.
    This is brilliant work.
    Are Hunt Rifts good exp?
    Haha, hey Primal.

    I think they might be good XP, but I'm not sure.

  4. #4
    Telaran Relinquish's Avatar
    Join Date
    Jan 2011
    Posts
    69

    Default

    Really nice work on this so far, only two errors i've noticed personally is the chest rune's data validation isn't set correctly and there seems to be some problems with changing essences, otherwise working well!

  5. #5
    Soulwalker
    Join Date
    Aug 2011
    Posts
    11

    Default

    Quote Originally Posted by Relinquish View Post
    Really nice work on this so far, only two errors i've noticed personally is the chest rune's data validation isn't set correctly and there seems to be some problems with changing essences, otherwise working well!
    Thanks for the feedback. I think I've fixed both of those issues now.

  6. #6
    Shield of Telara Eosmeep's Avatar
    Join Date
    Mar 2012
    Posts
    741

    Default

    It would be nice if you could add how to obtain the item/where it drops from as well, but I know that can be a challenge sometimes

    Upgrade window is a nice way to look ahead, wish I knew enough to make that.

  7. #7
    Rift Disciple Protoskull's Avatar
    Join Date
    Dec 2010
    Posts
    173

    Default

    Fantastic work here. I would be interested in hearing what other cleric healers value CP at. It seems to be very much the King of the DPS stats at the moment (in the budget it is currently being given).

    But what about for healing?
    Yes, I raid in pink, what of it?
    FT: 4/4 :: EE:4/5
    Not copying strats since 2011

  8. #8
    RIFT Guide Writer Redcruxs's Avatar
    Join Date
    Jan 2011
    Posts
    3,935

    Default

    Quote Originally Posted by Griedus View Post
    Scourge = 0.9x + 2187
    SanctionHeretic_Min = 0.285x + 692.5
    SanctionHeretic_Max = 0.315x + 765.5
    Sanction = 0.3x + 729
    BoltOfRetribution_Min = 0.475x + 1154.25
    BoltOfRetribution_Max = 0.525x + 1275.75
    BoltOfDepravity_Min = 0.57x + 1385
    BoltOfDepravity_Max = 0.63x + 1530.9
    NysyrsRebuke = 2.7x + 9840

    These formulas may not be exact, but they are extremely close if they aren’t; I had to tweak some of the constants with weird fractional parts to make them match my data.

    Did you test these scaling factors at multiple gear levels?

    I did a test about a year ago where I got my spellpower to 4 different values by removing/adding gear from about 100 SP up to 2k SP, then I tested out how much the damage changed by adding a powerstone (+200 sp) I used this to find the SP scaling at different values of SP and I found that they were all considerably different.

    This might have changed since then but it seems that the scaling of spells with SP isn't as simple as y=mx+b

    Another avenue to check out is the official trion FTP dump, there is an XML file in there that shows the "coefficent" and base damage of spells at each rank. however it doesn't explain what the coefficient means or how it is used in calculating damage.

  9. #9
    Plane Walker
    Join Date
    Nov 2011
    Posts
    483

    Default

    Quote Originally Posted by Redcruxs View Post
    Did you test these scaling factors at multiple gear levels?

    I did a test about a year ago where I got my spellpower to 4 different values by removing/adding gear from about 100 SP up to 2k SP, then I tested out how much the damage changed by adding a powerstone (+200 sp) I used this to find the SP scaling at different values of SP and I found that they were all considerably different.

    This might have changed since then but it seems that the scaling of spells with SP isn't as simple as y=mx+b
    After plugging in all my gear, removing the extra +60 SP from the synergy crystal, and modifying the base Wis/Int because of race so that the stats in the spreadsheet match exactly to my stats in-game, the spreadsheet is spot on with the tooltip values except for the Min damage of Bolt of Retribution.

    At 3592 SP, the min value of BoR in game for me is 7067, while the spreadsheet shows 7069.

    Dropped my SP down to 1643 by removing my weapon and the spreadsheet is still spot on with the exception of the BoR min value, which is 4586 in-game vs 4587 in the spreadsheet.

    Removing most of my equipment to drop SP down to 678 it's still spot on but with BoR min being 3358 in-game, 3359 spreadsheet.

  10. #10
    Ascendant
    Join Date
    Jun 2011
    Posts
    1,450

    Default

    Quote Originally Posted by Redcruxs View Post
    Did you test these scaling factors at multiple gear levels?

    I did a test about a year ago where I got my spellpower to 4 different values by removing/adding gear from about 100 SP up to 2k SP, then I tested out how much the damage changed by adding a powerstone (+200 sp) I used this to find the SP scaling at different values of SP and I found that they were all considerably different.

    This might have changed since then but it seems that the scaling of spells with SP isn't as simple as y=mx+b

    Another avenue to check out is the official trion FTP dump, there is an XML file in there that shows the "coefficent" and base damage of spells at each rank. however it doesn't explain what the coefficient means or how it is used in calculating damage.
    The XML dump is worthless, the values don't seem to match reality. In principle, maybe you could reverse it, but that'd be more work than just solving for them directly.

    At least for warrior's it followed a strictly linear progression from near zero to full expert gear, for both Weapon DMG and AP.

    Good to see this getting worked on for clerics. Drop me or MealRain a PM, we're (slowly) working on a revised version for Warrior's. It sounds like you've got this one all solved though.
    ---------------------------------------------
    Caelem -- Warrior Tank/DPS
    Carinae -- Mage Alt
    Greybriar -- <Last Attempt> 9/9 T1, 2/4 GA, 1/4 IG, 1/5 PBB

  11. #11
    Plane Touched Aethys's Avatar
    Join Date
    Jan 2012
    Posts
    203

    Default

    Good job, thank you.

  12. #12
    Soulwalker
    Join Date
    Aug 2011
    Posts
    11

    Default

    Quote Originally Posted by Eosmeep View Post
    It would be nice if you could add how to obtain the item/where it drops from as well, but I know that can be a challenge sometimes

    Upgrade window is a nice way to look ahead, wish I knew enough to make that.
    Sure, I can add that. I also plan on adding Resist and Set information.

  13. #13
    Soulwalker
    Join Date
    Aug 2011
    Posts
    11

    Default

    Quote Originally Posted by Protoskull View Post
    Fantastic work here. I would be interested in hearing what other cleric healers value CP at. It seems to be very much the King of the DPS stats at the moment (in the budget it is currently being given).

    But what about for healing?
    I've already begun work on the warden calculations, though there are still some issues that I'm wrestling with. I'll let you know when I have an update.

  14. #14
    Soulwalker
    Join Date
    Aug 2011
    Posts
    11

    Default

    Quote Originally Posted by Redcruxs View Post
    Did you test these scaling factors at multiple gear levels?

    I did a test about a year ago where I got my spellpower to 4 different values by removing/adding gear from about 100 SP up to 2k SP, then I tested out how much the damage changed by adding a powerstone (+200 sp) I used this to find the SP scaling at different values of SP and I found that they were all considerably different.

    This might have changed since then but it seems that the scaling of spells with SP isn't as simple as y=mx+b

    Another avenue to check out is the official trion FTP dump, there is an XML file in there that shows the "coefficent" and base damage of spells at each rank. however it doesn't explain what the coefficient means or how it is used in calculating damage.
    Yes, I tested at multiple gear levels. I think you may not have taken the constant into account.

    Let us say that the damage formula for some theoretical attack is 0.75x + 50.

    At 100 SP, this attack would do 125 damage.

    At 4000 SP, this attack would do 3050 damage.

    If you didn't think that the damage formula had a constant, you would think that the SP coefficient was changing since it would be 125/100 = 1.25 at 100 SP and 3050/4000 = 0.7625 at 4000 SP.

    You should be able to use my spreadsheet to calculate the expected values and compare them with what you see in-game at multiple gear levels. If they don't match with what you see, please let me know.

  15. #15
    Soulwalker
    Join Date
    Aug 2011
    Posts
    11

    Default

    Quote Originally Posted by Golladan View Post
    After plugging in all my gear, removing the extra +60 SP from the synergy crystal, and modifying the base Wis/Int because of race so that the stats in the spreadsheet match exactly to my stats in-game, the spreadsheet is spot on with the tooltip values except for the Min damage of Bolt of Retribution.

    At 3592 SP, the min value of BoR in game for me is 7067, while the spreadsheet shows 7069.

    Dropped my SP down to 1643 by removing my weapon and the spreadsheet is still spot on with the exception of the BoR min value, which is 4586 in-game vs 4587 in the spreadsheet.

    Removing most of my equipment to drop SP down to 678 it's still spot on but with BoR min being 3358 in-game, 3359 spreadsheet.
    I made a mistake in the spreadsheet, so the BoR Min tooltip value was actually referencing the +1 SP value instead of the normal value. I've fixed this mistake, so I hope that all the calculated values should now match what you see in-game.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts