Archived

This topic is now archived and is closed to further replies.

Paininabox

Resto Spreadsheet

69 posts in this topic

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

<Bug>

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
<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.

Share this post


Link to post
Share on other sites

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."

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Please consider adding [iTEM]Idol of the Raven Goddess[/iTEM]. 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).

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Hello, is this still working? I'm clicking on the link and it comes up with the same screen. I tried saving the file, but it came up as a webpage. Any help would be appreciated. Thanks!

Share this post


Link to post
Share on other sites
Hello, is this still working? I'm clicking on the link and it comes up with the same screen. I tried saving the file, but it came up as a webpage. Any help would be appreciated. Thanks!

It works for me. Try going to the link and clicking the start download button; let me know if you still can't get it.

Share this post


Link to post
Share on other sites

I got it...thank you! I was reading that download now part as an ad because some family guy survey was above it and some other add was below it. Thank you for your help!

Share this post


Link to post
Share on other sites

I don't know if you're still updating the spreadsheet, but I believe Idol of Awakening should be applied before Moonglow and ToL form.

From WoWHead IoA comments:

idol subtraction takes place before percentage reductions. I don't care to write out the math, cause these numbers are pulled right from the game as of time of post.

With no talents- 629

with talents - 446

with talents & idol - 371

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

  • Recently Browsing   0 members

    No registered users viewing this page.