Wulf Ratbane
Adventurer
New thread.
Last edited by a moderator:
[Immortal's Handbook] CR, EL, and other Excel spreadsheets
- Thread starter Wulf Ratbane
- Start date
Wulf Ratbane
Adventurer
CRGreathouse said:
Let me ask you this (and others who have seen the spreadsheet).
Because I did start with the original, simple logarithmic function, and getting it whacked into shape to mirror the charts was the real bitch.
Should the spreadsheet mirror the charts presented in UK's work (and my own, for that matter);
or should the spreadsheet have a mathematical accuracy that the charts cannot convey?
Which is the more useful tool?
Wulf
CRGreathouse
Community Supporter
Wulf Ratbane said:
As a math guy, of course I'd say that increased mathematical accuracy is a Good Thing, especially if it means that the formulae are simpler. Heck, if we're going to have a chart, we might as well just do a simple lookup on Excel...
I'm not sure how others would feel about this, though. I'd certainly feel bad for you if we went to the simple formulas -- after you spent all that time working out a way to represent the chart...
Wulf Ratbane
Adventurer
CRGreathouse said:
I was actually using the Excel sheet to make another chart to include. The chart had EL's down the left hand column, Creature CR's across the top row. The idea was to be able to look across the top and down the side to find out how many creatures of a given CR you needed to have a particular EL encounter.
When I found the discrepancy at CR23 (because log2 23 rolls over 9.xxxx) the chart started to fall apart.
The question I asked myself was, is someone who owns the book and uses the charts going to find the spreadsheet LESS useful if it does not return the same results as the charts, or MORE useful if it returns a result that is, technically, more accurate than the chart.
The higher the CR goes, the farther off it can be in terms of CR. For example, the chart might say that CR4090 = EL48, whereas the Math function would say that CR4090 = EL45.
Not so big a deal for Grim Tales (as it is not meant to be Epic) but for UK's system, a difference of even 2 EL could be a TPK.
It seems one system (either the Chart or the Math) could result in a TPK. One of the two isn't returning a fair value.
Wulf
CRGreathouse
Community Supporter
Wulf Ratbane said:
CR 4090 = EL 48 using my suggested function
=INT(4*LOG(D7,2))+1
This is the same result as the rounded/chart function.
Wulf Ratbane
Adventurer
CRGreathouse said:
I'm gonna feel real silly if the problem is fixable with order of operations...
Wulf Ratbane
Adventurer
By the way...
I should note that the spreadsheet does not work with Fractional CRs (1/10th through 2/3).
That would require a more elaborate fix; I discussed it in the main thread but can recap here if anyone wants to attempt a fix.
Wulf
I should note that the spreadsheet does not work with Fractional CRs (1/10th through 2/3).
That would require a more elaborate fix; I discussed it in the main thread but can recap here if anyone wants to attempt a fix.
Wulf
CRGreathouse
Community Supporter
Wulf Ratbane said:
Don't feel silly. I believe that the 'neat' function will always be within 1 of the normal function, but there's still some difference there. (I see this difference as a good thing, of course.) This means that there's no real danger of one system sending the group into a TPK.
Wulf Ratbane said:
I haven't seen your post about this, but the problem is easily fixible in the abstract (non-chart). I'll throw together a formula once I've looked over UK's CR sheet to brush up on his method (which, as I recall, is similar to what I've done for years).
Wulf Ratbane
Adventurer
CRGreathouse said:
Let me recap first on how I calculate EL.
1) Add up the CRs of all creatures involved.
2) Convert CR to EL.
3) Adjust the EL based on the number of creatures involved.
This doesn't work out very well when you're using fractional CRs, so here is the fix I proposed, which involves an extra step:
3a) When determining the number of creatures involved, you have to "group" fractional CRs into groups of CR1, then count the number of "groups" as the number of creatures.
Example: Sixteen creatures, CR 1/2. (Say, orcs.)
Total CR = (16)(1/2) = 8.
CR 8 = EL 13 (unadjusted EL)
Number of combatants = 8 (each group of two CR1/2 creatures counts as one combatant)
Adjusted EL = 7.
Note that if you just tried this with the spreadsheet, you'd get some wonky errors, because the number of combatants will drag down the EL faster than each combatant is pumping up the CR. (It's even worse with smaller fractions).
Note also that this is consistent with how the SRD does things. Two orcs are CR1/EL1, therefore four orcs (one doubling) are EL3, eight orcs are EL5, sixteen orcs are EL7.
Wulf
CRGreathouse
Community Supporter
Calculating EL under the standard system, for CR >= 2:
A. List the CRs (e.g. 6, 6, 8)
B. Calculate the power for each member: 2 ^ (x / 2) (e.g. 8, 8, 16)
C. Sum the powers (e.g. 8+8+16=32)
D. Take the base 2 log (e.g. log_2 32 = 5)
E. Double the result (e.g. 10)
This is pretty basic. Since CRs under 2 work differently under the standard system, step B needs to be changed. The power of a CR 2 from step B is 2, and below CR 2 power is proportional to CR, so we can use the CR itself. Thus, the revised step B:
B. Calculate the power for each member: 2 ^ (x / 2) for x >= 2, or x for x < 2.
This way 16 CR 1/4 under the standard system are EL 2 * log_2 (4) = 2 * 2 = 4, just as 4 CR 1s are EL 4.
As I said, I'll have to look over UK's system to see if it's as easy and elegant as the standard system's
=If(A1<2,A1, 2^(A1/2))
A. List the CRs (e.g. 6, 6, 8)
B. Calculate the power for each member: 2 ^ (x / 2) (e.g. 8, 8, 16)
C. Sum the powers (e.g. 8+8+16=32)
D. Take the base 2 log (e.g. log_2 32 = 5)
E. Double the result (e.g. 10)
This is pretty basic. Since CRs under 2 work differently under the standard system, step B needs to be changed. The power of a CR 2 from step B is 2, and below CR 2 power is proportional to CR, so we can use the CR itself. Thus, the revised step B:
B. Calculate the power for each member: 2 ^ (x / 2) for x >= 2, or x for x < 2.
This way 16 CR 1/4 under the standard system are EL 2 * log_2 (4) = 2 * 2 = 4, just as 4 CR 1s are EL 4.
As I said, I'll have to look over UK's system to see if it's as easy and elegant as the standard system's
=If(A1<2,A1, 2^(A1/2))
Last edited:
