Spreadsheet that can optimize Gear and Spec?

Bury · 01/07/07, 11:22 AM

We’ve seen many spreadsheets that print out DPS when they’re told your favorite talent spec and your gear/stats. That’s fine and all, but have we seen spreadsheets that take this concept and show you what an optimum combination of gear and spec would be? If so, where can I download it? =]

In such an optimizing spreadsheet, you would be able to put in a list of gear that you have, and a list of popular talent specs. This way you can compare gear that may be similar (Briarwood Reed or Eye of the Beast?), and across different talent specs (Ring of the Eternal Flame or Band of Vaulted Secrets?). So you put in a list of all of your gear, and then the spreadsheet summarizes neatly:

Highest DPS of X
is achievable with
talent spec S
set of gear {Head1, Shoulder6, Wand32, etc}
cast cycle Z
sustainable for T seconds

If for some reason we didn't like the optimal solution, we could look at solution2, solution3, constrain it by only looking at builds that include Imp Scorch/Meditation/WhatHaveYou.

FAQ:
This is a stupid idea! You can do the same thing just by manually typing in all possible combinations of gear and spec!
You could definitely do that. At the same time, a more advanced spreadsheet would be able to do all the work for us and save us a big headache.

What’s the point?
I like to know what my char’s theoretical optimum gear, spec, and the results from such—even if I elect to choose something suboptimal because it seems more fun.

Of course this doesn’t exist, why don’t you make it?
I haven’t figured out how to do it yet, and if someone smarter has a working version already, I’d rather not re-invent the wheel. This is a bit beyond the scope of anything I’ve done in Excel and would probably take me quite a few hours/days/weeks/centuries.

Is this too complex for Excel to handle?
I have no clue.

Smithist · 01/07/07, 12:00 PM

It may not be too complex, but it really wouldn't be too hard to compare stat totals from different gear sets in a more traditional sheet, and it seems unlikely that a sheet catering to such specific needs is out there.

Evalara · 01/07/07, 12:14 PM

I've long thought that it would be possible, at least for spec. If you can express the effects of talents, and the constraints of which talents you can choose as linear equations, you can use well-understood linear programming techniques (which can be easily faked in Excel using the Solver) to find the optimal combination. If I had been playing WoW 3 years ago when I was still in college I would have certainly attempted this, but at the present time it doesn't seem worth the trouble.

Never mind that the result would always be contingent upon certain assumptions about the encounter, etc...

Exewut · 01/07/07, 12:23 PM

It's quite easy,
All you have to do is add solver in the end.

Let me demonstrate:

You need a window to fill in base stats in excel
strenght (a1)
+AP (a2)
+critrating (a3)

talent 1 (b1) Out of 5
talent 2 (b2) Out of 5

ilvl (c1) The amount of points you want to optimise
talentp (c2) The amount of talent points you want to optimise

then you need to add limiting factor for the items (store in d1):
Let's use the ilvl formulla:
[(strenght*1)^1.5 + (AP*.50)^1.5 + (critrating*1)^1.5]^(1/1.5)
which would end up in excel as:
[(A1*1)^1.5 + (A2*.50)^1.5 + (A3*1)^1.5]^(1/1.5)

now we add the limiting factor for the talents (store in d2):
B1+B2

Now we make the dps formulla:
talent 1 gives us 1 crit per talent point
talent 2 gives us +20 AP per talent point

(strenght*2+AP)/14+20*talent2)*(critrating/20+talent1) =dps

And now we use solver to:
MAX (strenght*2+AP)/14+20*talent2)*(critrating/20+talent1)
WHILE [(strenght*1)^1.5 + (AP*.50)^1.5 + (critrating*1)^1.5]^(1/1.5) < D1
WHILE B1+B2 < D2
USING strenght (a1)
+AP (a2)
+critrating (a3)

What will happen now is that Excel will adjust strenght, AP and +critrating to find the MAX DPS.

Basically it's just adding solver to the current existing DPS Excel files. You need to install solver separately via the tool menu though, I'll never understand why Microsoft didn't install that (very useful) tool in the standard install.

[e] The BIG problem is that I have no idea how to use this tool in a sheet all the time, I can only calculate the optimized number if I manually start up the solver tool, select the variables. I would love to somehow imbed it in my sheets.

[e2] Casting cycles are HORRIBLE to do. For a moonkin:
Moonfire
if crit: 2,5starfire*hasterating and cast moonfire again in 12s, if resist: moonfire, else 3starfire*hasterating

and then the second step even becomes more complicated. The same with lacerate for example, how do you keep a 5stack lacerate going? Do you refresh it 2s before it fades, so if it resists you can refresh it again, or do you do it right at the end?... Depends on your rage (solo, boss tanking, trash, raids,...), bosslvl, +hit items, debuffs on the mob, % of mobs hp, your maul/mangle/swipe dps,...
A nightmare

Bury · 01/07/07, 12:29 PM

Originally Posted by Evalara

I've long thought that it would be possible, at least for spec. If you can express the effects of talents, and the constraints of which talents you can choose as linear equations, you can use well-understood linear programming techniques (which can be easily faked in Excel using the Solver) to find the optimal combination.

Let me see if I understand you correctly. We would formulate a linear equation for each talent spec with inputs like spelldamage/crit/int, and an output of some DPS number. Then we could simply plug in each piece of gear in a set, like for Mish'undare in the set of Head, and then take the maxDPS for the set. From there we could add up the maxDPS from each set to come up for a maxDPS for that particular spec. Then we could see which spec has the highest dps and say that is the highest dps spec with optimized items a,b,c.

Then the challenge would just be in translating specs like 28/0/23 into linear equations. Does it make sense, though, to calculate DPS per item? Seems like a strange concept to me.

Kalman · 01/07/07, 12:33 PM

While that tells you the optimal distribution of item stats, it doesn't tell you which specific items to use.

After all, I know I'd like to (approximately equally; there's some math behind this statement which I won't repeat here) spread my points across AP, hit, crit, and agility more or less equally. Too bad items don't do that, isn't it?

The major problem with using solver in this fashion is that it ideally looks for a non-integer solution to the linear programming problem you've setup, while by definition we need not only integer solutions, but solutions that are mixes of specific integers. I don't care that I want X+Y+Z AP+crit+hit; I care what, of the gear available to me (either right now or in the game), will provide best results.

A friend of mine did once compose something like this for paladin healing gear, based around optimal output under a given set of conditions, which would choose the right gear. It mostly worked, by using a constraint that forced a piece of gear's presence to be binary (present or not) and a class of gear's presence sum to be 1. It mostly worked, except when it didn't.

Exewut · 01/07/07, 12:35 PM

Originally Posted by Kalman

It mostly worked, by using a constraint that forced a piece of gear's presence to be binary (present or not) and a class of gear's presence sum to be 1. It mostly worked, except when it didn't.

This is a great idea, I hope he doesn't mind if I borrow it.

[e] I believe trying to solve these problems via integers gives even brilliant ma thematic minds headaches (as it is impossible to do I think).

Kalman · 01/07/07, 12:36 PM

Originally Posted by Exewut

Originally Posted by Kalman

It mostly worked, by using a constraint that forced a piece of gear's presence to be binary (present or not) and a class of gear's presence sum to be 1. It mostly worked, except when it didn't.

This is a great idea, I hope he doesn't mind if I borrow it.

Go for it; like I said, it mostly worked.

(Getting it to work all the time wasn't worth the effort.)