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.