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

AI Thread Summary
It is possible to reverse calculate a data set with a known mean of 100 and standard deviation of 15 for n=3, but it requires setting up a system of equations due to the underdetermined nature of the problem. Users can generate random numbers using Excel's NORMINV(RAND(), 100, 15), but this may not yield a dataset that meets the specified mean and standard deviation. To achieve the desired values, one can generate n-2 random numbers and then calculate the remaining values to satisfy the equations for mean and standard deviation. The final solution involves solving a quadratic equation derived from the conditions set by the mean and standard deviation. Understanding this process allows for the creation of a valid dataset that meets the specified statistical criteria.
fieldmusic123
Messages
7
Reaction score
0
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.
 
Mathematics news on Phys.org
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.
 
fieldmusic123 said:
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.
 
I like Serena said:
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
 
fieldmusic123 said:
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$.
 
Dear Ackbach and I Like Serena, thanks very much for your help, now I understand. All the best.
 
Suppose ,instead of the usual x,y coordinate system with an I basis vector along the x -axis and a corresponding j basis vector along the y-axis we instead have a different pair of basis vectors ,call them e and f along their respective axes. I have seen that this is an important subject in maths My question is what physical applications does such a model apply to? I am asking here because I have devoted quite a lot of time in the past to understanding convectors and the dual...
Insights auto threads is broken atm, so I'm manually creating these for new Insight articles. In Dirac’s Principles of Quantum Mechanics published in 1930 he introduced a “convenient notation” he referred to as a “delta function” which he treated as a continuum analog to the discrete Kronecker delta. The Kronecker delta is simply the indexed components of the identity operator in matrix algebra Source: https://www.physicsforums.com/insights/what-exactly-is-diracs-delta-function/ by...

Similar threads

Back
Top