DCSS Spell Lab

[Caethan]

So after the rousing success of the Weapon Lab, I began putting together a Spell Lab to get some similarly useful information for casters. I just finished the first (simple) implementation, which just calculates spell success rates based on int and skills. I've found it useful to get *real* spell success rates as a percentage instead of just a short description. I haven't yet added the effects of armour and shields or various enhancer effects, though those are planned. Also planned are spell power calculations.

It's currently available on google docs: https://spreadsheets1.google.com/spreadsheet/ccc?key=tFyCrh9-muLmkSZY6u1Sjmw

You'll have to make a copy (File --> Make a copy) of my version to try out your own character's build.

Incidentally, while building and testing the spreadsheet, I found an interesting bug in the spell power calculations that unduly penalizes three-school spells. (https://crawl.develz.org/mantis/view.php?id=4107) This bug is reproduced in the spreadsheet for the moment, though hopefully it will get fixed in trunk soon.

[Galefury]

Cool. Could you also add spell hunger? It's a more simple calculation than success and power, but would be helpful to have in there.

[galehar]

Great work, thanks a lot!
I've fixed the bug you reported, thanks for that too. You did a very good job with the spreadsheet, it looks like most of it can be reused for other purposes. You've put a lot of data which seems to be irrelevant for calculating spell success (races, slots, piety). Any reason you did that? Also, max piety rank ranges from 161 to 200, not 200 to 400.
I'm curious, how did you fill the random table? I guess you wrote a program to crush the numbers (I don't think there's another way to do it). Can you upload the source?
Let me also give you the same advice I gave to Danr. Use named ranges. It's very simple, and it makes writing formulas and maintaining spreadsheets much easier.
Thanks again!

[Caethan]

Great work, thanks a lot!
I've fixed the bug you reported, thanks for that too. You did a very good job with the spreadsheet, it looks like most of it can be reused for other purposes. You've put a lot of data which seems to be irrelevant for calculating spell success (races, slots, piety). Any reason you did that? Also, max piety rank ranges from 161 to 200, not 200 to 400.
I'm curious, how did you fill the random table? I guess you wrote a program to crush the numbers (I don't think there's another way to do it). Can you upload the source?
Let me also give you the same advice I gave to Danr. Use named ranges. It's very simple, and it makes writing formulas and maintaining spreadsheets much easier.
Thanks again!

The extra data is planned to be used when I add some more calculations: racial armour, armour and shield penalties, and Vehumet support.

The random table was just built with a simple python script: it's available at http://pastebin.com/ay3eyXhe.

Thanks for the advice!

[asdu]

Thanks, that looks like it could save me a few headaches
However, is it possible to save it and use it offline? Unlike Danr's spreadsheet, when I try to edit this offline (with Open Office), values don't get updated and the drop-down menus don't work (I don't know the first thing about spreadsheets ).

[Caethan]

I haven't tried it with Open Office or Excel yet; I'll see if I can make it so the downloadable version works properly tomorrow.

Also, spell hunger and spell power calculations have been added. Still to come: armour and shield penalties, enhancer effects (= of ice/fire, ] of the archmagi, staves, etc.), and Vehumet support.

[Galefury]

Awesome. Armor penalties will be very useful.

Don't forget wizardry. Probably easiest to just make checkboxes for staff, ring, and other ring. Having to manually enter wizardry level would be pretty annoying. I can never remember what gives how much.

[Caethan]

Awesome. Armor penalties will be very useful.

Don't forget wizardry. Probably easiest to just make checkboxes for staff, ring, and other ring. Having to manually enter wizardry level would be pretty annoying. I can never remember what gives how much.

Armour and shield penalties are now included in spell success rates.

[Caethan]

And it now supports enhancer staves/rings and wizardry effects. Let me know if I forgot any equipment that could have an effect.

[Galefury]

I think you have equipment covered. Some forms also have an effect by the way. Blade Hands lowers spell success, and Necromutation grants a double necromancy enhancer. Also being a mummy grants you necro enhancers at certain levels (or maybe just one, I'm not sure). There may be some I'm missing.

[galehar]

List of enhancers:

• Necromancy: staff of death: +1; mummy XL >= 13: +1; XL >= 26: +2; Lich form: +1.
• Fire: staff of fire: +1; ring of fire: +1; ring of flames: +1; in a rain cloud: -1
• Ice: staff of cold: +1; ring of ice: +1
• Earth, Air, Conjuration, Enchantment, Summoning: staff of school: +1
• Poison: staff of poison +1; staff of Olgreb: +2
• All schools: Lantern of shadows: -2; Archmagi: +1

For elemental schools, enhancers also work as negative enhancers for the opposite school. Standing in the rain works as an ice enhancer! It probably shouldn't...

[Caethan]

OK, all of the enhancers/effects that have been mentioned should be available now. I also went through and did damage dice calculations and average damage calculations for most damaging spells.

[Galefury]

Wow, this is getting more and more awesome. You're missing Tornado damage by the way.

Is it possible to also make this work for trunk? No more triple school bug there. Also Berserker Rage is gone and Tornado was nerfed.

[Caethan]

Wow, this is getting more and more awesome. You're missing Tornado damage by the way.

Is it possible to also make this work for trunk? No more triple school bug there. Also Berserker Rage is gone and Tornado was nerfed.

Tornado damage is... nontrivial to calculate. Along with Orb of Destruction, Ball Lightning, Chain Lightning, and Static Discharge, I'm trying to figure out the best way to present their damage, since they're all so highly dependent on other variables.

It could probably be adjusted to work with trunk. Is there a changelog somewhere that I could look at to see any relative changes I would need to incorporate?

[Galefury]

So far there just is the commit log, which is not really suited to finding changes to spells. I think the changes I listed are the only ones so far, but I may very well have missed something. Maybe galehar or another dev knows better. The effects of spellcasting skill might change at some point, which would be pretty big, but so far they have not.

For IOOD I would just list the full damage (possibly mentioning lowered damage at short range in a note). For lightning spells I would list single zap damage and maybe add a note that multiple hits are possible (or very likely for some of them). Tornado lasts for 6 standard turns. Average damage is always the same, but how many dice rolls are done depends on how many actions you take in that time. Also AC applies to it twice (over the total duration, not per turn). I would only list average damage for Tornado, either per standard turn or total, with a note which one is listed. Also having a note for storm spells that mentions the damaging clouds they leave might be nice.

Edit: I just remembered that the irresistibility of the storms, bolt of maga and throw icicle was recently nerfed. So far this stuff isn't mentioned on your sheet anyway, so this doesn't really matter I guess.

[galehar]

So far there just is the commit log, which is not really suited to finding changes to spells. I think the changes I listed are the only ones so far, but I may very well have missed something. Maybe galehar or another dev knows better. The effects of spellcasting skill might change at some point, which would be pretty big, but so far they have not.

For IOOD I would just list the full damage (possibly mentioning lowered damage at short range in a note). For lightning spells I would list single zap damage and maybe add a note that multiple hits are possible (or very likely for some of them). Tornado lasts for 6 standard turns. Average damage is always the same, but how many dice rolls are done depends on how many actions you take in that time. Also AC applies to it twice (over the total duration, not per turn). I would only list average damage for Tornado, either per standard turn or total, with a note which one is listed. Also having a note for storm spells that mentions the damaging clouds they leave might be nice.

Edit: I just remembered that the irresistibility of the storms, bolt of maga and throw icicle was recently nerfed. So far this stuff isn't mentioned on your sheet anyway, so this doesn't really matter I guess.

From memory, I don't think you've forgotten anything. Tornado's duration can vary a bit with power. With high skills and a staff of air, I think 7 turns should be possible.

[dolphin]

Can a mod sticky this before it gets too lost?

[galehar]

Can a mod sticky this before it gets too lost?

Good idea. Done.

[XuaXua]

Seeing as how discussion of this and the Weapon Lab really only relate to use of, or glitches or updates or revisions, I'm of the opinion that there should be a forum for Crawl Utilities and both labs should be moved there as well as anything else that might crop up in said fashion.

[galehar]

Seeing as how discussion of this and the Weapon Lab really only relate to use of, or glitches or updates or revisions, I'm of the opinion that there should be a forum for Crawl Utilities and both labs should be moved there as well as anything else that might crop up in said fashion.

A forum with 2 threads? I don't think so. On the other hand, they would fit better in the contribution section than the general advice forum.

[Galefury]

People looking for something like this wouldn't look anywhere in the back room, but here in the advice section. So I don't think they would fit in the contributions section. If these get moved they should be added to useful links or a new "list of cool tools" thread.

[Caethan]

An updated version is now available, which incorporates damage reduction from monster AC and resistances for most spells. I'm still working on a couple of them, hopefully they'll all be done by the end of the weekend.

[Roy]

Is this spreadsheet still accurate for 0.11? If not, do you know what is wrong?

Edit: Well, for one, it doesn't accept fractional numbers...

I'd like to update it, but I'm not really sure of all the places that it's wrong.

[Caethan]

It hasn't been updated in quite some time. I'd be happy to pass ownership to you if you'd like to take over. I haven't played much DCSS for a while, so I'm not sure what kind of changes have been made.

[monty]

Perhaps this should be un-stickied until it's updated? I clicked on it hoping I could use but I'm not sure that .08 spell failure bears all that much resemblance to trunk.

[Grimm]

Good idea. Anyone willing to take this over? If I hear nothing in a few days, I'll unsticky it.

[Siegurt]

I've got my own DCSS spreadsheets that I haven't updated since .12 otherwise I'd probably be up for it

[Caethan]

Give me a couple weeks to finish a major work project and I'll see what I can do about updating this for 0.13 and 0.14.

[Grimm]

This thread has been unstickied.