Bell Curve Chart in Excel 2007

[aayers]

Hello,


I've been looking everywhere, on this site and other sites, and I still can't find what I need as far as Bell Curve Charts. I saw one that took simple numbers, -4 to 4 in .25 progressions, but it didn't seem to help me with the data that I have. The data that I have is all over the place. I'll copy it in here so you can get a clear picture of what I'm trying to chart. If anyone could please help me, that'd be great.


Thanks,

Andy


Data - I'm trying to graph this.

Jan-09 21,907.93

Feb-09 8,730.25

Mar-09 5,017.96

Apr-09 12,680.79

May-09 12,747.76

Jun-09 13,031.23

Jul-09 7,635.92

Aug-09 12,297.93

Sep-09 13,263.18

Oct-09 7,376.07

Nov-09 6,893.94

Dec-09 729.75

Jan-10 23,663.94

Feb-10 12,438.10

Mar-10 26,528.29

Apr-10 8,578.52

May-10 48,310.16

Jun-10 11,316.99

Jul-10 19,799.64

Aug-10 32,812.01

Sep-10 14,541.33

Oct-10 6,030.60

Nov-10 23,972.99

Dec-10 53,085.31

Jan-11 27,609.72

Feb-11 17,413.96

Mar-11 85,263.60

Apr-11 11,216.84

May-11 42,795.87

Jun-11 3,949.82

Jul-11 5,446.36

Aug-11 5,049.64

 

[OleMiss2010]

First, welcome to the Chandoo forums.


Can you explain exactly what you are trying to accomplish with this chart? You can't really create a normal distribution for a single column of data points. Are you trying to look at various years or compare months across different years or some other task?

 

[aayers]

Thanks. I've definitely liked looking on here for different Excel uses.


Yes, I'm trying to get a standard deviation graph comparing the different years. I figured I needed to have the =normdist() formula in there as well, but I wasn't sure what I should put in the formula. Some of the examples I've seen say to put it as =normdist(x,0,1,0) and some say to use =normdist(x,mean,std_dev,cumulative).

I guess I need to know exactly what I need for this to work and then chart it out on a Bell Curve sort of graph. I haven't seen any formulas/graphs with such a random set of data points.

 

[Hui]

To use a Normal Distribution your data has to fit a Normal Distribution.

To determine this setup some cells with the values from 0 to 100000 in steps of 5000

Then add a counifs to it to count how many values fit in each pigeon hole.

You will see a table like

[pre]

0	2
5000	9
10000	9
15000	2
20000	3
25000	2
30000	1
35000	0
40000	1
45000	1
50000	1
55000	0
60000	0
65000	0
70000	0
75000	0
80000	0
85000	1
90000	0
95000	0
100000	0
Your data is unlikely to be normally distributed

Although you only have 32 data points it isn't definitive

Once you have the above table

you can chart it

or more normal is to add a cummulative column


range Count	Cumm
0	2	2
5000	9	11
10000	9	20
15000	2	22
20000	3	25
25000	2	27
30000	1	28
35000	0	28
40000	1	29
45000	1	30
50000	1	31
55000	0	31
60000	0	31
65000	0	31
70000	0	31
75000	0	31
80000	0	31
85000	1	32
90000	0	32
95000	0	32
100000	0	32

[/pre]
Once again chart away


I'd suggest having a read of

http://chandoo.org/wp/2011/01/24/trendlines-and-forecasting-in-excel/

http://chandoo.org/wp/2011/01/26/trendlines-and-forecasting-in-excel-part-2/

http://chandoo.org/wp/2011/01/27/trendlines-and-forecasting-in-excel-part-3/


But please don't rush out and start applying trend lines to your data.

 

[aayers]

Ok, thanks you guys. I was hoping I just wasn't seeing something, but I figured there really isn't a way to easily do it in Excel, unfortunately.