How the XP tables work (Forked thread: Consumable item pricing)

[James McMurray]

It'd be hard to make that in to an Excel macro, sure, but I could easily write that in to a pattern in a regular programming language now that the inner workings are revealed - and using a IF to make an exception for four lines.

If the progression were continued, we would only have to *not* make such exceptions and one could ideally take the chart to any level range they want and still be within the mechanics of the game.

True, but unless you're wanting to extend the range, it's easier to just do a lookup table (in excel), or an array / enum (when programming).

 

[Dracorat]

Indeed it is. =)

 

[CapnZapp]

Here's my stab at an Excel formula (for xp). Using it is a two-step process.

Step 1. Assuming column A holds the level as previously discussed (i.e. A1 is the header, A3-A31 holds the numbers 1-30), copy the following into cell B31:

=1000*MAX(A31-1;0)+250*IF(A31>2;SUM(A$2:A29))+250*IF(A31>6;SUM(A$2:A25))+500*IF(A31>10;SUM(A$2:A21)+1)+1000*IF(A31>14;SUM(A$2:A17))+2000*IF(A31>18;SUM(A$2:A13))+4000*IF(A31>20;SUM(A$2:A11))-5000*IF(A31>21;SUM(A$2:A10))+7000*IF(A31>22;SUM(A$2:A9))+15000*IF(A31>26;SUM(A$2:A5))

...and hit enter.

Step 2. Then CTRL+C that cell (B31), mark the entire column (cells B2:B31), and paste.

Voila (I hope)!

As you can see, I'm using the SUM function to compress each band of Delta 2 increase. (=Not having to have one entry for each level, which would kind of have made it look stupid to have a function at all...). This only works if you do copy-paste as instructed, so A31 becomes A30 for the cell above, and so on.

You will get individual cell errors when each such reference reach A0, but this doesn't impact the final sums (in my Excel, anyway).

There are three kinks that make the function bigger:
#1: the extra +1 at 11th level (as this marks the beginning of a new "band", I don't need a whole extra IF)
#2: the jump at 21, requiring the extra IF(>20)
#3: the "backwards" jump at 22, requiring the extra IF(>21). Notice how this is subtracted from the total.

Otherwise, you can see how the numbers multiplied always are the difference between the old and new delta 2 "band" number.

I have a vague suspicion you could condense down the formula even further (not having to repeat all those IF(SUM).

I simply don't have the Excel-fu to even begin to understand how to accomplish this, however. (Merely utilizing SUM in this way required some googling... on "number series excel" to be exact )

 

[CapnZapp]

Just for fun, the xp required for 40th level is seven million!

How did I reach this number? Well, I had to make certain assumptions, which could be wrong, of course...

I assumed 1) delta-2 generally double every four levels 2) that Wizards like nice even numbers for their maximum levels.

So delta-2 double to 50k at level 31, and again to 100k at level 35. But at level 39 instead of merely doubling, I set the delta-2 to 250k, which gave such a nice even number at level 40... so if Wizards ever publish a levels 31-40 expansion module, remember where you heard it first!

Edit: By the way, here is the Excel (MS Excel 2000 SR1) file, if you can't be bothered to use the manual steps... it includes the xp for levels all the way up to forty...

 

[Dracorat]

if it helps, to get the 4 number "bands", you can FLOOR((A2 - 1)/4) (assuming row 2 ... A3 for row 3, etc)

So, throwing it all together, I give thee...

=IF(A4=11,26000,IF(A4=12,32000,IF(A4=21,175000,IF(A4=22,210000,(B3-B2)+IF(A4<23,(250*POWER(2,FLOOR((A4-3)/4,1)))+B3,(4000*POWER(2.5,FLOOR((A4-19)/4,1))+B3))))))

Oh yeah -_-

Paste starting at row 4 (level 3) and copy down.

 

[Nikosandros]

Right, like I said. The semblance of a pattern is there, but no pattern (currently) exists. Whether they messed up, consciously changed it (my assumption), or lucked into something pattern-like doesn't change the fact that there are irregularities which destroy any chance of it being usable for formulae, which is where the XP discussion started.

I'm fairly certain that they purposefully altered a regular pattern. Probably in order to make the progression protected by copyright.

 

[Jhaelen]

I'm fairly certain that they purposefully altered a regular pattern. Probably in order to make the progression protected by copyright.

lol!
I'm pretty sure they did this because they wanted to avoid the number 997000 which is a registered trademark of mine

Edit: This reminds me of the German Telekom trying to trademark the letter 'T' and the colour 'magenta'... sometimes I think, I'm really living in a Dilbert cartoon.

 

[CapnZapp]

if it helps, to get the 4 number "bands", you can FLOOR((A2 - 1)/4) (assuming row 2 ... A3 for row 3, etc)

So, throwing it all together, I give thee...

=IF(A4=11,26000,IF(A4=12,32000,IF(A4=21,175000,IF(A4=22,210000,(B3-B2)+IF(A4<23,(250*POWER(2,FLOOR((A4-3)/4,1)))+B3,(4000*POWER(2.5,FLOOR((A4-19)/4,1))+B3))))))

Oh yeah -_-

Paste starting at row 4 (level 3) and copy down.

Wow. That's frikkin' amazing! It actually works! Thank you!


Edit: And by your formula, the level 40 xp value is 7140625...

 

[Dracorat]

No problem!

Math has always been one of my better subjects.