7
\$\begingroup\$

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.

\$\endgroup\$

3 Answers 3

9
\$\begingroup\$

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: 100 + \sum_{1}^{n} (i + i/7) * 100.

CR Actual XP Predicted XP Error
1 200 200 0
2 450 400 -50
3 700 700 0
4 1,100 1,100 0
5 1,800 1,600 -200
6 2,300 2,200 -100
7 2,900 3,000 +100
8 3,900 3,900 0
9 5,000 4,900 -100
10 5,900 6,000 +100
11 7,200 7,200 0
12 8,400 8,500 +100
13 10,000 9,900 -100
14 11,500 11,500 0
15 13,000 13,200 +200
16 15,000 15,000 0

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.

\$\endgroup\$
6
  • 1
    \$\begingroup\$ Have you tried looking to see if there is some external factor involved? Such as how many combats are expected between levels? Most levels need a different amount of XP and it sounds to me like that might have something to do with it. \$\endgroup\$
    – SeriousBri
    Oct 24, 2021 at 12:41
  • \$\begingroup\$ Thanks, this answers the question exactly. \$\endgroup\$ Oct 24, 2021 at 14:17
  • 1
    \$\begingroup\$ @SeriousBri: That's an interesting observation, the experience thresholds also show some kind of tailoring, unfortunately, so it would take some experimentation to derive a formula back: if you divide the increase in level by the square of the new level, you get as low as 65 (from 1 to 2) and as high as 208 (from 5 to 6). It's messy :( \$\endgroup\$ Oct 24, 2021 at 17:28
  • \$\begingroup\$ If you look at the DMG monster building math, at level 20+ the HP/level (etc) scales way up. I suspect mapping HP/Damage to a different value, then mapping that value to XP and to Level, would work better. \$\endgroup\$
    – Yakk
    Oct 25, 2021 at 18:08
  • \$\begingroup\$ Also, the raw differences have a bunch more pattern than your version does... \$\endgroup\$
    – Yakk
    Oct 25, 2021 at 18:12
8
\$\begingroup\$

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:

CR XP
0 0
1/8 25
1/4 50
1/2 100
1 200
2 450
3 700

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:

A B
1 CR XP
2 3 700

Assuming the reference table is in the cell range D2:E31, the formula in cell B2 looks like:

=VLOOKUP(A2,$D$2:$E$31,2,FALSE)

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.

\$\endgroup\$
7
  • \$\begingroup\$ Oh, I'm using org-mode's spreadsheet functionality, by the way. \$\endgroup\$ Oct 24, 2021 at 1:11
  • 1
    \$\begingroup\$ I think the literal answer to the question is no, there isn't an equation (other than a best fit line approximation). But, just like the character advancement table, this is easily handled with a lookup table as discussed in this answer. \$\endgroup\$ Oct 24, 2021 at 2:24
  • 2
    \$\begingroup\$ Technically this is not answering the question that was asked. The question is whether there exists a generalized formula, not a way to make a lookup table. Is there something like XP = n+(CR*x) where you specify what n and x are? The answer may be, No, there's no such formula. But that's the question being asked, technically at least. \$\endgroup\$
    – Valley Lad
    Oct 24, 2021 at 2:31
  • 3
    \$\begingroup\$ You can certainly derive an explicit formula using Langrange interpolation, but the result's not going to be pretty. \$\endgroup\$
    – jwodder
    Oct 24, 2021 at 2:51
  • 2
    \$\begingroup\$ @ValleyLad Maybe it doesn’t directly answer the explicit question as written in the post, but it aims to solve the problem presented in the post, rather than suggest the solution asked for. \$\endgroup\$ Oct 24, 2021 at 12:55
1
\$\begingroup\$

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
1 200 200 1
2 450 250 1
3 700 250 1
4 1,100 400 1
5 1,800 700 2 T2 is a large PC power bump (2nd attack, 3rd level spells)
6 2,300 500 2
7 2,900 600 2
8 3,900 1000 2
9 5,000 1100 2
10 5,900 900 2
11 7,200 1300 3 1500/level with smoothing
12 8,400 1200 3
13 10,000 1600 3
14 11,500 1500 3
15 13,000 1500 3
16 15,000 1500 3
17 18,000 3000 4 2500/level with smoothing
18 20,000 2000 4
19 22,000 2000 4
20 25,000 3000 4
21 33,000 8000 5 8000/level
22 41,000 8000 5
23 50,000 9000 5
24 62,000 12000 5
25 75,000 13000 6 15000/level
26 90,000 15000 6
27 105,000 15000 6
28 120,000 15000 6
29 135,000 15000 6
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
5 1,800 700 2 1800 700
6 2,300 500 2 2400 600
7 2,900 600 2 3000 600
8 3,900 1000 2 4000 1000
9 5,000 1100 2 5000 1000
10 5,900 900 2 6000 1000
11 7,200 1300 3 7200 1200
12 8,400 1200 3 8400 1200
13 10,000 1600 3 10000 1600

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
1 200 200 1 200 200
2 450 250 1 400 200
3 700 250 1 700 300
4 1,100 400 1 1200 500
5 1,800 700 2 1800 600

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.

\$\endgroup\$

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .