CODEWITHSUNDEEP
excelstatistics
Narc
Narc

Reputation: 11

'Reverse' derive correct dataset from just mean and std dev?

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 am aware that I can use "=norminv(rand(), MEAN, ST.DEV)" in Excel to give me a normal distribution of random numbers that may each individually fit a given mean and std. dev. but when taken together as a group, this data set never gives me the correct 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=4 that will have this mean and std dev?

Many thanks.

Upvotes: 1

Views: 273

Answers (1)

pnuts
pnuts

Reputation: 59485

Four numbers that average 100 must sum to 400, so given three numbers the fourth has to be 400 less the sum of the other three. Calculate the standard deviation for all four numbers and then use Goal Seek to adjust one of the three to give 15 for the result of the standard deviation formula.

Upvotes: 1

Related Questions