1
$\begingroup$

I'm running a DnD campaign and made a Random NPC generator in Excel. It's pretty slick, but the random height/weight calculator is... flat. RAND(X,Y) flat.

So I'd like to add some Bell Curve calculations, because why not. I've entered the average min/max heights and weights for each race, and can easily calculate the mean. Now, if I assume those average min/max values are 1 standard deviation, I've got the ingredients of a formula. (the average height listed for Human was pretty close to real life 1SD range for a human male)

It's been... years... since I took a stats course, and I remember almost nothing.
Bell Curve and SD is about it. I spent a few hours poking around, but didn't find any answers, or at least I don't think I did. X__X

So, given a Mean, and ranges for 1SD, I'd like to enter a percentile (1d100) and arrive at a height.
For example, a 16% would be -1SD, 50% would be exactly the median, and 85% would be +1SD (following the 68–95–99.7 rule). Hopefully that makes sense.

Given the criteria above, could someone please explain what Excel formula to use? Preferably using a built-in one.

$\endgroup$

1 Answer 1

1
$\begingroup$

So I'd like to add some Bell Curve calculations, because why not. I've entered the min/max heights and weights for each race, and can easily calculate the mean. Now, if I assume those min/max values are 1 standard deviation,

Why would the minimum and maximum values be one standard deviation from the mean? That's not true of either the uniform ('flat') case, nor of the normal case.

In the case of the uniform, 1 standard deviation is $\sqrt{1/12}$ (just under 30%) of the distance between minimum and maximum.

The normal curve - what I assume you mean by a bell curve - doesn't actually have a minimum or maximum. Normally you would specify a mean and standard deviation, but there's no limit to how many standard deviations an observation could be, they just become increasingly rare.

One way to give something like a 'minimum' and 'maximum' if you still want to use a normal distribution, is to specify some extreme quantile as a notional minimum and maximum, which would correspond to some number of standard deviations from the mean.

So, given a Mean, and ranges for 1SD, I'd like to enter a percentile (1d100) and arrive at a height.

Ah, yes, if you give the mean and standard deviation (you don't have to specify mean-sd and mean+sd, though you could do that instead), it's easy.

(Your use of the terms minimum and maximum threw me off what you were trying to do)

For example, a 16% would be -1SD, 50% would be exactly the median, and 85% would be +1SD (following the 68–95–99.7 rule). Hopefully that makes sense.

Well, you mean 84%, but otherwise, yes.

As a general solution that applies to any package, you need a function that converts a percentile to a normal; that's known as the quantile function or the inverse cdf for the normal. Most packages used for statistical work will implement one where you can supply the mean and standard deviation. (If they only supply one for the standard normal, it is easy to convert to any other normal by multiplying by the sd and adding the mean).

So if you have such a function, you then supply it with a uniformly distributed random percentile (or rather, a quantile) to be turned into a normal quantile.

If you just want to use a random uniform, in Excel you need: =norm.inv(rand(),mean,sd)

So for example if I put =NORM.INV(RAND(),B3,C3) into D3, and the values '58' and '5' into B3 and C3, I get:

        B     C       D
 3     58     5     56.5974071

where 56.59... is a random normal value with the required mean and standard deviation

If you actually want the results to reflect using a d100 (i.e. a discrete uniform), what you want to do is make your d100 into a number between 0 and 1, by subtracting say 0.5 and dividing by 100 (more generally, you can subtract $\alpha$ and divide by $101-2\alpha$, for $\alpha$ between 0 and 1 - different choices will put you more or less far into the tail, but $\alpha=0.5$ should do okay.

So RANDBETWEEN(1,100) gives you your d100

In that case,

        B               C          D     E         F

6     d100     (d100-0.5)/100    mean    sd      height
7       51              0.505      58     5      58.063

where the formula in F7 is now: =NORM.INV(C7,D7,E7)

$\endgroup$
1
  • $\begingroup$ The Human "min/max" values given in the book are nearly identical to the human male 1SD I've seen listed around the internet. Close enough to just roll with it. As for technically not having a min/max, I'll just use percentiles between 1 and 100 (exclusive). This seems to be working quite well, and I can refine it as I go. Thanks! :D $\endgroup$ Commented Dec 13, 2013 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.