MHB Calculating Data Set w/ MEAN=100 & STD DEV=15 & n=3

[fieldmusic123]

If I know the mean and standard deviation, but I don't have an original data set, is it possible to reverse calculate a data set that has this known mean and std dev? Is there an excel function I could use to do this easily? I know there could be multiple correct datasets and that's fine, I'm just wondering if it's possible to reverse derive a correct one that has the known mean and std dev.

So, for a MEAN = 100 and STD. DEV= 15, can anyone tell me how to calculate a data set with n=3?

Many thanks.

 

[Ackbach]

I've wondered this same problem myself. The problem is much more difficult with larger data sets (you would just have to assume certain values), although with a large enough data set, you can just go with a normal distribution, and generate a whole raft of values from a known normal distribution. That procedure might not be exact, but it would be close. The larger the data set the closer it would be.

For $n=3$, you can set up a system of equations:
\begin{align*}
100&=\frac{x_1+x_2+x_3}{3} \\
15^2&=\frac12\left[(x_1-100)^2+(x_2-100)^2+(x_3-100)^2\right]
\end{align*}
Since you have three unknowns and two equations, this is an underdetermined system. Just pick one value, and the other two will be determined.

 

[I like Serena]

If I know the mean and standard deviation, but I don't have an original data set, is it possible to reverse calculate a data set that has this known mean and std dev? Is there an excel function I could use to do this easily? I know there could be multiple correct datasets and that's fine, I'm just wondering if it's possible to reverse derive a correct one that has the known mean and std dev.

So, for a MEAN = 100 and STD. DEV= 15, can anyone tell me how to calculate a data set with n=3?

Many thanks.

Hi fieldmusic123! Welcome to MHB! (Smile)

It's [m]=NORMINV(RAND(), mean, stdev)[/m].

To explain, the CDF of some x has the range 0 to 1.
When we generate a random number between 0 and 1 from the uniform distribution, we can use the inverse CDF to find the corresponding random x value.

 

[fieldmusic123]

Hi fieldmusic123! Welcome to MHB! (Smile)

It's [m]=NORMINV(RAND(), mean, stdev)[/m].

To explain, the CDF of some x has the range 0 to 1.
When we generate a random number between 0 and 1 from the uniform distribution, we can use the inverse CDF to find the corresponding random x value.

Hey there, thanks for the welcome and your reply! NORMINV(RAND(), 100, 15) in Excel will give me 3 random numbers in what looks like a normal distribution but the 3 numbers as a combined data set do not give a mean of 100 and std dev of 15. Is there perhaps a way to use Excel to manipulate one of these values so that the set has the required mean (eg. 100) and std dev (eg. 15)? I'm a little rusty with complicated maths. Cheers, Sean

 

[I like Serena]

Hey there, thanks for the welcome and your reply! NORMINV(RAND(), 100, 15) in Excel will give me 3 random numbers in what looks like a normal distribution but the 3 numbers as a combined data set do not give a mean of 100 and std dev of 15. Is there perhaps a way to use Excel to manipulate one of these values so that the set has the required mean (eg. 100) and std dev (eg. 15)? I'm a little rusty with complicated maths. Cheers, Sean

As Ackbach explained, you can generate $n-2$ numbers this way, say $x_3...x_n$, and calculate $x_1$ and $x_2$ to match from:
$$\begin{cases}\mu &= \frac{x_1+x_2 + \sum x_i}{n} &= 100 \\
\sigma^2 &= \frac{(x_1-100)^2 + (x_2-100)^2 + \sum (x_i-100)^2}{n} &= 15^2\end{cases} \\ \Rightarrow
\begin{cases}x_2 &= 100n - \sum x_i - x_1 &= A - x_1 \\
(x_1-100)^2 + (x_2-100)^2 &= 15^2n - \sum (x_i-100)^2 &= B\end{cases} \\
\Rightarrow\begin{cases}x_2 = A - x_1 \\
(x_1-100)^2 + (A - x_1-100)^2 = B\end{cases}
$$
The last equation is a quadratic equation that can be solved with the quadratic formula yielding $x_1$, after which the first equation gives $x_2$.

 

[fieldmusic123]

Dear Ackbach and I Like Serena, thanks very much for your help, now I understand. All the best.