Cataclysm Balance Theorycraft and Spreadsheet Thread - Page 14

♦ Tecton · 11/23/10, 6:31 AM

Fixing stuff now:

I'm now displaying Spellpower in the same cell as Intellect for weapons & trinkets. I didn't think it warranted adding another row.
I've added a single row between each item, 10 pixels, good suggestion. It does cramp things in the hidden values a little, but we can expand those when we need to check them. I readjusted the guide formatting a little to allow for this.
MATCH() changed to an array formula, which should remove the crit/hit item issue and is a bit of a better solution anyway, as you say.
Reforges should no longer display at all if they are invalid.
!VALUE errors fixed, it was a misplaced bracket when adding the reforge values (i.e. it was adding it outside of the error checking).

I'm trying to figure out some way of preventing reforging of trinket procs/uses, if I can't get it shortly I'll upload these changes. Are the asterisks you added beside trinkets purely for a visual check?

Regarding adding conditional formatting for each kind of gem, Excel by default has a cap of 3 conditions. You can get around it, but it's a bit of work to do so (especially while avoiding macros/etc). I can take it a look at it further down the road.

♦ Tecton · 11/23/10, 9:17 AM

Didn't get the proc/etc stuff off of the reforge options yet, bear with me on that one. Above stuff is fixed, reforge menus are tidied up.

Latest version:

klüger · 11/23/10, 10:07 AM

If the insane requirement for the Chaotic meta remains, could it be worth it using the wotlk-meta? I can imagine the ~20 crit rating being made up by being able to have only 2 blues rest red.

♦ Tecton · 11/23/10, 10:14 AM

The WotLK meta was updated to the same requirement, from memory.

[edit] Ran out of time for today, I'll add professions after your next draft. [/edit]

• Hamlet · 11/23/10, 12:11 PM

You can keep it for today if you want, I don't think I particularly have anything I was going to do.

♦ Tecton · 11/24/10, 8:05 AM

Chaotic requirements reverted to WotLK values (as per today's blue post, I'm just assuming it'll be patched before we get to 85).

Professions mostly added. Skinning, mining and herbalism are added as straight stat values (i.e. 80 crit, 80 haste and 120 stamina, respectively). The benefits for the other professions should only be available if you select that profession (i.e. Chimera's Eyes, etc). I'm 99% sure I've got everything working so that if you select a benefit then swap away from that profession it should remove the stats and hide the benefit too, but if I've missed one please let me know.

The only exceptions are Engineering helms (reworking the gear list, etc, for one item seemed a lot of work, especially after the pain in the ass that was Jewelcrafting) so just don't equip these for now if you don't have Engineering.

Mixology stats aren't pulling through yet as they are selected on the 2nd page directly. Hamlet, if you could update those as per your PM last night that'd be awesome (I think you might also want to add options for more potions than just Mythical Mana, too?). I had a look at it, but I'm not 100% of where all the references are yet, so didn't want to break anything unwittingly.

One bug I did notice with the second page: if you set Moonfire and Insect Swarm to "Unused" everything goes to #DIV/0 errors. I know we're never going to use a dotless rotation, I was just checking menus.

[edit] Another new version, you should no longer be able to reforge stats procced from trinkets. [/edit]

• Hamlet · 11/24/10, 5:34 PM

Fixed that bug and added Mixology (for Flasks only for now). Probably won't be doing anything else for a couple days.

That's fine for Engineering items. Other professions will have BoP stuff too I'm sure, people will just have to know not to equip them. It was worth doing for gems.

aceofsween · 11/24/10, 10:19 PM

There's a small (and simple) bug with the Hit capped checklist. In cell AI:2 it should be set to
=CONCATENATE(IF(I5>=Main!C53,"Yes","No")," (by ",ROUND(ABS(Hit-Main!C53),1)," rating)")

For example, using the default values if you were to put 2 in for a manual adjustment for Hit, the box would still say no (even though the "by X rating" will adjust to it).

Also, what's up with the glyph selections? Starsurge either has no model for DPS value or it's not applying correctly when you select it and the Major Glyphs all change based on the value of the 3rd Prime glyph. Finally, if you select None (or Starsurge for that matter) as the 2nd Prime Glyph, the Moonfire glyph (in the 1st slot) changes to a negative number.

Edit: Nevermind figured out what was going on. If Starfall is set to be used "During Lunar" it makes both the Starfall and Starsurge Glyphs appear to do nothing. Also, unlike the WotLK Wrathcals, I wasn't aware that you need to manual refresh the values on the page using F9 until after I read the note more closely.

♦ Tecton · 11/25/10, 3:12 AM

If you'd prefer the sheet to refresh on each change, you can go to Tools > Options > Calculations and choose Automatic, rather than Automatic (except tables) and it'll update on each change.

I'll update the checklist section you mentioned in the next build, good catch.

• Hamlet · 11/29/10, 6:06 PM

I turned off auto-calculation of tables (i.e. I save the sheet with auto-calc turned off in my Excel preferences) now, because the sheet is so much bigger than before and it was starting to take a really long time.

Tecton, did you have the sheet "checked out" at the moment? I think I'm probably not going to be doing much more between now and release, so any further cleanup you want to do is fine. I do want to get it ported to the Resto sheet at some point as the only big remaining issue. Basically, whenever you're done making any updates, you can copy all your new sheets into the last version of the Resto sheet (I think the same version you've used before) and make sure they're all working. I can worry about hooking it up to the stats in the "old" sheet again and generally making any other changes.

♦ Tecton · 11/30/10, 4:17 AM

I'll tidy a few things up and post up a version. I've not had a ton of time the last few days, but I've been trying to fathom out a neat way to calculate optimal reforging (ideally, without resorting to macros) but that's a tricky puzzle as people are finding out so I'll probably cut that for now and work on getting Resto ready over the next couple of days. Will drop you a PM with a preliminary version.

[edit]

Fixed the hit rating bug noted above, amended a trinket proc that had been missed and a couple of other housekeeping things.

Latas · 11/30/10, 12:59 PM

This is mainly a housekeeping thing but, it would probably be good to put blank values in for one of the drop down selections on stat to reforge. Also is there a dps increase on next point of each stat section?

• Hamlet · 11/30/10, 2:20 PM

Hmm, I didn't know you were trying to do anything that fancy. Any automated gem/reforge selection would be really tricky though. You obviously can't do it in the general case--that's basically trying to replicate Rawr. But I guess the idea is--given our knowledge of which stats are better than which other ones, can we come up with an algorithm that reforges everything in the right direction automatically?

This thought might give you a bit of pause: say I have a bunch of items I can reforge to hit, and I only need to reforge some subset of them to reach cap. The optimal choice is the subset that leaves me closest to the hit cap without being under it. The problem of finding this subset, however, is NP-complete, because it is the Knapsack problem - Wikipedia, the free encyclopedia . So a simple algorithm for true optimization is unlikely. And at that point, we're unlikely to give anything too much more useful than people can work out by hand (without excessive amounts of computation).

♦ Tecton · 12/01/10, 4:52 AM

Yeah, I've been reading around it elsewhere, it's a big problem to handle. I've removed the sheet I was working on, as I basically came up against a brick wall.

It was a good thought exercise for me, at least!

• Hamlet · 12/01/10, 7:03 PM

The only change I made was the hit rating effect on rotation. I think it's basically good for Cata release now. It doesn't have to be perfect of course, I'm sure a lot will change later, but I'm just trying to do everything I can think of right now, since I probably won't be working on it much for a few weeks at least.

Was there anything else I'd mentioned wanting to include?