Need help from someone with crazy math skills...

[JVisgaitis]

I'm working on an Excel spreadsheet that auto calculates all of the math for each of the monster types and roles. I want to add a section where it gives the standard experience reward from the chart on page 120 of the DMG, but I have no clue if that can be done in a formula in Excel or not. Can someone help me out?

 

[Danceofmasks]

Not sure what you're asking ...

As far as I can tell, you want to have two bits of info, level and monster type, and have a function that returns what XP it should be worth?

If it helps, standard award looks like:
100 for level 1, +25 per level till 5, +50 per level till 9, +100 per level till 13, +200 per level till 17, +400 per level till 21 ... seems the amount added doubles every 4 levels till 21.
Then it's arbitrarily +950, +2000, then doubling again ... +4000, +8000, +16

Monster type, quarter for minion, double for elite, x5 for solo ...

Erm, the sequence is too difficult to code ... I'd go with two cells:
A LOOKUP function, manually assigning the XP value for standard monster by level, then the second cell to apply the multiplier for monster type.

Quite sure that'd work ... not as sure if it'd work to put both lookup functions in the same formula and multiply them.

 

[Danceofmasks]

Just tested the lookup, and it works .. so I will copy it here to save you the trouble of typing all that crap in.

Umm, the whole thing is one line, whatever the board's wrap code decides to do with it ...
A5 is the cell that had the monster's level in it.


=LOOKUP(A5,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40},{100,125,150,175,200,250,300,350,400,500,600,700,800,1000,1200,1400,1600,2000,2400,2800,3200,4150,5100,6050,7000,9000,11000,13000,15000,19000,23000,27000,31000,39000,47000,55000,63000,79000,95000,111000})

 

[JVisgaitis]

Just tested the lookup, and it works...

Exactly what I was looking for. Thanks so much!

 

[Storminator]

It's pretty easy.

Put the level of the monster in cell A1.
Put the type of monster (standard, minion, elite, solo) in cell B1.
Put the entire table, including the headers (standard, minion, elite, solo) in the section from A2 to E42.

Put the formula in cell C1
=HLookup(B1,A2:E42,A1+1,FALSE)

Should work like a champ.

PS

 

[Storminator]

Just tested the lookup, and it works .. so I will copy it here to save you the trouble of typing all that crap in.

Umm, the whole thing is one line, whatever the board's wrap code decides to do with it ...
A5 is the cell that had the monster's level in it.


=LOOKUP(A5,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40},{100,125,150,175,200,250,300,350,400,500,600,700,800,1000,1200,1400,1600,2000,2400,2800,3200,4150,5100,6050,7000,9000,11000,13000,15000,19000,23000,27000,31000,39000,47000,55000,63000,79000,95000,111000})

No job is too big for brute force!

That is frickin' crazy DofM!

PS

 

[Storminator]

Oh, and in general, I recommend playing with the Dynamic Excel "Wiesbaden Charactersheet" on the Fan Creations forum. You can learn a lot of clever Excel trickery by reverse engineering that sucker.

PS

 

[Danceofmasks]

Well, I'm fond of saying ... why write an essay if you can say the same thing in a single line?
Seems that applies to spreadsheets, too.

 

[Guyanthalas]

My buddy and I got into this argument today, as I'm trying to create a formula that will calc the experience. His argument was of course "H(V)LOOKUP", which I'm sympathetic to, but there should be a way to do this formula via algebra right? Granted, its complex I'm sure, but it must be doable as the XP increase does follow a formula.
Anyone have any ideas for how to calc it without a lookup function?

 

[Nytmare]

I'm sure, but it must be doable as the XP increase does follow a formula.

It does?