need help with formula for critical hit and damage (Diablo 3 related)

Factual Questions
1

This is more of a math/logic problem than it is a game question, so I put it in GQ. In this game two major stats affect your damage. They are plus to Critical Hit Damage and Critical Hit Chance. So, if your base damage is 1000 and you have a 5% Critical Hit Chance (CC) and + 100% to Critical Hit Damage (CD), then 95% of the time you will just do 1000 damage, but 5% of the time you will do 2000 damage, for an average of 1050.

I decided to make a spreadsheet that would show hundreds of combinations so that I could figure out the question of "Which is more important? CD or CC? (I’ve heard that 1 CC is equal to 10 CD, but I want to test this. I suspect that it’s best to have them balanced, but wanted to see if I could mathematically figure out how many CD is equal to 1 CC.)

Along the top I have “Critical Hit Chance” in row 1, and in row 2 I have 0, 5, 10, … 100.

Coulmn A is labeled “Critical Hit Damage”. In column B I have 0, 50, 100, …500 (although I could go higher.)

I have the value 1000 in field A1, because I want to be able to change this base value to see if it makes a difference. So, if you’ve made your spreadsheet like mine it looks like this:



1000          Critical Hit Chance
                0          5        10
       0   
      50
     100

Now I just need to make the formula and paste it to every field from C3 (where 0 and 0 meet and the value should be 1000) to W13 (where we have 100% critical hit chance and a 500% increase to the damage, so the value should be 6000)

The problem is making the formula. Here’s a rough draft of my formula:

[(100 - CC) * 1000] + [CC * (1000 * CD)]

Here it is in Excel terms:
=(100-C2) * A1 + (C2 * (A1*B3))

This comes up with a value of 100000. Not entirely unexpected because I didn’t input CC as a percentage. I’m not sure if I should change the formula or the headings. (I started to change the headings but then didn’t think that changing 0 to .00 would change anything.)

Next I copied the formula (that was only off by 2 orders of magnitude) from C3 to D3 and confused the heck out of Excel. I had to change some cell references. Perhaps I need to scrap the idea of being able to change the base damage on the fly? Or do I need to put that in another sheet? Or do I need to hold Ctrl or Shift when pasting?

After correcting the formula in D3 (5% CC and 0 CD) I got 95000. With 0 CD they should all be 1000.

I’m good at math and decent at Excel, but not an expert in either. I appreciate any help.

2

Just in case it’s unclear, the first half of the formula is to calculate the percentage of the times it just does the base (1000) damage, and the 2nd half is to calculate the percentage of the times “I make a critical hit”, ie. when the CD gets added. I suspect that my formula would render 100 CC and 500 CD as 5000 instead of 6000, but that’s a bridge that I’ll cross when I come to it.

3

For the math, I don’t know enough about the formulae to notice what to change, but using 1 and .05 etc. might work better, but you’d need to change some things, as 0 dam/5% chance is lower than 0/0%. I believe the first half is wrong because it reduces the base damage when that should always be 100%, although I haven’t played D3.

For formula, try:
=(100-C$2)$A$1+(C$2($A$1*$B3))
This will allow you to just drag the formula, instead of copying and tweaking each one.

4

I modified (improved?) the formula that I put in field D4 (I’ll worry about copying and pasting later. Right now I just want to get a formula that gives the correct number (or the correct number * 100)

The formula in D4, where we have CC or 5% and CD of 50% is:


    
=(100-D2) * A1  +  (D2*(100+B4)*A1)
      5     1000    5      50   1000
      CC   BD       CC     CD  BD

I kinda labeled these as CC (Critical Hit Chance or 5% in this case) and CD (Critical Hit Damage of 50% in this case) and BD (Base damage of 1000)

This gives a value of 845000 when it should be 1025 (I would accept 102500 or 10250000).

5

OK, I made some more changes. The Lurking Horror faught my ignorance and fixed my problem of copying the formula.

I changed the first half of my CC fields to percentages (ie I changed 5 to .05 and 10 to .10, etc.)
Because of this, I changed the first “100” in my formula to “1”.

For field E5, which should be easy to caluclate, since it’s a 10% chance to get a 100% increase in damage (ie. double the damage). So 90% of the time we do the base damage of 1000 and 10% of the time we add 100% of the damage (so we double it) and get 2000 damage. This averages out to 1100.
We’re trying to get the formula to do this:
90% chance of 1000 and 10% chance of 2000 … so…
(.9 x 1000) + (.1 * 2000) = 900 + 200 = 1100

Here’s my current formula which gives a value of 20900
=(1-E$2)$A$1+(E$2($A$1*($B5+100)))
E2 is .1
A1 is 1000
B5 is 100

6

Working this formula out on paper I get:

=(1-E$2)$A$1+(E$2($A$1*($B5+100)))

(1-.1) * 1000 + (.1 * (1000 * (200)))

So I have some extra zeros in the second half of the formula. The first half is fine. Smoke break. I’ve almost got this.

7

OK, I finally found success by dividing the last part by 100. My problem was mixing percentages and whole numbers. I did change all of the CC values to percentages, so 95 became .95 and 100 became 1. Here is the final formula (taken from W13):

=((1-W$2)$A$1)+(W$2($A$1*(($B13+100)/100)))

8

Simple way to see that they should be balanced: One is useless without the other. If you have 0% critical hit chance, then critical hit damage is irrelevant, and if you have 0% critical hit damage, then critical hit chance is irrelevant.

True, the game starts with a default of 5% or 10% chance for PCs and 50% damage, but the edge case is still insightful (and anyway, hirelings do actually start with 0% crit chance, so crit damage gear is a waste on them unless you can give them a ton of +chance too).

9

You can calculate average crit damage as well, which seems a bit easier.

cc = Critical strike chance
cd = Critical strike damage
dmg = Average damage

(cc/100)*(cd/100)*dmg

Note: Values as seen in game (5% crit chance, 50% crit damage as default)

5% cc and 50% cd = 2.5% damage increase
50% cc and 50% cd = 25% damage increase
100% cc and 50% cd = 50% damage increase

10

Ho. Lee. Crap. [Slaps forehead… a few times for good measure]
Now I’m embarrassed. DELETE THREAD!!! DELETE THREAD!!!
I seriously spent a lot of time on this, not because it was important (It would have been faster to calculate several dozen different scenarios by hand), but because I got obsessed with figuring out how to get the formula correct.

Where the hell were you yesterday? :slight_smile:

At least I learned something about Excel… and math.

11

I’ve done that many times, do an Excel only to find out I could’ve done it much easier. Still, I happy for having the experience. Plus I am going to guess that someone has already made an intricate spreadsheet with all the D3 variables, but your way is more fun (if you’re the kind of dork like me that enjoys Excel).

12

Oh, and for statements like

This depends on the cost of both attributes, and on how much of each you have already. The formula is symmetric, but the actual ranges available in the game are not. In practice, a statement like “1 CC = 10 CD” means that they’re about equally easy to get in the game.

13

Chronos, I figured as much, and my spreadsheet basically assummed a 1:10 ratio, mostly because I read something on a forum basically saying this.

That said, I finally found a website that shows the max amout of each mod.
http://www.diablohub.com/guides/diablo-3-item-stat-maximum-values
The max amount of CC is roughly 1/10 of the max amount of CD.

The question now becomes: what’s the exchange rate of IAS to CC or CD?