I am trying to make a spreadsheet to track encounters, and I was wondering if there is any generalised formula I can use to calculate the average XP for any given Challenge Rating, or is it like character advancement in that there's no consistent formula for such? An approximation is also acceptible.
As pointed out by @Thomas Markov, the Table is odd. I expect that some rounding occurred to get "nice-looking" numbers, plus 2 bumps (CR 17 and CR 20), and an acceleration of the growth rate post CR 20.
I expected a quadratic curve, so went looking for an arithmetic progression, and it turns out that the increase from CR N-1 to CR N is close to
(N + N / 7) * 100, using integer division on N.
With an offset, we get the closed formula for CR N: .
|CR||Actual XP||Predicted XP||Error|
From there on, however, the formula gets much more inaccurate:
- There's a weird +1,000 bonus for CR 17.
- There's another +1,000 bonus for CR 20.
- The rate of growth increase from CR 21 onward.
I suppose further tweaks to the formula could fold in the bumps and increase in rate of growth, but at this point I expect it's just easier to use a look-up table.
Consult the "Experience Points by Challenge Rating" table in the Monster Manual and set up a VLOOKUP.
Page 9 of the Monster Manual has a table that tells you the exact XP of a monster of a given challenge rating. This table can also be found here for free in the Basic Rules
To integrate this into a spreadsheet, you can copy this reference table into your sheet as two columns, and use a VLOOKUP to pull the XP values for your chosen monsters. So you would first have your CR/XP reference table like this:
Then, you could reference this table using VLOOKUP. By inputting the CR on the left, we can have the XP calculated automatically by referencing the above table:
Assuming the reference table is in the cell range
D2:E31, the formula in cell B2 looks like:
A2 in the first argument means "check the reference table for the value in
A2", the second argument points to the reference table range, the third argument
2 says "once you have found
A2 in the left most column of the table, grab the value in that row from column
2", and the fourth argument tells the function to find an exact match for
A2 in the reference table.
Is there a closed form expression for the table? Probably not, but I don't think we need one to do what we need to do with spreadsheets.
We know more than just level and XP. We know the tier structure of 5e, and we know the DMG monster building rules.
|CR||Actual XP||Change in XP||Tier||Notes|
|5||1,800||700||2||T2 is a large PC power bump (2nd attack, 3rd level spells)|
|11||7,200||1300||3||1500/level with smoothing|
|17||18,000||3000||4||2500/level with smoothing|
|30||155,000||20000||6||extra little kick|
We can break it down into 4-6 level ranges: L 1-3 is +200 /level ish L 4-7 is +500 /level ish L 8-11 is +1000 /level ish L 12-16 is +1500 /level ish L 17-20 is +2500 /level ish L 21-25 is +8000 /level ish L 26-30 is +15000/level ish
Initially the increase in XP/CR doubles. In T3/T4 each window only gets about +50% XP/CR. Then after CR20, things change. If you look at monster building guidelines, CR 20+ creatures have a larger gap in HP and Damage per point of CR than pre-CR 20 creatures do; they appear to have just compressed things. That would account for a change in the curve's shape (on top of what would be "natural").
Going to the DMG's CR calculator, each CR adds 15 HP, and 6 damage per round, plus a small amount of AC/ATK/DC every few CR.
CR under 1 and 20+ aren't quite this neat.
In the CR 1 to 19 range, the CR's HP column caps out at 10+(CR+4)*15 (and the least value is 11+(CR+3)*15), and the damage at 2+(CR+1)6 (and the least value is 3+CR6).
At CR 20+, HP goes up by 45 per CR and damage by 16, exactly 3x what you gained from 1 to 19.
If we map CR 1 to 19 to 1.5 to 19.5 in terms of HP/damage, then CR 20 is 21.5, CR 21 is 24.5 all the way up to CR 30 is 51.5.
That explains the anomaly at level 20 and above.
In short, I think that monsters and PCs where balanced with a presumed level of HP and damage output. CR X creatures where designed to be a certain amount difficult for Level X PCs.
We can then look at how tough CR X+/-Y creatures are compared to CR X creatures. How many more/fewer resources will they need to be defeated?
Standard encounters are worth 1 unit of XP. Easy are 1/2 of that, Hard 1.5x that, and Deadly 2x that. Playtesting (or models) tells you where these thresholds lie.
A CR 2 creature is 2.25x CR 1 XP; it is beyond deadly. It being 400 instead would put it at deadly, and state it was an appropriate encounter for 4 level 1 PCs. Instead, it is a hard to deadly encounter for 5 level 1 PCs.
That kind of tweaking (especially at low levels) wouldn't be expressed in a simple formula.
I mean, there is no reason why level 10 is 5900 instead of 6000, unless they where trying to state that it was a deadly or hard encounter for some other level of creature, and that 100 points mattered; 1.6% more or less XP is not usually worth talking about. Or maybe it was just a joke, to make the table less smooth.
Take this section:
|CR||Actual XP||Change in XP||Tier||Tweaked||Tweaked Change in XP|
it would be hard to notice, in play, the difference in the original and the tweaked table, except in corner cases of "does this count as a hard encounter for X level Y PCs".
The same "simplified" can be done for L 1 to 5:
|CR||Actual XP||Change in XP||Tier||Tweaked||Tweaked Delta|
a slightly smoother curve that is at most 100 points off the actual XP table.
Then you playtest, and find that "no, 4 L 1 PCs vs a CR 2 is a bit too hard", so you boost CR 2 XP to 250. And similar tests drop CR 4 down to 1100.