• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create a Normal Distribution of Textual Data

W

wlerner

New Member
I have a text list of 27 items. I want to simulate a normal distribution of the list with 2000 samples. Once the list has been simulated, I would like to assign a value of zero or one to each item, based on how often it occurs. Any help would be greatly appreciated!


Thanks!
 
Hi, wlerner!


I don't fully understand your issue, would you please elaborate a bit more?


Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.


Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!
 
Thanks for your reply SirJB7.


I am attempting to create a normal distribution sample data set. To create a sample set with 1000 values, using a mean of 10 in cell F1 and standard deviation of 2 in cell H1, I place the value -10 in cell A1. I then click the Home tab, editing group, fill drop down, series button put a series in columns, a step value of .01 and a stop value of 10.


In cell B1 I enter the formula =A1*$H$1+$F$1 to convert the standard normal distribution to the distribution of interest.


In cell C2, I enter the formula =NORMDIST(B2,$F$1,$H$1,FALSE) to provide the Y-values for the distribution. I then copy down B@ and C@ to cover all the rows that contain data in column A.


I then plot columns B or C in an XY scatter chart and get a chart of a normal distribution.


With this in mind, I have a list of 27 items which I would like to plot in a similar manner. So, if item A held a value of -2, item B held a value of 2, and item C held a value of -2, I would have a normal distribution if I plotted it.


Does this help? I thought I could assign a numerical value to each of the items in the list. However, I am not sure how I would then create a normal distribution. The end result of this exercise is that I would have a list of items, normally distributed, with a value of 0 assigned if they were not present, say in a box, or 1 if they were present in a box.


The only Excel file I could post would contain what I had performed above.


Thank you for you understanding in this matter.. :)
 
I'm still just a bit confused. I think the file would help, if you're able to present it here.


Are you trying to create the curve of a normal distribution upon which to map your set of 27 items?
 
Yes Jordan, that might be a better way to say this. How the items are mapped to the distribution is not important, only that they are. This will help me create a frequency of occurrence of each item that appears in the distribution.


Below is a link to my sample.


https://dl.dropbox.com/u/66538119/Chandoo%20Question.xlsx
 
Wlerner,


Is this what you are looking for?


https://docs.google.com/open?id=0B1OBNnu3ZbL0enV5aXpDTzVobTg


If so, let me know where you have questions. Otherwise, we'll try again. :)
 
Hi, wlerner!

I was about to say the same as Jordan regarding my confusion but I see that he already uploaded a solution for your issue. Less work, a little more of NFS The Run :)

Regards!


@Jordan

Hi!

Now seriously, the bell chart doesn't look like a ghost glancing towards West?

Regards!
 
Jordan, that appears to be the solution I am looking for. Were the values you put into the input column chosen arbitrarily? It seemed like in the example everything was skewed to the left of the curve. This is a huge step forward for me! Thank you so much!
 
Ha-yes! They were picked completely at random. I'm glad to help with your research, if only by accident. Make sure though you understand what's going on and why it works.


And hi to you too, SirJb7! I've been lurking for far too long. Figured I'd try to help where I could ;)
 
I believe I understand what is going on. Can you tell me which columns you selected to create the chart? I am not having any luck reproducing your results with the chart. The list of animals are showing up as lines and not points, as they were in your example. Any assistance with this is appreciated.


Thanks again!
 
Oh yeah, I forgot about that. If you initially selected a scatter plot with lines connected when you first created this chart, any new series will assume this style by default.


So what you need to do is select the new series, right-click, then go to Format Data Series. From here, select a built-in marker from the Marker Options and 'No-line' from Line Color.
 
<p> BEIJING, Nanchang, July 3 (Wang Jian Song Changhong) Wuyuan County, Jiangxi police fraud disclosed together with high interest rates. 19-year-old young man, Lee set up investment companies by funding high interest to others, involving more than 1,000 yuan. Currently, Lee has been the police according to law under criminal detention. </P>


In May, <p> 2012 Wuyuan County in Jiangxi Province Police began to receive the report made by the victim Shumou, Cheng, Lee et al. The report alleges that the county Dongchang investment company,tom ford handbags, Lee form of high interest rates, high profits raise funds, has disappeared. According to statistics, there are more than a dozen people had been cheated, the amount of money involved in more than ten million yuan. </P>


<p> press report after Wuyuan County police attaches great importance to immediately set up a task force to expand the investigation and arrest of Lee. The task force investigating Lee facts of the crime on the one hand, to collect evidence, identify the account funds toward; on the other hand its the absconding trajectory analysis, legislation seeking as soon as possible to an arrest. </P>


<p> After nearly two weeks of intense work, the morning of June 23, police investigation, Lee is very possible fraudulent use of another person's identity to stay in a hotel in Hangzhou 8302 room. Under the assistance of the police in Hangzhou,tom ford handbag, after a night of waiting, the police suspect Lee had cracked. </P>


<p> identify preliminary interrogation, the suspect Lee, male, 19 years old, Wuyuan County, Jiangxi. </P>


on <p> 2011 in July,tom ford bag, Lee incorporated In February 2012, Lee Youyi partner to do business in the name of defrauding the victim Shumou et al funds of 3.4 million yuan. </P>


<p> According to police, Lee fraudulently obtained funds were primarily used to reimburse the fund-raising high principal and interest, and buy a luxury car and personal splurge. </P>


<p> view of the tremendous amount of fund-raising fraud,tom ford handbags, causing huge economic losses to the victim,tom ford bag, the suspect Lee has been under criminal detention, the case further investigation. (End) </p>


</Div>

<- Share microblogging begin ->


<- Share microblogging end ->


<div class=<div class=<- Forwarded to microblogging begin ->

<div id=the <span style=<div class= came home one night.
[/list]
 
You must log in or register to reply here.
Back
Top