Monster CR Calculator

[Tormyr]

I have just uploaded Monster CR Calculator to the downloads area.

This excel spreadsheet implements the monster creation and CR calculation guidelines from pages 274-281 of the Dungeon Master's Guide. It includes the steps listed from those pages that effect CR. Fill out the yellow spaces in the spreadsheet as appropriate. The Offensive, Defensive, and Average CR will be computed at the bottom of the first tab. Only the first tab is needs anything entered when calculating the CR of a monster. However, the Traits tab does need to be set up the first time.

To be fully useful, the Monster Features from pages 280-281 of the DMG need to be entered into the Traits tab on the spreadsheet. These were kept out for copyright purposes. Only the 39 entries that actually affect CR need to be entered.

One thing I noticed as I was calculating the CR of monsters in the Monster Manual that were spellcasters: when calculating damage per round of an area of effect spell, the number of creatures hit by the area of effect spell was half what was recommended in "Adjudicating Areas of Effect" on page 249 of the DMG.

You can find the file here in the downloads section. Please use this thread for comments.

 

[Dysfunkt]

floor function

FLOOR function didn't get updated, still uses _xlfn

If anyone wants to try using it and it hasn't been updated then it's just a few small edits to the formulas that show the error. When you change a field like Str to a different value #NAME? errors come up everywhere something needs to be changed.

Replace the _xlfn.FLOOR.MATH with just FLOOR and if there isn't a signifier already just add ", 1"
So: =_xlfn.FLOOR.MATH((C5-10)/2)
Would be: =FLOOR((C5-10)/2, 1)

If there are already commas in the function then we need to use ROUNDDOWN instead, with the number of digits being 0 (and forgetting about the extra comma and the -1).
So: =_xlfn.FLOOR.MATH((D40-C42)/2, , -1)
Would be: =ROUNDDOWN((D40-C42)/2, 0)

I'll upload my 2010 friendly version

 

[Tormyr]

FLOOR function didn't get updated, still uses _xlfn

If anyone wants to try using it and it hasn't been updated then it's just a few small edits to the formulas that show the error. When #NAME? errors come up replace the _xlfn.FLOOR.MATH with just FLOOR and if there isn't a signifier already just add ", 1" and an extra set of brackets where needed
So: _xlfn.FLOOR.MATH((C5-2)/3)
Would be: FLOOR(((C5-2)/3), 1)

If a signifier is there already there'll also be an extra comma with it and an extra number which I'm sure is supposed to do something but I'm not sure what, but if you leave the negative numbers the maths won't calculate so this will make the sheet work, but I'd have to double check the numbers to see if they're still accurate.
So: _xlfn.FLOOR.MATH(((C5-2)/3), , -1)
Would also be: FLOOR(((C5-2)/3), 1)

Uh, the formulas look a bit odd there. A couple questions:
1. What application are you opening the file in?
2. I don't see the use the calculation (C5-2)/3 anywhere. Are you seeing this in a cell, or is this just an example?

The Floor.Math(number, significance, mode) function was introduced in Excel 2013 and has the following arguments:
number - the number to round
significance - (optional) the significance to round downwards towards. Defaults to whole numbers.
mode - (optional) the direction to round negative numbers. -1 rounds toward 0. Default is to round down.

 

[Dysfunkt]

Excel 2010, and yes that's just an example. That part's the part that doesn't need to be changed, just needed to show where the comma should be. The internet told me that the _xlfn functions were being replaced, but I didn't look too far into it. Just know excel 2010 doesn't like them.

And I did have problems with the negative numbers it turns out which makes sense now, so those had to be changed to 1 as well

It does look like FLOOR rounds down as default as well and that's the only mode ever entered in the code. It turns out excel 2010 also doesn't like it when you leave variables empty though so they have to be put in. Perhaps there is a FLOOR.MATH equivalent somewhere but for this file FLOOR should work.

I did get around to doing the manual calculations on a created creature and verified that they are accurate.

 

[Dysfunkt]

Oh wait, I see what you mean there by round towards 0 instead of down. Maybe I missed something. Might not have had negative numbers in my calculations.

Update: According to the internet the floor.math functions that were -1 should be ROUNDDOWN instead. Will remove uploaded file and fix. (yes, apparently FLOOR rounds down now and ROUNDDOWN rounds to 0)

 

[Dysfunkt]

Updated my initial reply and uploaded file there.

 

[dave2008]

Tormr,
Thank you for doing this work, I've been wanted to add traits to my CR calculator for some time. However, the spreadsheet I downloaded doesn't have any traits listed (there is nothing in the pull down or on the traits tab). So maybe I don't have the most current file. With that in mind, here are few comments I have:

1) I am not sure about the expected CR tab. Shouldn't this be auto-calculated based on HP and then adjust by AC and traits?
2) Similarly there should be an expected attack CR based on DPR that is then adjusted by attack bonus and traits.

Thank you for your hard work.

 

[Tormyr]

Tormr,
Thank you for doing this work, I've been wanted to add traits to my CR calculator for some time. However, the spreadsheet I downloaded doesn't have any traits listed (there is nothing in the pull down or on the traits tab). So maybe I don't have the most current file. With that in mind, here are few comments I have:

1) I am not sure about the expected CR tab. Shouldn't this be auto-calculated based on HP and then adjust by AC and traits?
2) Similarly there should be an expected attack CR based on DPR that is then adjusted by attack bonus and traits.

Thank you for your hard work.

Hey, glad you like it!

The bold section in the first post mentions that the traits sheet is empty because of copyright reasons. It doesn't take long to add the entries, and you only have to add the ones that have any effect. Even then, they are usually just there as a reminder to make a change in the appropriate cell of the "Trait Adjustments" line.

1. Do you mean the "Expected CR" cell? That defines all the numbers that are in the bottom of that section. It gives you the effective ranges for HP and DPR to shoot for and, most importantly, gives the proficiency bones that is used for calculation of attack bonus and saving throw DC. The "Average CR" cell at the bottom of the sheet is the end result of all the calculations, but it cannot feed back into "Expected CR" because it would cause a cyclical set of references through the sheet.

If you are using the page to create a monster of a specific CR, then set "Expected CR" to what you want the monster to be so you see the HP and DPR ranges and the correct proficiency bonus is applied. If you want to type in a monster and see what CR it is, set the "Expected CR" to where you think the monster should be, and if the "Average CR" is different to the point that the proficiency bonus would change, set the "Expected CR" to the "Average CR" that was calculated, and check the "Average CR" again.

2. That is the second to last section, near the bottom of the sheet. The "Offensive CR by DPR" is the calculated value. You can then select "Attack Bonus" or "Save DC" from the "Using" field to choose which modifier gets applied to the initial offensive CR.

 

[dave2008]

Hey, glad you like it!

The bold section in the first post mentions that the traits sheet is empty because of copyright reasons. It doesn't take long to add the entries, and you only have to add the ones that have any effect. Even then, they are usually just there as a reminder to make a change in the appropriate cell of the "Trait Adjustments" line.

OK, I read that to quickly. I thought you had set up to auto calculate the traits effect to the CR. I guess I still have work to do

1. Do you mean the "Expected CR" cell? That defines all the numbers that are in the bottom of that section. It gives you the effective ranges for HP and DPR to shoot for and, most importantly, gives the proficiency bones that is used for calculation of attack bonus and saving throw DC. The "Average CR" cell at the bottom of the sheet is the end result of all the calculations, but it cannot feed back into "Expected CR" because it would cause a cyclical set of references through the sheet.

If you are using the page to create a monster of a specific CR, then set "Expected CR" to what you want the monster to be so you see the HP and DPR ranges and the correct proficiency bonus is applied. If you want to type in a monster and see what CR it is, set the "Expected CR" to where you think the monster should be, and if the "Average CR" is different to the point that the proficiency bonus would change, set the "Expected CR" to the "Average CR" that was calculated, and check the "Average CR" again.

Well, the thing is I don't like to start with an expected CR and by the book you don't have too. Per the DMG, you can input the HP and that gives you the "expected Defensive CR," from the table. This is then adjusted by AC and traits which gives you the Defensive CR. Next you calculate the monsters DPR which gives you the "expected Attack CR," from the table. This is then adjusted by the attack bonus and traits to give you the Attack CR. These are then averaged to give you the Final CR (which may cause adjustment to proficiency bonuses). That is how I like to work. Your spreadsheet attacks it a little differently that is all. No worries.

Thank you for the quick response and hard work!

 

[Dysfunkt]

The spreadsheet works that way too. You don't need to fill in the expected CR, that's just extra info people can use if they want whilst filling out the other fields. The calculation you refer to is at the bottom and based only on all the stats you fill out.