• 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.

How to create bell curve in Excel 2013 from my data

A

AitchK

Member
Hello Chandoo forum members,
I'm so stuck!

I need two bell curve aka standard deviation charts. One to show bell curve for a year's worth of data, and another showing a separate bell curve for each month's worth of data.

I've calculated the count, mean, and standard deviation, by year and by month (12 months) for the metric %ofPurchasePrice and attached excel spreadsheet. I just can't figure out how to chart bell curve/standard deviation in Excel 2013.

Can someone take the attached spreadsheet and do it for me? Thanks.
 

Attachments

  • ChandooHelp.xlsx
    114.8 KB · Views: 47
Where is your raw data..

I mean.. where is the daywise / monthwise sale.. for arrow / Sims..

So that.. a histogram can be made..
your data is not in a Normal Distribution, its scattered..
Please elaborate..
 
Debraj said:
Where is your raw data..

I mean.. where is the daywise / monthwise sale.. for arrow / Sims..

So that.. a histogram can be made..
your data is not in a Normal Distribution, its scattered..
Please elaborate..
Click to expand...
Debraj, I've reuploaded attachment with date column. All tutorials I watch suggest that having the data that I needed distributed (%ofPurchasePrice), the mean, and standard deviation is what I need for bell curve, and I have it in this file. I thought that even scattered data could be made into bell curve if mean and standard deviation were known?
Can you take a look now?
 
Not sure if this is really what you are aiming at... but here is my 2 cent bet

Create a named range XVAL defined as
=ROW($A$1:$A$200)/500

Create another namer range YVAL
=($C2/($G2*SQRT(2)))*EXP(-(($B2-(ROW($A$1:$A$200)/500))^2)/(2*($G2^2)))

and plot the two in a XY scatterplot.

Is this what you want?
 
Iferror, I don't know if that's what I want. sorry, I forgot to upload updated attachment. Can you look at attachment?
 

Attachments

  • ChandooHelp.xlsx
    137.7 KB · Views: 26
Yes i can...but i do not understand what it means!! :) Makes no sense to me ;)
Have a look at the formula in my last post, assuming that you have
B2=mean value
C2=total events
G2=sigma
that is a draft version of the formula you're after

Would that be an option to update the raw data and the formulas you've used to get mean,sum,% and stdev from that?
 
Oh, I see. I already have mean, total events, and sigma in my columns:
CountYr is count of total events in a year.
CountMo is count of total events in a month.
MeanYr is the mean for all events in the year.
MeanMo is the mean for all events for each month grouping.
StandDevYr is for year
StandDevMo ...month
%ofPurchasePrice are the events I'd like graphed.

I was actually missing 'MeanMo' from former spreadsheet so have reattached. I have everything I should need to make the bell curves, but I don't know how to chart it.
can you please help make the charts?
 

Attachments

  • ChandooHelp.xlsx
    152.3 KB · Views: 10
Last edited:
I think i got it.
Let's say i would have managed the file in a rather different way :)

That is what i did to your latest file:

Pivot Table to get one single entry for mean, count and stdev each month and vendor

HTML: 
<table border=0 cellpadding=0 cellspacing=0 width=448 style='border-collapse:
collapse;table-layout:fixed;width:336pt'>
<col width=64 span=7 style='width:48pt'>
<tr>
  <td></td>
  <td colspan=3>ARROW</td>
  <td colspan=3>SIMS</td>
</tr>
<tr>
  <td></td>
  <td>MEAN</td>
  <td>COUNT</td>
  <td>STDEV</td>
  <td>MEAN</td>
  <td>COUNT</td>
  <td>STDEV</td>
</tr>
<tr>
  <td>1</td>
  <td align=right>0.134911</td>
  <td align=right>200</td>
  <td align=right>0.08802</td>
  <td></td>
  <td></td>
  <td></td>
</tr>
<tr>
  <td>2</td>
  <td align=right>0.025223</td>
  <td align=right>163</td>
  <td align=right>0.030796</td>
  <td></td>
  <td></td>
  <td></td>
</tr>
<tr>
  <td>3</td>
  <td align=right>0.086471</td>
  <td align=right>122</td>
  <td align=right>0.079755</td>
  <td></td>
  <td></td>
  <td></td>
</tr>
<tr>
  <td>4</td>
  <td align=right>0.098795</td>
  <td align=right>142</td>
  <td align=right>0.111409</td>
  <td align=right>0.098795</td>
  <td align=right>142</td>
  <td align=right>0.111409</td>
</tr>
<tr>
  <td>5</td>
  <td align=right>0.12249</td>
  <td align=right>1396</td>
  <td align=right>0.063357</td>
  <td align=right>0.12249</td>
  <td align=right>1396</td>
  <td align=right>0.063357</td>
</tr>
<tr>
  <td>6</td>
  <td align=right>0.084031</td>
  <td align=right>251</td>
  <td align=right>0.063634</td>
  <td align=right>0.084031</td>
  <td align=right>251</td>
  <td align=right>0.063634</td>
</tr>
<tr>
  <td>7</td>
  <td align=right>0.088681</td>
  <td align=right>44</td>
  <td align=right>0.049343</td>
  <td align=right>0.088681</td>
  <td align=right>44</td>
  <td align=right>0.049343</td>
</tr>
<tr>
  <td>8</td>
  <td align=right>0.031221</td>
  <td align=right>147</td>
  <td align=right>0.028665</td>
  <td align=right>0.031221</td>
  <td align=right>147</td>
  <td align=right>0.028665</td>
</tr>
<tr>
  <td>9</td>
  <td align=right>0.059487</td>
  <td align=right>526</td>
  <td align=right>0.081983</td>
  <td align=right>0.059487</td>
  <td align=right>526</td>
  <td align=right>0.081983</td>
</tr>
<tr height=20 style='height:15.0pt'>
  <td height=20 align=right style='height:15.0pt'>10</td>
  <td align=right>0.323062</td>
  <td align=right>58</td>
  <td align=right>0.73566</td>
  <td align=right>0.323062</td>
  <td align=right>58</td>
  <td align=right>0.73566</td>
</tr>
<tr>
  <td>11</td>
  <td align=right>0.152497</td>
  <td align=right>359</td>
  <td align=right>0.078144</td>
  <td align=right>0.152497</td>
  <td align=right>359</td>
  <td align=right>0.078144</td>
</tr>
<tr>
  <td>12</td>
  <td align=right>0.151547</td>
  <td align=right>21</td>
  <td align=right>0.09766</td>
  <td align=right>0.151547</td>
  <td align=right>21</td>
  <td align=right>0.09766</td>
</tr>
</table>

- create a column for xvalues strarting from the minimum value you get by mean-(5*stdev) up to the max value you get by mean+(5*stdev)
- use the formula for the bell curve as stated above and copy down/across
 
I use SQL to retrieve and aggregate data into excel. Did you mean to post your sample in html? I'm not sure how I will consume that.


How about starting again. Can someone just tell me what calculations and columns I need to make a bell curve? Do I need anything more than mean, standard deviation, the values themselves?
 
Last edited:
sorry new to the board.....i though i could add an HTML table to the post. Anyway, here's a screenshot of the worksheet
 

Attachments

  • gaussian.jpg
    gaussian.jpg
    153.8 KB · Views: 25
just noticed that you have the same count,mean and stdev for arrow and sims... is that really what you want to have?
 
Column N is the column you can use as X VALUES for the chart.
Use Column O for Y VALUES.

To keep it simple:
select cell O1
copy the formula right for 11 columns
edit the reference to mean,sigma and count for each month
copy down
add one graph for each month
 
ifError, thanks a lot. I've attached end results.
What is this formula?
=($B$2/($C$2*SQRTPI(2)))*EXP(-(((E2-$A$2)^2)/(2*($C$2)^2)))

What is the vertical axis referencing?

I would like the bell curve to begin at base of graph...since I do not have any values below zero I have started the x-axis at zero...Can I do that or do I have to show negative values on x-axis?
 

Attachments

  • ChandooHelpv1.xlsx
    40.3 KB · Views: 26
Last edited:
Thank you for telling me that the truncated bell is normal and yes I need to understand it better, but can you tell me what the vertical access numbers are in layman's terms (besides the output of the formula)?
Is it possible to superimpose 4 or more bell curves on same chart?
 
Last edited:
a
NARAYANK991 said:
Hi ,

Can I suggest something ?

Go through the following link , and do exactly what it describes. I did and the file I got is uploaded.

http://support.microsoft.com/kb/213930

Narayan
Click to expand...
I really appreciate that link. It requires that my end consumer will have to install an add-in and I can't do that right now.
My questions are really specific about the Y axis and about overlaying multiple bell curves- can they be answered???
 
Last edited:
Hi ,

As far as I know , the Y-axis plays no significant part in a distribution , since what you want to see is the distribution along the X-axis.

As far as multiple bell curves are concerned , if you repeat the suggested procedure multiple times , you should get the distributions , which will allow you to plot them. There should be no problem , since all of them are referred to the same base distribution in columns E and F.

Narayan
 
Thank you amazing people, not the least of which was iferror and Narayan. I appreciate that the Y-axis is uninteresting in terms of what I'm interested in: the x-axis. I appreciate you staying with me to answer all my questions. I've managed to make overlapping standard deviation curves. Thank you very much!
 
Last question.
What is this formula called?
=($B$2/($C$2*SQRTPI(2)))*EXP(-(((E2-$A$2)^2)/(2*($C$2)^2)))
 
You must log in or register to reply here.
Back
Top