I think just about everyone on these forums either creates their own spreadsheet, or uses at least one of the many spreadsheets available through these forums. It's fairly obvious why you'd use them, the simple desire to be able to do what what you do, well. However, some are far more used than others. The standard design seems to be along the lines of Chalon's old rogue sheet (I'm sure his was based off something earlier), however some (specifically Vontre's mage spreadsheet and the shadowpriest sheet) focus more on cycle comparison than gear/spec. For those of us planning new spreadsheets, or those looking to improve their current ones, what exactly makes a spreadsheet good from technical and visual design perspectives?

Obviously accuracy is important, anyone can make up math to show what they want to show while ignoring important factors, but any spreadsheet that isn't somewhat accurate is worth the paper it's typed on. The question is how accurate? Once the average damage (or healing) of each ability is calculated, how much more specific do you need to get for things like the multiple "proc uptime" effects (ie. Flurry, Imp Shadowbolt, etc.) Specifically in the case of Moonkin, the calculations required for the effects of Natures Grace on spell cycles required more work than the entire gear/buffs sheet for a change of ~2.5% off of a sloppier but much shorter formula.

Then there's user input, most of which seems fairly obvious how much is desired around here: Complete lists letting you choose your exact gear, enchants, buffs, talents, and now gems (Meta's are sometimes an issue due to understanding proc rates and haste effects ...) Should something like a "Judgment of Wisdom uptime", "DPS of the Shadowpriest in group", or even "Shamans rotated through group for Bloodlust/Mana Tides" be included, or is this something that can just be estimated? After all, your shadow priest may just suck randomly and vary from 600-900 DPS depending on fight.

The readout is also a bit of a concern, just how much information do you want to come out of this? DPS DPM HPS and HPM are clearly important, as is some sort of an "optimal cycle" option (either as an input option, or a displayed result). 'Next Stat' seems to be somewhat popular, considering I'll be putting items in for the stats/buffs anyway, would it be a good idea to take 'next stat' a step further and include an option for all items that would be upgrades in that slot (a la the armory) or is all of this unnecessary because you can just cycle through all the known items to find the one that gives the best result?

For Tanking readouts, would you like to see something other than Total Mitigation and Burst Mitigation (ignores avoidance/block)? I thought about Magical Mitigation and Burst Magic, but aside from the removing 0% resisted with ~265 resist (I think) I don't think any useful thresholds exist so it's really just "more is better" in that area, right?

Visually ... no offense to some of the sheets out there, but having Gear, Buffs, Talents, and Cycles on the same page is excessive. By the same token, having them all on different sheets (and then some) makes spec changes a hassle, I'm really not sure where the balance is here. I also think (personal opinion here) that the places where user input is required need to be able to be visually separated from formulas, headers, and blank space without resulting in too many color contrasts that just look unappealing.

And then there's graphs and other images for display versus the standard list readouts, Hybrid multi-sheets, New player accessibility, and probably a good dozen things I haven't even thought of. I guess my question is, if you could describe the perfect spreadsheet, what would you include?

I think part of the problem with most spreadsheet designs is that the primary concern is always just figuring out the math involved - the user interface to the spreadsheet for selecting gear, talents, buffs, etc is usually just tacked on as an afterthought. Quite frequently I find user input cells mixed up in the middle of calculation cells, making it a chore to scan the sheet to find all the particular places that input needs to go.

Then on the other extreme of that are the spreadsheets where all the inner workings are completely hidden, making it difficult to find out how the designer was calculating a particular proc or effect.

I think you just have to be extremely clear with what you want to end up with. A lot of the sheets I've come across recently have started out as "Rogue cycle" sheet for example. However, as the popularity of said sheet increases then the author gets ambitious and tries to write say, an item handler or, a gem selector. I can't say I've seen many successful sheets which have taken a different direction from the original plan without the whole layout, design and basically the entire sheet to be redone (read: a new sheet written)

I guess my only advice on this thing as an end-user is be clear on your goals, and don't try to bodge another function onto something it's clearly not designed for. I think through these forums however it's hard not to get a lot of support and a bug-testing squad worthy of monthly paychecks.

Speaking as someone who was involved with the rogue spreadsheet for a long, long time, I can trace the evolution of it fairly well. I'll be inlining relevant quotes from emails to help illustrate.

Rogues, remember the 16/25/10 bad old days?

So, initially, the sheet was designed around the assumption of a particular cycle of CP usage (3/5/5 in the swords case, 5/ in the daggers case), as those cycles were believed to be the best at the time (and indeed, I don't recall anyone ever finding a general-case cycle that was better for 1.0 specs). Not only that, but the sheets were seperate; you had a daggers sheet and a sword sheet, each slightly different, each maintained by different people (albeit in the same guild.) This situation persisted for a few months, with Chalon and Valar making small improvements, adding new gear, but keeping the sheets seperate.

As a sidenote, during the time-frame when there was only Chalon's daggers sheet and Valar's sword sheet wasn't released yet, a few rogues (myself, Lrigatonmai, and Bluur) were working on a sword sheet. It got shelved when Valar's sword sheet released because, well, his was better.

In July, I wrote up the CP/Cycle sheet's first version.

The CP/cycle sheet I sent out was the root of what would eventually become the cycle analyzer sheet in pf's revamping of Chalon's spreadsheet; it didn't have gear selectors or anything like that, you just punched in crit, AP, hit, and some talent choices, and it spat out information about potential cycles. In other words, rather than assuming things about 3/5/5 and 5/0, it allowed for some proof of those cycles vs. other cycles, and allowed you to see the effect of talent points in things like Ruthlessness or Relentless Strikes.

Around this step in development, hemo was getting more popular, and Taikero developed a hemo variant of Valar's combat swords sheet.

Prescient. By about a month. :p

This is also roughly the time when Chalon and Valar stopped maintaining the sheets (they left the game); Pf had taken over the sheets, and was beginning work on updating them to clean them up. His obvious next step was to integrate the dagger and sword sheets into one. The less obvious and far, far uglier step was the integration of my energy/CP backend into his gear/whitedamage/procs front end.

A few weeks proved to be a lie, as two days later, an initial merged version was floating around. Of course, there were still major bugs in it, especially in my part of it. Not to mention that CP/cycle was visually so intimidating that people weren't generally willing to play with it.

Oops.

At this point a lot of our discussion focused on mathematics and modeling, but not to the exclusion of UI; one of the things Pf really wanted to avoid was sheet bloat.

About 5 days later (October 2nd), we'd added in most of the talent options we'd just argued against, fixed all the bugs related to those talent options (less Ruthlessness makes your DPS go up... which wasn't actually a bug, it was just a counterintuitive side-effect of the modeling method I was using, but one which was fixed by changing to a better model). We also added in some Seal Fate stuff; it was ugly then, and it's still ugly. At this point, the sheet was basically fixed in form until the TBC sheet began to be worked on; all the changes from here to there were mathematical and hidden rather than visible to the user.

Some sage thoughts on spreadsheets and bug-fixing.

A few weeks later, as TBC/2.0 info was released more widely, work began on a TBC version, modeling things like Combat Potency, Surprise Attacks, and the like. An initial version was completed November 2nd, modeling Mutilate, SA, and CP (with the information we had available to us at the time.)

Around this time, my involvement in the sheet lessened, but in the next month, Pf finished up socketing and TBC updates, and then he also stopped maintaining the sheet. Currently updates are handled by a rotating cast of thousands, but the sheet still looks more or less like the one Pf and I roughed out in September of 2006.

This post is already quite long, but I guess the point I want to make is that these things evolve over time; there are bits in the rogue spreadsheet from various places. There are elements from Chalon and Valar's sheets. There's a (much modified, much improved) version of my original CP/cycle sheet. There are even small bits of my enchant spreadsheet incorporated there. For all that the version you see today looks different, at its heart, it's still the same sheet as the one Chalon emailed me over a year ago, following the same vision; just a little more accurate, a little more complicated, and probably quite a bit bigger on my hard drive.

Something I've had good experience with is having the math open to those who wish to check it, while hidden enough as to not interfere with the regular use. In my case, it was quite simply making the text be almost the same color as the background, hidden roughly 1 Page Down away Not the most elegant, but it worked.

Having the math open like this is crucial for error checking, and input regarding various things. It's also interesting, just due to having a more direct sense of what changes when, and why.

Regardless of what you're trying to convey to the user, having good names, labels and terms help tremendously.

I'd also agree with that, before you fully flesh your spreadsheet out, basically planning what you want to input / output is a good idea. Having a cool idea 4 months after you've created the spreadsheet often results in a massive workload, if you haven't planned for it, or created an easily enough extendible backbone. In short, planning is king.

While the user interface is, of course, important, you have to be sure you have something interesting to display with it. Due to this, my advice would be not locking the interface, etc, before you've got the code up and running. Obviously, having future design in mind often pays off. I'd just not let it have top priority.

Well, that's some of my random thoughts. Back to work.

I like to think that the Rogue Gear spreadsheet that Aldrinna and I put together--http://elitistjerks.com/showthread.php?t=11882-- attempted (to some degree) to learn from past spreadsheets. I really like that the math is laid out in it's own pre-computed sheets where you can look at the cycles and calculations easily. Similarly, I attempted (although I was foiled by feature additions/updates) to keep a standard naming convention and to use it gratuitously throughout the sheet.

One thing that I think is extremely valuable is to do a full 'proof of concept' build (this is the stage most of the spreadsheets are at) and once it is stable, a complete rebuild at 'production level'. This really lets you clean things up and spot errors.

Finally, one thing that I think would be helpful for all the spreadsheets, is to display formulas being used in a symbolic language. When the Rogue Gear sheet (read, when Ald. and I get more time) moves from proof of concept to something more stable, I intend to add descriptions for each calculation into the sheet. This makes future updates much easier as formulas change and better data becomes available.

my biggest pet peeve with spreadsheets, is that i've found that theyre so inaccurate or biased toward one or another spec. For example, i went looking for a resto druid spreadsheet and i found one that was written post BC. I thought, 'great, i can finally start crunching numbers to try different things out.'

however, it was so biased toward dreamstate as to leave out 75% of the tree druid talents yet INCLUDED all the balance spec talents. Then it still went so far as to compare tree druid heals to dreamstate heals, even though over half of the tree druid talents were not represented whatsoever. Even the ones that were represented, were represented incorrectly.

What that meant was, the graphs would always come out skewed against TOL, no matter what you did, simply because TOL was misrepresented so badly. So, however much i'd like to use a spreadsheet, i'll always be leery of using one for this reason.

Don't mistake a particular spreadsheet for the trend for all of them; I know the rogue sheets have always been very clear about any bias present and have generally moved towards being more widely applicable (see the arc for the rogue DPS spreadsheet; initially only for combat daggers, it moved to a general combat sheet and then to a sheet capable of supporting basically any common rogue build, or Vontre's mage sheet, which aims to support arcane, frost, and fire builds equally well).

Ah design.

I think my "ShamStats" is the third edition of spreadsheets I've been kicking around for a while.
The initial "ShamanDPS" sheet was looking at various talent specs, given base stats. Couldn't quantify non-output talents, but that was ok.

Went on to a shamgear one little bit later on, which kinda failed miserably. It was reborn, however, as the mammoth ShamStats, which is more designed around my frustration with Emmeralds feral gear listing having all the items on one page, and inspired by the Agi Equiv Hunter calcs. At some point I shall simplify the front end pages so you only really need to do stuff to the first three before looking at the item listings (and I try to make everything that nice pastel yellow if it needs user input).

From my experience, functionality and usability are never easy to plan out, and tend to evolve over time. Having notes on the calculation pages work out well too. As you can see from the shaman elemental thread, there has been a little interest in it, and people have managed to pick up mistakes I make (Rather frequently I might add ).

Most modern spreadsheet applications are pretty bad for evolving complicated things because they entirely intermingle data and calculation. It makes it very hard to make changes to the calculations, and it makes it very easy to introduce errors due to missing the formula in one cell.

Honestly, if you are looking at doing very complicated spreadsheets that expect to have to alter over time, and you want flexible presentation you should look at getting an old copy of Lotus Improv, or Quantrix Modeler (though in my experience Quantrix is a bit less stable than I would like when working with large numbers of dimensions). Quantrix has a video demo that is pretty good.

(For you software engineers out there, those programs basically introduce an MVC type paradigm to user level spreadsheets)

Sorry if this is going a notch off topic, as it's more practical then theoretical, and it's a bit "l2spreadsheetcraft", but I don't suppose anyone would mind educating me on how (or whether it is even possible) to use Google Docs to perform an evaluation similar to:

FOR c2..zz99 { IF a#.Label = i[MATCHa#]#.Label THEN b.Label+=i4 }

That is, raid events are columns (instead of rows), with row 4 being the DKP value of the raid and rows 5+ being an unordered list of attendees.

Alternatively, the point is to attempt to manage a spreadsheet DKP system (preferably with copy and paste from NRT's list of names of attendees), if there's already some solution to that problem in place. I was thinking shared google.docs would be a handy hosting solution... if there's some conversation on this already, my apologies, I did search.