Jump to content


Photo

Resto Spreadsheet


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

#1 Paininabox

Paininabox

    Piston Honda

  • Members
  • 137 posts

Posted 31 March 2009 - 02:51 AM

The purpose of this thread is to discuss Resto Spreadsheet, i.e.:

  • Bugs
  • Mistakes
  • Suggestions
  • Corrections
  • Ideas for expansion
  • Ideas for modeling

This thread is not meant to discuss:

  • Itemization
  • Playstyles

For example, please note the difference between discussing how to model the power of a glyphed HT build versus discussing glyphed HT build viability. The first falls in this thread, whereas the other would likely fall into the PVE discussion thread. Please keep them separate.

My overall goal for the spreadsheet is to provide a reliable tool for the community to use to model situations, gearing decisions, and playstyle choices for PVE encounters. I would like the spreadsheet to reach a point where it is credible enough that it can be trusted to make accurate calculations, while still allowing for the imperfection of reality.

Currently, Resto Spreadsheet is at a point where I think it provides solid calculations for spell stats and spitting out reliable stat totals from a gear/buff setup (you have no idea how not-simple it can be). Currently, I've been branching out into stat weight calculations. The regeneration side of the scale is very strong where the healing power scale is very faulty. I'm in the process of developing a solid healing power scale using a method similar to the regen scale. I'm also trying to figure out how I'm going to do lifebloom calculations when there are so many different methods and human errors that greatly influence its strength.

Spreadsheet:
Resto Spreadsheet v1.41L

Supporting Documents:
Mana Regeneration Analysis v1.1
Level 80 Restoration Druid Spreadsheet here.
(v1.41)

#2 Guest_Allinone_*

Guest_Allinone_*
  • Guests

Posted 31 March 2009 - 07:46 PM

Perhaps it is the version of Excel that I use, (Excel 2003) but changing the value for Gift of the Earth Mother does not appear to change the cast times for Lifebloom, Wildgrowth, or Rejuvenation. While this doesn’t actually matter in terms of HPS for a HoT, it would make the spreadsheet more useful in terms of a ref to how much Haste is still needed to reach the Softcap based on Talent choices. For that matter a 'Haste needed to hit Softcap' counter could be added.

Also, I believe it was announced that Mana Spring totem and Blessing of Wisdom will no longer stack, the spreadsheet might need to reflect this accordingly, but this should most likely wait until all changes have been finalized on the PTR.

Also, from my personal experiance (as well as looking at other guilds WWS reports) I believe that a 100% uptime on Replenish is grossly over estimated. Even on a patchwerk type fight I only observe an 80-85% uptime in a 25 man raid with 3-4 classes that can provide it. I would suggest either adjusting the value from 100 or (what I would prefer) is a feature similar to your '% time in 5SR', which would allow the user to select how much weight to give replenishment.

Lastly, in terms of modeling/gear ranking, the values presented should reflect the relative value of one point of stat on the gear, and not one point of stat on the individual. Due to blessing of kings, (assuming it is present) 1 int on gear = 1.1 int on the character (1.122 if you also have an Ember Skyflare Meta). In a similar fashion Spirit should reflect the appropriate changes if someone has Blessing of Kings and Living Spirit (1.1 x 1.15 = 1.265). It appears that you have done this for your Mana regeneration rankings, but not for your Spell Power rankings.

You've already discussed calculating living seed procs, as well as a number of other changes, so I won't discuss them here.

#3 Paininabox

Paininabox

    Piston Honda

  • Members
  • 137 posts

Posted 31 March 2009 - 08:49 PM

Perhaps it is the version of Excel that I use, (Excel 2003) but changing the value for Gift of the Earth Mother does not appear to change the cast times for Lifebloom, Wildgrowth, or Rejuvenation. While this doesn’t actually matter in terms of HPS for a HoT, it would make the spreadsheet more useful in terms of a ref to how much Haste is still needed to reach the Softcap based on Talent choices. For that matter a 'Haste needed to hit Softcap' counter could be added.

Also, I believe it was announced that Mana Spring totem and Blessing of Wisdom will no longer stack, the spreadsheet might need to reflect this accordingly, but this should most likely wait until all changes have been finalized on the PTR.

Also, from my personal experiance (as well as looking at other guilds WWS reports) I believe that a 100% uptime on Replenish is grossly over estimated. Even on a patchwerk type fight I only observe an 80-85% uptime in a 25 man raid with 3-4 classes that can provide it. I would suggest either adjusting the value from 100 or (what I would prefer) is a feature similar to your '% time in 5SR', which would allow the user to select how much weight to give replenishment.

Lastly, in terms of modeling/gear ranking, the values presented should reflect the relative value of one point of stat on the gear, and not one point of stat on the individual. Due to blessing of kings, (assuming it is present) 1 int on gear = 1.1 int on the character (1.122 if you also have an Ember Skyflare Meta). In a similar fashion Spirit should reflect the appropriate changes if someone has Blessing of Kings and Living Spirit (1.1 x 1.15 = 1.265). It appears that you have done this for your Mana regeneration rankings, but not for your Spell Power rankings.

You've already discussed calculating living seed procs, as well as a number of other changes, so I won't discuss them here.


A "haste until softcap" calculation would likely be a very good idea. As to Mana Spring and BoW not stacking, I'm not sure how I'll handle that but off the top of my head it will probably be something like what I have going with gems (put in a gem on gear without a slot for the gem = red box). I agree with you about replenishment. It should be an easy fix to put in the % uptime scalar on the equations. As to the modeling formulae, I agree with you, as well. The spell power rankings have always been clunky and generally poor; I'm working on a better system to more accurately weight the stats. The strength of it my plan lies in the fact that it can be customized. For example, different spells scale differently with different stats, and the amount you use each spell fluctuates from player to encounter. I'm devising a system where I look at how much hps gain you get per point of stat per spell, then using weighted distributions that the user inputs it will calculate how much, overall, benefit is gained in terms of throughput for each stat. To put it in more simple terms, if 90% of your healing is nourish, then crit will be valued highly, as opposed to if 90% of your healing is rejuv, where crit is worthless. The only major difficulty, besides clunkily long equations, is how I'm going to handle the new nature's grace.

I'll make sure those features are implemented in the next version, thank you.
Level 80 Restoration Druid Spreadsheet here.
(v1.41)

#4 Guest_Allinone_*

Guest_Allinone_*
  • Guests

Posted 01 April 2009 - 07:43 AM

<Bug>

Equiping the Glyph of Innervate doesnt seem to actually change the total mana returned from innervate,

#5 Erdluf

Erdluf

    Great Tiger

  • Members
  • 968 posts

Posted 01 April 2009 - 04:15 PM

There is a trick to get Excel to compute scaling for you (I don't believe this works with Open Office, but the non-scaling cells will still work in OO).

Here is a little Rejuv spreadsheet. It only considers balance talents.
A	B	C	D	E
1	SP	Haste	Latency	CF	Splendor
2	2000	241	0.2	0	0
3					
4	Spell	Rejuv			
5	Cast Time	=1.5/(1+Haste/3279)/(1+0.01*CF)+Latency			
6	Base	1690			
7	Multiplier	=1+0.2*Splendor			
8	Coef	1.88			
9	Heal	=(Base+SP*Coef)*Multiplier			
10	H/Cast	=Heal/Cast_Time

Suppose I want to know how Heal and H/Cast scale with Nature's Splendor.
I add the following (it must be done on the sheet that contains Splendor, does not have to be on the sheet that contains the result).

A	B	C	D
13	NS Scale		=1-Splendor	
14	Heal	=Heal		=C14-B14
15	H/Cast	=H_Cast		=C15-B15

I then select the rectangle from B13 to C15. I want to add a data table. In recent versions of excel I go to the Data Tab, select What-If Analysis, and then Data Table. If you don't see these, you may need to add the Analysis add-in.

In the Data Table dialog box I type Splendor (or E2) in "Row Input Cell". I leave "Column Input Cell" blank. I then hit OK.

The final spreadsheet looks like this.

SP	Haste	Latency	CF	Splendor
2000	241	0.2	0	0
				
Spell	Rejuv			
Cast Time	1.597301136			
Base	1690			
Multiplier	1			
Coef	1.88			
Heal	5450			
H/Cast	3412			
				
				
NS Scaling		1		
Heal	5450	6540	1090	
H/Cast	3412	4094	682

The top section is my stats.
The middle section is rejuv info based on my stats.
In the bottom section, the first column of numbers are current values. Second column is what those values would be if my stats had 1/1 Splendor. The third column is the difference (value of a point in Splendor).

Notice that once I had the middle sections (compute numbers based on stats), I didn't need to duplicate any formulas in order to get the scaling.

WrathCalcs uses this trick extensively for Moonkin scaling with regards to both talents and stats. Expand rows 30 to 100 on the first page of the spreadsheet to see what it does.

#6 Paininabox

Paininabox

    Piston Honda

  • Members
  • 137 posts

Posted 01 April 2009 - 07:50 PM

<Bug>

Equiping the Glyph of Innervate doesnt seem to actually change the total mana returned from innervate,


Noted, thank you.

@ Erdluf

Thank you for that. That certainly makes quite a few calculations a ton easier. I've already finished the equations, so I might as well use them, but I'm definitely going to use this instead when the opportunity crops up. It seems to be a much more efficient use of time. Quick question, does heavy use of this method slow down the sheet more than doing it the "normal" way? I was looking at how the Wrathcalcs sheet used the what-if analysis, and I noticed that it seemed as though the sheet ran slowly from having to recalculate those all the time.

For anyone that wants to see/check my work on the equations I've cooked up, they can be seen here: Healing Power Stat Derivations. I'm beginning the process of implementing them into the sheet, at the moment.
Level 80 Restoration Druid Spreadsheet here.
(v1.41)

#7 Erdluf

Erdluf

    Great Tiger

  • Members
  • 968 posts

Posted 01 April 2009 - 09:03 PM

I don't know if the data tables are slower or faster than explicit scaling formulas. They often take less time to develop and maintain.

Note that in Formulas/Calculation Options you can pick

- Automatic
- Automatic Except for Data Tables
- Manual

so the user has some control over how responsive the sheet is. However I'd expect that most users won't be aware of this.

Things get worse as you add functionallity. Once you have stat weights and gear lists, it is natural to score the pieces of gear.

If you are scoring the pieces of gear, it makes sense to compute things like: What item would give me the biggest increase in HPS (for some assumed situation) per Emblem of Valor spent?

Given a spreadsheet where changing one haste rating causes computations with every piece of gear (and a new search for the top pieces) and it is easy to put together spreadsheets that become somewhat "clunky."

#8 Manitou

Manitou

    Glass Joe

  • Members
  • 1 posts

Posted 03 April 2009 - 10:11 AM

Hiya,

Great looking spreadsheet but there is one thing missing (hoping I'm right) for some of us tree's out there.

There are no Jewelcrafting only Gems selectable when gearing and I don't think its possible to manually add those as the cells are locked. I myself always have 3 in my gear so it would be great to have them as an option.

Thanks,


Manitou

#9 Paininabox

Paininabox

    Piston Honda

  • Members
  • 137 posts

Posted 03 April 2009 - 09:09 PM

Hiya,

Great looking spreadsheet but there is one thing missing (hoping I'm right) for some of us tree's out there.

There are no Jewelcrafting only Gems selectable when gearing and I don't think its possible to manually add those as the cells are locked. I myself always have 3 in my gear so it would be great to have them as an option.

Thanks,


Manitou


Jewelcrafting gems will be out next version, thank you.

Erdluf:

Alright, thanks for the info.

"What item would give me the biggest increase in HPS (for some assumed situation) per Emblem of Valor spent?"

That would be a pretty useful calculation, but it will have to wait until the next version. I have a lot on this one already.

I'm adding Innervate to the MRV scales because now that it doesn't easily cap out, it's an important consideration.
Level 80 Restoration Druid Spreadsheet here.
(v1.41)

#10 Tebasile

Tebasile

    Glass Joe

  • Members
  • 12 posts

Posted 07 April 2009 - 10:21 AM

Thanks for doing this!

I've had a chat going on with one of the Rawr devs to get the soft-cap haste added to that... but as a non-math person, I think I'm doing an awful job.

If anyone has any insight, here is the thread: Rawr - Discussions

I apologize for hijacking the spreadsheet thread, but I use all sorts of tools, so was hoping folks looking here could look there to figure out why the soft-cap isn't working correctly.

Again, I apologize if this is the wrong place to post, I read here constantly, and tend not to post much... and as I couldn't start a new topic, I jumped out on a limb (pun intended), as this seemed too detailed for the Q&A thread.

What would be great is a 'Rawr.Tree Module' thread... I've just not posted enough in 2 years to create one. >.< Color me embarrassed.

#11 Rototoro

Rototoro

    Glass Joe

  • Members
  • 24 posts

Posted 13 April 2009 - 06:06 PM

I just downloaded and started to fill this in. In the talents tab, I don't see Improved Regrowth, am I missing something?

EDIT: Yes I'm missing the fact that it's now called Nature's Bounty. Apologies. However, does this mean the spreadsheet is doing mana regeneration calculation assuming the 3.1 model?

#12 Paininabox

Paininabox

    Piston Honda

  • Members
  • 137 posts

Posted 13 April 2009 - 06:09 PM

Yeah, it got changed in 3.1 to Nature's Bounty I think.
Level 80 Restoration Druid Spreadsheet here.
(v1.41)

#13 Rototoro

Rototoro

    Glass Joe

  • Members
  • 24 posts

Posted 13 April 2009 - 06:14 PM

Yeah, it got changed in 3.1 to Nature's Bounty I think.


My EDIT crossed this in posting. Thanks for the response. So the spreadsheet is using 3.1 mechanics?

#14 Paininabox

Paininabox

    Piston Honda

  • Members
  • 137 posts

Posted 13 April 2009 - 06:19 PM

My EDIT crossed this in posting. Thanks for the response. So the spreadsheet is using 3.1 mechanics?


Yeah, the spreadsheet has basic support for 3.1, including the mana regen changes.
Level 80 Restoration Druid Spreadsheet here.
(v1.41)

#15 Rototoro

Rototoro

    Glass Joe

  • Members
  • 24 posts

Posted 13 April 2009 - 06:25 PM

Fantastic.

A mechanics issue - the spreadsheet doesn't work on a Mac using Excel 2004 (version 11.3.7). Gives 'Sub or Function not defined' errors with the VB functions and eventually crashes. I'm not expecting it to work on all platforms, OS revisions, and Excel versions, and am not expecting any investigation of this, just thought you might like to know. I will just use the spreadsheet at home on my Windows box that has Excel 2007 on it.

#16 Guest_Allinone_*

Guest_Allinone_*
  • Guests

Posted 18 April 2009 - 07:02 PM

Bug/Exploit.

In an attempt to push your spreadsheet, I recently stumbled across an 'error' of sorts in the HPS calculation. Depending on how your rewrite goes, this could actually be of little importance.

In your HPS calculations, it should limit the spam timer to the global cooldown.

Allow me to explain. I was attempting how well spells with haste, comparing Glyphed Healing Touch, Regrowth, and Nourish. By giving my spells enough haste to make the nourish cast timer = 1 second, i found it to do crazy things to Healing Touch (Glyphed). Long story short, Healing Touch scaled down to a cast timer of under .2s (not sure if this behaivor works the same way on live), and its HPS jumped up to 33,000 HPS. While I admit that this is a very special case (as glyphed HT is the only spell that is easily haste capped) but it would give more accurate comparisons between glyphed HT and the rest of our spells.

So in most cases the HPS calculation should be limited by the cast timer or the spell, unless the cast timer is less than the GCD.

#17 Paininabox

Paininabox

    Piston Honda

  • Members
  • 137 posts

Posted 19 April 2009 - 07:27 AM

I too noticed that glyphed HT's were getting huge HPS numbers. It's true, however, that the spell, when glyphed, can be faster than the GCD. The glyph is deceptive in that it states a 50% decrease in cast time, which one would assume is multiplicative. In actuality, it's a flat -1.5s off the cast time. However, the formula is calculated in such a way as haste scales the cast time down as fast as if it were still at the 3 second cast, so that haste is better at reducing glyphed HT cast time than it is at the GCD. I realize that it's unrealistic and deceptive to portray glyphed HT as some uber heal like that, because you have to wait for the GCD even when it has only .6s cast or whatever. The fix I implemented is that when the cast time gets below 1 second, additional fields pop up for an average cast time, which is capped at one second (due to GCD). I use this to then calculate a much more reasonable HPS value.
Level 80 Restoration Druid Spreadsheet here.
(v1.41)

#18 Erdluf

Erdluf

    Great Tiger

  • Members
  • 968 posts

Posted 22 April 2009 - 06:30 PM

Please consider adding . It works now, and 44 SP (all heals) seems very competitive when compared to other pre-Uldar idols (for instance 125 SP, Lifebloom HoT only).

#19 Cyrran

Cyrran

    Glass Joe

  • Members
  • 2 posts

Posted 27 April 2009 - 12:19 AM

Apparently this file isn't available anymore (error when trying to download from the linked site). Anyone have a copy around that they can host somewhere?

#20 Paininabox

Paininabox

    Piston Honda

  • Members
  • 137 posts

Posted 27 April 2009 - 02:11 AM

It still works fine for me. Try again, maybe the site was down/having maintenance. Let me know if you still can't get it.
Level 80 Restoration Druid Spreadsheet here.
(v1.41)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users