[RPG] the formula behind each level spell slot progression that I can use in a spreadsheet

dnd-5emulti-classingspell-slotsspells

I have been trying to find a way to find the formula behind spell slot progression on the multiclass caster table, to add a spell slot table to this sheet with the best example being on the last table group at the end of the second page and there seems to be a pattern, but I can’t seem to find what the formula for it would be. Especially because many lower level slots cap out at a number around 3 after only a few levels.

I have been linked to this post a few times because it has a similar title to my question, but it does not have the answer I am looking for. If anyone has the formula for the individual level slot progression per level that would be greatly appreciated.

What is the formula that can be used to generate the spell slots for each level on the multiclassing spell slot table?

I would prefer the mathematical function that gives it, so it could be used in other projects should the time arise. But if there is no other solution a spreadsheet specific answer would be appreciated.

Best Answer

There is no General Formula

Or at least not one that is pretty.

Let's start by looking at how many spell slots are gained at each level:

\begin{array}{rl|rl} \text{Level} & \text{+ Slots} & \text{Level} & \text{+ Slots} \\ \hline \text{1st} & \text{+2 1st} & \text{11th} & \text{+1 6th} \\ \text{2nd} & \text{+1 1st} & \text{12th} & - \\ \text{3rd} & \text{+1 1st; +2 2nd} & \text{13th} & \text{+1 7th} \\ \text{4th} & \text{+1 2nd} & \text{14th} & - \\ \text{5th} & \text{+2 3rd} & \text{15th} & \text{+1 8th} \\ \text{6th} & \text{+1 3rd} & \text{16th} & - \\ \text{7th} & \text{+1 4th} & \text{17th} & \text{+1 9th} \\ \text{8th} & \text{+1 4th} & \text{18th} & \text{+1 5th} \\ \text{9th} & \text{+1 5th; +1 4th} & \text{19th} & \text{+1 6th} \\ \text{10th} & \text{+1 5th} & \text{20th} & \text{+1 7th} \\ \end{array}

So looking at this, there are a few patterns that stand out:

  • For Tiers 1 & 2 (Levels 1-4 and 5-10) the very first level gives 2 spell slots of the type gained at that level. Then, the second-to-last level gives two types of spell slots, not just one.
  • In Tier 3 (Levels 11-16), you only gain spell slots every other level, on the odd levels. Nothing is gained on the even levels (except, usually, for class features).
  • In Tier 4, (Levels 17-20), the first level gains the 9th level spell slot, but then all the other levels give lower level spell slots.

But these patterns don't give us enough to work with in terms of writing a clean formula. The table is built from deliberate decisions about how the designers wanted to advance power at each stage of the game, not a single encompassing formula.

So if you're trying to put spell slot numbers into spreadsheet software, you'll need to punch the table in manually somewhere.

Spreadsheet Math

This is what I came up with:

Spell Slot Spreadsheet

In Cells M3 to M11, the formula used there is

=IF(OFFSET($B$1,$M$2,MATCH(L3,$B$1:$J$1)-1)<>0, OFFSET($B$1,$M$2,MATCH(L3,$B$1:$J$1)-1), "")

All of the other cells are just flat values.

That formula will fill in the correct number of spell slots based on what is punched in in M2, the cell next to LEVEL. All you have to do is supply the correct "effective" caster level to that cell, based on the total caster level of the character, multiclassed or single-classed.

The idea is that the array on the right, with the complex lookup formula, can be copied (with the references being updated) into each of the character sheets you're trying to generate, and it'll generate the correct values for each character. All you then have to do is just copy down the canonical table somewhere in your workbook so you can use it as a reference.

And if you're feeling really frisky bold, you could even homebrew alternate spell slot progressions into that table, and the formula would pick up the homebrew instead, with minimal effort needed to make changes... Just saying...

Related Topic