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.

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:

beginarrayrl
textLevel & text+ Slots & textLevel & text+ Slots \ hline
text1st & text+2 1st & text11th & text+1 6th \
text2nd & text+1 1st & text12th & - \
text3rd & text+1 1st; +2 2nd & text13th & text+1 7th \
text4th & text+1 2nd & text14th & - \
text5th & text+2 3rd & text15th & text+1 8th \
text6th & text+1 3rd & text16th & - \
text7th & text+1 4th & text17th & text+1 9th \
text8th & text+1 4th & text18th & text+1 5th \
text9th & text+1 5th; +1 4th & text19th & text+1 6th \
text10th & text+1 5th & text20th & text+1 7th \
endarray

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:

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...}

Just use a sum of conditionals

I have recently implemented auto-updating spell slot numbers in a character sheet spreadsheet, and the only reasonable way I found to do it was write a bespoke formula for each spell slot level. For example, you gain a 5th level slot at caster levels 9, 10, and 18. Sure, you could find some non-linear function that matches this behavior, or you could just encode this as 3 conditional statements:

= (LEVEL >= 9) + (LEVEL >= 10) + (LEVEL >= 18)

This formula is for Google Sheets, and LEVEL should be replaced by the absolute cell ID containing your caster level. In your example sheet this would be $BX$27. Each conditional evaluates to TRUE or FALSE, and when you treat them as numbers by adding them together, TRUE becomes 1 and FALSE becomes 0. You will need to write a similar formula for each spell slot level. Note that for 8th and 9th level slots, which only have one conditional, you'll need to add a +0 just to convert the T/F to 1/0.

There is no formula for number of spell slots simple enough to be useful. Some numbers in RPGs are ad hoc and not based on consistent math, and this seems to be such a case.

There if no succinct mathematical function that does it, but we can construct a function for number of nth level spell slots for a level m caster by using an if function which takes two inputs, (a, b) and returns 1 of $a >= b$ and 0 if $b > a$. The number of 1st level spellslots a m level caster has becomes:
$$
2 cdot textif (m, 1) + textif (m, 2) + textif(m, 3)
$$
and so on. You can implement this in a spreadsheet by the IF() function (Works in Excel and Google Spreadsheet):

 =IF(LEVEL>=1, 2, 0)+IF(LEVEL>=2, 1, 0) + IF(LEVEL>=3, 1, 0)`

where LEVEL is the cell containing the casters level. And so on for the other spell slot levels.