Percentage chance to hit target number, A1+variable

[miinstrel]

Hi all -

Here is my spreadsheet:

What I need to do is calculate in column F the percentage chance of someone rolling a 20 sided die and achieving a number greater than or equal to the number in column E after adding the bonus on the same line in column C. So for example, in order to get at least 18, as shown on line 5, the player would need to roll X+7.

What I THINK needs to happen is have excel check C5+H3,C5+H4,...C5+H22, determine how many "Yes this roll will be equal to or greater than 18" there are, and then divide by 20. That's how it will seem to work in my mind at least.

The percent chance of success is capped at 95%, so even if all 20 rolls would beat the number in column E, there is still a 5% chance of failure.

Thank you in advance for any insight you can provide. I'll be checking regularly if you need more information or clarification from me.

 

[Dryver14]

is it me or is not a 5% chance of landing on any side of a 20 sided dice?

This variation does does not change with reference to what you are rolling against.

The odds will not change.

 

[miinstrel]

The numbers in columns C and E will both be changing frequently. E4 might be 23, E6 might be 42, C5 might change to 11... i'll be entering those target number and modifiers manually as the situation changes.

And sorry, when I said above "Yes this roll will be equal to or greater than 18" I should have said "Yes this roll plus the modifier in column C will be equal to or greater than 18"

 

[miinstrel]

Figured it out... I was making this way more difficult than it needed to be:

=IF(((20-E22+C22)*0.05)<=0,0,IF(((20-E22+C22)*0.05)>0.95,0.95,((20-E22+C22)*0.05)))

That stops it from going below 0% success, over 95% success, and works perfectly. probably a simpler way to do it but this works just fine

Problem solved, thanks.

 

[shg]

This does the same thing:

=MEDIAN((20-E22+C22)/20, 0, 0.95)