DM Tool - Using Excel to Calculate XP

Post/Author/DateTimePost
#1

calico_jack_drake

Apr 12, 2005 15:40:10		Ok, first, credit where credit is due. The work in this article is based off of the work done at this web site: http://www.hambo.com/dnd/rules/xp.html

The website does a pretty good job of reducing the xp awards to a single formula which would be necessary for being able to do the xp calculations in an Excel spreadsheet. However, his final solution is less than adequate as a 6% loss or gain in xp at the outer edges would seriously annoy most people. If the party goes through a hard fought battle against a creature with a CR 5 above the average party level, the loss of three or four thousand xp might be a cause for concern.

Working from his model, however, I was able to build a formula that worked for all apparently correct values on the XP chart. Note that what follows is a copy of the formula that can be used in an Excel spreadsheet by replacing the CR and ECL variables with creature and character reference cells respectively and a description of how the formula works. COUNT is the number of PCs in the party. If you get bored by math you might want to stop here.

(ECL*300*
(2^TRUNC(ABS(CR-ECL)/2)* {power of 2 multiplier}
(1+(0.5*MOD(CR-ECL,2))))^ {one and a half multiplier}
(IF(CR-ECL=0,1,(CR-ECL)/ABS(CR-ECL))))/COUNT {inverter and proportional adjustment}

The base xp granted for any individual starts with a base of 300 * their Effective Character Level (ECL). Everything after that is based on a modifier that is calculated from the Challenge Rating (CR) of their opponent; or, more specifically, the difference between the character's ECL and the opponent's CR.

The next part of the formula is based on the fact that for every two levels difference between the ECL and CR, the xp award value doubles. So, an ECL 10 v. a CR 10 is worth 3000xp while an ECL 10 v. a CR 12 is worth 6000xp. By taking the difference between the ECL and the CR and dividing it in half, we get the appropriate power of 2 necessary to calculate the xp where the difference between the ECL and the CR is an even number.

This method breaks down, however, when the difference between the ECL and the CR is an odd number. Recall from basic math that a number raised to the one-half power would be equal to the inverse or square root of the number. This is the basis to the original formula from the above mentioned web site. However, for our purposes, that number is too imprecise to work in this situation. Also, there is a better way to calculate the xp award if the difference between the ECL and the CR is an odd number.

If we look at the xp table in the v3.5 DMG, we can deduce that, if the difference between the ECL and CR is an odd number, the xp value is equal to half again the next lowest power of 2. In other words, a CR one level higher give an xp award equal to one and a half times the next lowest power of 2 which is 2 to the 0 power or 1, a CR 5 levels higher is equal to six times the base xp award since it is one and a half times the next lowest power of two which is 2 squared (2^2 = 4 *
1.5 = 6). This progression holds true for all given values of xp awards except the five or six we assume to be either statistical anomalies or simply typos in the chart.

In order for the formula to take this multiplier into account, we need to multiply the power of two multiplier by one and a half if, and only if the difference between the ECL and the CR is an odd number. First, we truncate the power of two multiplier so we're only working with whole powers of two in the calculations. We then need take the modulus of the difference between the CR and ECL divided by two. The answer here is always 1 or 0. Since any number times 1 returns that number, and any number multiplied by 0 returns 0, we multiply the .5 modifier by the modulus (1 if the difference between the ECL and CR is odd, 0 otherwise) and add that to 1. If the difference in the ECL and the CR is an odd number, we multiply the xp award by 1.5 or by 1 if the difference is an even number.

These calculations match the DMG so long as the ECL is smaller than the CR. However, if the ECL is larger, the values for the encounter begin to go back up again. Obviously this is the wrong result. The website above noted that the proportional decrease in xp awards was consistent with the inverse of the multiplier we have calculated so far (2x became one half, 4x became one quarter, etc.) In order to account for this and make the calculations work correctly, we need to
invert the multiplier for the base value if the CR is less than the ECL. We can do that by raising the multiplier to the power of -1 if CR - ECL is negative, that is the ECL is greater, of raise it to the power of 1 if the difference is positive.

To do that, we can raise the multiplier to a calculated value of CR - ECL over the absolute value of CR - ECL. A negative number divided by a positive number will always return a negative value and a positive number divided by a positive value will always return a positive value. Further, any number, divided by itself equals 1. So combining these to values, if the CR is less than the ECL, the number will always return -1; if the CR is greater it will always be 1.

Everything works until the CR and ECL are equal. At that point, however, the CR - ECL over the absolute value of CR - ECL becomes 0 over 0 which is impossible. You cannot divide a number by 0. In this case we must test for a 0 value and replace the calculation with a static value of 1. If we don't the calculation can never handle two opponents where their ECL and CR match. This creates a problem for a purely mathematical formula but can be handled by a spreadsheet such
as Excel.

Finally, the xp award for each character must be divided by the number of characters in the party to get a proportional award per character. In this case, a character with a greater difference between ECL and CR will get proportionally more or less experience depending on whether the difference is positive or negative.

Fun With Math...
#2

jazzman831

Apr 16, 2005 1:01:54 		Great stuff. I made a little excel program out of it to calculate all of the experience per monster, and it adds them all up. I was checking against the table and all the ECL=1 were wrong except for where CR=1. Also ECL=2,3,4,5 when CR=1 was wrong, and ECL=2 when CR=4,6,8,10 was wrong. I don't think it's possible to write a function for these though without at least another IF function; it's related to the fact that the 1st-3rd party level EXP's are grouped as one category. You did EXTREMELY well and this will make EXP tons easier.
#3

eldmor

May 02, 2005 18:58:20 		Could you make a calculator program for this? Say for a TI-83 Plus? (I don't know crap about calculator programming so don't blame me.) It would prove useful as I have a dice program and a PC gen on it also.

Thanks in advance,
Eldmor