Excel party xp calculator for 3.5e!

[Drommon]

Nice spreadsheet. Any idea why it requires you to enter a 1 or 0 in the level column for party members 7 and 8?

Now I just need a sheet that can handle up to 10 PCs

Likewise. Anyone know how to remove the dialog box that keeps coming up when I put a number other than 0 or 1 in level 7 & 8?

I need more party member slots. I have used excel a lot but I am not savvy with it.

Thanks!

Drommon

 

[Fast Learner]

My original only handled 6. I'm pretty sure I set those cells up so that they had zeros in them, and that if they had a zero in them then they appeared blank. Sorry I can't help more... if I had more time to work on it then I'd already have created a new one for 3.5 that supports a dozen party members and epic xp.

 

[Plane Sailing]

Nice spreadsheet. Any idea why it requires you to enter a 1 or 0 in the level column for party members 7 and 8?

Now I just need a sheet that can handle up to 10 PCs

1. The /Data/Validation option needs to be changed for those two spreadsheet cells. I've prepared a new zip file to upload but ENboards are working like treacle at the moment, so I can't upload it. You'll be able to make the change quite easily yourself though - just look on the menu indicated above by "/" and you'll be done.

2. This would be quite easy to extend to 10 PCs; Just unhide all the hidden rows and it should be quite obvious how to insert a few extra rows for the character names (naming their level field as level9, level10 etc) and then inserting a few extra columns for recording their attendence and ditto for recording their xp accumulation. Inserting the attendence columns will probably put some of the summation references out, so they would have to be corrected by the four new columns too.

Cheers

 

[Plane Sailing]

I've cracked it!

I've solved the general "experience table progression formula" problem (not as impressive as, say, the general theory of relativity, but still I'm pretty pleased).

I've just got to add in some calculations to cover the odd little corner cases that WotC introduced (e.g. 1st -3rd level uses the 3rd level line, no CR1 Critter is ever worth more than 300xps). As soon as those are added in to the spreadsheet I'll upload it as a fully non-infringing experience calculator

Cheers

 

[Wonko the Sane]

I've cracked it!

/me bows to Plane Sailing

Epic levels too, then?

 

[Davin]

I've cracked it!

Super!!

Question: Is it all one (or two) formulas for the whole table, or is it cell-by-cell variable (e.g. some cells 1/2 of two columns over and others multipliers of the level and still others multipliers of cells to their left, above, or below)? I'd really like to get it down to a nearly-single, relatively-simple formula.

 

[Plane Sailing]

Because of limitations in excel as compared to a programming language I have to use a simple lookup table instead of converting it all into a single expression.

Basically take level-CR and look at the following table to get the multiplier which is then applied to level * 300xps.

-8 or less   0
-7           1/12
-6           1/8
-5           1/6
-4           1/4
-3           1/3
-2           1/2
-1           2/3
0             1
+1           3/2
+2           2
+3           3
+4           4
+5           6
+6           8
+7           12
+8 or more   0

I use an "adjusted level" for the lookup - if level is < 3, adjusted level = 3.

I also have a quick sanity check that says if CR = 1 and level < 6, then xp = 300 rather than do the calculation (because the table ignores the calculation and tops out at 300xp for CR1 challenges).

My dear wife thought I was a bit bonkers last night, sitting with the DMG table on my lap and surrounded with bits of paper with scribbled mathematical series on them, but once I'd got going I really wanted to suss it out this time.

Wonko - yep, epic levels too now!

Cheers

 

[Plane Sailing]

Davin Church has detected a flaw, and I have completed further investigations and can confirm that the experience calculator doesn't precisely match the DMG table in the following circumstances:

Level = 4 and CR = 5 (and 7 & 9 & 11 due to knock on effect). For some reason they decided to use a 4/3 multiplier in this one instance rather than a 3/2 multiplier. I can only assume that this is a bug in their table

7 <= level <= 19 and CR = (level - 5). For some reason in these particular circumstances on this particular diagonal they decided to switch from "divide by 6" to "divide by 5 and a third". Maybe they got tired of seeing all the xp values end in 0 or 5, and they wanted other integers to spice it up a little? The difference amounts to between 50-100xps less on the spreadsheet rather than by the table, which isn't much.

I'll probably put in a fix for the first one, since it makes a significant difference for CR 5/7/9/11 encounters at 4th level, giving too much according to the chart (even though I'm convinced that it is a bug in the original chart

Cheers

 

[Davin]

I'd like to take a couple of exceptions to your reasoning here. Second, values varying by "just a little bit" (more than just rounding) have to be taken seriously and accounted for in the formulas. I don't think "fairly close" is good enough for this.

Then firstly, I don't think that's a bug in their table. For instance, if Level=4 & CR=5 was supposed to be what you calculated, then it would be the same number as Level=1-3 & CR=5, which doesn't make sense to me. They may well have bugs in their table, but over the years I've done a good bit of semi-mathematical table analysis on D&D tables, and every time I thought I'd found a typo it turned out to be because I had the wrong formula. So I'd suspect my own formulations first, second, and third before I'd try calling it an error.

I also don't think this should be "fixed", as in coding in an exception for them. This is partially because I hate singular (non-rule-based) exceptions, and partially because I'm not convinced they're wrong.

You might go back to my earlier note in this thread about how most of the table seems to use a sequential 4/3 & 3/2 multiplier pattern, but that pattern breaks down in various places. You'll also note that applying that pattern cumulatively (starting with 3/2) gives the multiplier sequence that you've described, but if you start with 4/3 it gives a completely different sequence in the odd terms, one of which is "5 1/3". My difficulty came when I couldn't figure out why they deviated from a strict pattern.

Also, I'll have to go back and confirm it, but I think I saw this pattern break down in lots of different places near the bottom-left-diagonal edge of the table. This says to me that something else is going on here that we haven't properly accounted for in our pattern. Can you maybe spot any sub-patterns in the list of exceptions?

 

[Plane Sailing]

Hi Davin,

I checked for anomalies this time by putting the two charts (standard and my calculated one) stacked above each other and then created a third chart which subtracted every position in one chart from every position in the other. The only anomalies are the ones that I identified above.

We know that they have already applied fudge factors to this table (vis levels 1-3 sharing a row; and the fact that no CR1 award is allowed to calculate to higher than 300 seems suspicious to me too).

The penultimate diagonal looks suspicious to me for several reasons. Primarily because the funny behaviour doesn't extend along the entire diagonal, secondly because it is the only part of the table with odd numbers.

Hang on a minute. I've just noticed something. Did you know that the experience chart changes between 3.0 and 3.5e?

That diagonal by my calculations is wrong if measured against the 3e chart, but correct according to the 3.5e chart !!! (My excel test was against the 3e chart in the original version of the spreadsheet, but I'd been creating the formula against the 3.5e DMG in my lap).

Huzzah, I feel vindicated! They *were* wrong in 3e and they corrected it in 3.5e!

This leaves only the anomaly at 4th level which I'd still categorise as a "hack" to separate the 4th level row from 1st-3rd level rows.

Cheers