Transforming data to Normal (Guassian) bell curve

[holiday]

Hi all

I've tried so many times but I'm getting lost and confused the more I learn on the net about this one... please could someone help?!!

Very simply, please could someone list step-by-step how I turn any data set of numbers into a Normal Probability output (also known as Guassian) in Excel 2007?

I've played with FISHER, NORMDIST, STANDARDIZE, everything!!! I just want to simply take a set of skewed number and get them as close to Normal (Guassian) as possible.

Also, how can I test this to make sure the output is indeed Normal? (EG: Plot a bell curve graph if possible so I can see it with my own eyes!!)

So many thanks to any helpers out there.... this may be a simple question but the help available online (if you use any search engine to look at statistics webpages etc) just gets complicated...

Thank you

Holiday

 

[Oaktree]

I don't follow your question... what do you mean "take a set of skewed numbers and get them as close to Normal as possible"?

There are transformation methodologies (e.g. Box-Muller or ziggurat) to take uniform distributions and create normal... but, if your starting point is already a skewed data set, you're trying to fit the square peg in the round hole.

For your second question, you may find this writeup helpful for testing if your distribution is normal:

http://www.skymark.com/resources/tools/normal_test_plot.asp

 

[holiday]

Hi

What I mean is I understand the Fisher Transform converts a data set (skewed) and converts it to a Guassian Probability Distribution... the vague Excel helpfile says so too!!
And I know it can only convert a dataset that runs from -.9999' to +.9999' so I can rescale any dataset I have to fall between those two limits (that's easy enough) and then run the FISH function on the scaled data. The way I understand it, the output from doing this should be a Normal Distribution set, to which I can then apply statistical concepts such as standard deviations from mean etc....

I know that the Fisher transform wont turn data EXACTLY to Normal, but it is supposed to do a very good job at un-skewing the data and getting it close to Normal... have I got this wrong??! Everything I have read up to now says this is the case... so I have played about with it (doing what I wrong in the first paragraph) and the output (when plotted with a line graph or scatter or whatever) seems a bit.... well, not what I expected, it didn't resemble to "bell curve" distribution at all.

Any help???!!!

Many thanks!!!!!!

Holiday

 

[Oaktree]

Be careful with what the inputs and outputs from the Fisher transformation are though...

Fisher's transformation is used to make observations about the *correlation coefficient* between the two variables... it doesn't actually convert the *data* to a normal distribution. The input to Fisher's transformation (the "x" in Excel's help file, or the "p" in most writeups) is the correlation between X and Y of the original data set, and it assumes that X and Y follow a bivariate normal distribution. Fisher's output, z, transforms p to have a normal distribution, and is basically intended to give an "easy" way to find a confidence interval for p.

If your data is skewed, it's skewed, and the analysis you could do on a normal distribution doesn't apply. It's like trying to make apple juice when your ingredients are oranges...

 

[holiday]

Hmmm ok, I think I follow... will fiddle sum more but I understand your point!!

Thank you very much for your time and help!!!!

Holiday

 

[goldfish]

If you are trying to get a best fitting normal approximation to your data, you may be over thinking it. You just need to find Average() and the STDev() of your data. To plot the normal curve generated by this Average() and STDEV() you can place the numbers you would like to see that normal curve at in one column, say O (so if your data set goes from -10 to 15, you can put -10 in O1, =O1+1 in O2, then drag down), then in R11 and R12 put the Average() and the STDEV() giving you:

=NORMDIST(O1,$R$11,$R$12,FALSE)

HTH,
~Gold Fish

 

[jazztech]

Hi Goldfish,

I think I have similar problems to holiday's, not enough knowledge in statistics for a start.

I have a large amount of numerical data from an experiment, where if the input parameters to run the experiment would be correct, the output should be a normal distribution.

Using average,median,skew,kurt and a histogram generated by the frequency function, I can see that my experiment did not lead to a normal distribution.

Using NORMDIST as you suggested, can I generate a set of data from the original one, with the same average and stdev but giving a normal distribution? I tried but didn't work. Or am I completely wrong?

Many thanks in advance!

 

[goldfish]

Yes, this function will work for you. Suppose your x points are in O1:O50 (The points you wish to plot by which I mean the input). Then if your mean is in R11 and your stdev is in R12 (make sure it is your stdev and not you variance) then the following formula should work for you:

=NORMDIST(O1,$R$11,$R$12,FALSE)

You can put this in say Q1 and then drag it down.

Does this clarify it?

~GoldFish