log in or register to remove this ad

 

Monster CR Calculator

Tormyr

Adventurer
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.
 

log in or register to remove this ad

Dysfunkt

First Post
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
 

Attachments

  • Monster CR Calculator 2010.xlsx
    20.4 KB · Views: 192
Last edited:

Tormyr

Adventurer
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

First Post
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.
 
Last edited:

Dysfunkt

First Post
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)
 
Last edited:


dave2008

Legend
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

Adventurer
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

Legend
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

First Post
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.
 
Last edited:

Tormyr

Adventurer
*snip*
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!

Except the Attack Bonus and Save DC are both modified by the proficiency bonus, which is set by CR for a monster and need to be in place before the final CR is calculated. If it makes you feel better, just think of it as "Proficiency Bonus" instead of "Expected CR" as the only actual effect that "Expected CR has is to set the proficiency bonus in the rest of the sheet. All the other information that it sets is advisory only. So you can set it to any of the following values:
Proficiency BonusCR Range
21-4
35-8
49-12
513-16
617-20
721-24
825-28
929-30

And I understand what you mean about not wanting to decide on a CR ahead of time. You can also give the Attack Bonus and Save DC a fixed modifier to simulate the proficiency bonus, but you may need to double check at the end that the monster's CR and proficiency bonus line up if that is important to you.

When I made this tool, I was converting a lot of monsters from 3.5 to 5e for my Age of Worms campaign. I used the Encounter Level of the encounters in the campaign as a guide and set the encounters in the 5e version as medium encounters for a group of 4 PCs of that level. So I knew how many monsters of a certain CR that I wanted. The HP and DPR ranges were more important for me to quickly ballpark the monsters in to a given CR before their special abilities adjusted the effective HP, AC, DPR, attack bonus, and Save DC.
 

dave2008

Legend
Except the Attack Bonus and Save DC are both modified by the proficiency bonus, which is set by CR for a monster and need to be in place before the final CR is calculated.

Thank you again for your reply. I typically determine CR first (as described in my previous post) and then if the final CR yields a different prof. bonus than the assumptions in the defensive and attack CR calculation i adjust as needed.

Typical it as no effect on the final CR (since it is a best 1/4 of the calculation)
 
Last edited:

SetArk

First Post
About Traits.

HaiL!
So little question, how about the Traits?
Yeah, I read the bold text about copyrights, but i seriously can't understand how to fill it, so it calculates automatically the OR and DR modifications from the traits.
 

Tormyr

Adventurer
HaiL!
So little question, how about the Traits?
Yeah, I read the bold text about copyrights, but i seriously can't understand how to fill it, so it calculates automatically the OR and DR modifications from the traits.

It does not calculate them for you. You fill out the traits page with the names of the traits, and the text of what they do. On the main page you can select one of them from one of the five drop-down lists, and it will remind you what change needs to be made. You then make that change in the appropriate yellow box a couple lines down.

For example, if a CR 15 monster had 3 legendary resistance uses, you would select Legendary Resistance from one of the drop downs. It would remind you to add 30 effective hp per use of Legendary Resistance for a creature of that CR. There is a row of yellow boxes after the traits drop-down lists that let you modify the effective HP, DPR, AC, etc. You would enter 90 into the yellow effective HP box. This would raise the calculated effective HP by 90 which flows through to the calculations at the end.
 

SetArk

First Post
Hmm
But, when a trait says, "Up the Ofensive Rating by 2", for exemple, the Agressive Trait.
There doesn't seem to be a yellow slot, so i can up the final Ofensive Rating for the monster.
Should i just go to the final Ofensive Rating, the yellow block on the botton, and change it there? Even through it will break the script before it?
 

Tormyr

Adventurer
Hmm
But, when a trait says, "Up the Ofensive Rating by 2", for exemple, the Agressive Trait.
There doesn't seem to be a yellow slot, so i can up the final Ofensive Rating for the monster.
Should i just go to the final Ofensive Rating, the yellow block on the botton, and change it there? Even through it will break the script before it?

Aggressive does not say that.

Aggressive
Increase the monster's effective per round damage output by 2.

So you add 2 to the yellow DPR box. If a trait says to only add the damage for one round then you would add it further up where DPR is calculated.
 

An Advertisement

Advertisement4

Top