How to create a normal gaussian distribution chart using vba macro?

**wdn1508** · 09-09-2009, 05:50 PM

I have data on sheet1 (attached). How can I create a normal gaussian distribution chart using vba macro in Excel of based on the column selection on sheet1 and put the chart on sheet2?

I found some link in this forum on how to create gaussian distribution in Excel:
http://tushar-mehta.com/excel/charts...tion/index.htm
http://www.exceluser.com/explore/statsnormal.htm

but if it is possible to create those above charts automatically using macro, I would appreciate if someone could share the code to me..

Regards,
WiD

**shg** · 09-09-2009, 05:54 PM

Why would you want use use a macro when it requires nothing more than listing some x values and copying down a single formula -- what's the advantage?

If you really want a macro, record one while doing the above.

**wdn1508** · 09-09-2009, 06:05 PM

I have hundreds of the same spreadsheets that need gaussian distribution to help on looking the distribution of the data. If on every cells I have to create the charts manually then it would be painful.

Rgds,
WiD

**shg** · 09-09-2009, 06:08 PM

Make one sheet (5 minutes) and 100 copies (30 seconds).

**wdn1508** · 09-09-2009, 06:17 PM

what I meant was every sheet containing different data. If 1-sheet takes 5 min, based on your calculation then it gonna take 5min x 100 to finish. Anyway, many thanks for the suggestion, I will create on manual way.

Rgds,
WiD

**shg** · 09-09-2009, 06:18 PM

Where would the data come from?

Even if you create the template sheet, you have to add it from someplace.

**wdn1508** · 09-09-2009, 06:28 PM

the data come from another calculation using another macro on the workbook. So the data will be totally different from one workbook to another workbooks. And I have more than 100 workbooks. On each of workbook I have 3 sheets, the 1st and 2nd sheet are original data, 3rd sheet the calculation data by macro based on 1st and 2nd, and the 4th sheet I wish could have auto created -gaussian normal distribution chart.

Rgds,
WiD

**shg** · 09-09-2009, 07:38 PM

and the 4th sheet I wish could have auto created -gaussian normal distribution chart.

I guess I don't know what that means.

Do you want to extract the mean and deviations from some data, plot the distribution of the data gainst some bin size, and also plot a gaussian distribution having the same mean and deviation?

If that's it, then create a template sheet having what you want, and then (in a macro, if you like) insert a copy of that sheet into your workbook, and then your macro can plug in the data.

No one (not me, anyway) could write a macro for you without knowing what you want, and the only details are what I'm making up as I go along.

**wdn1508** · 09-09-2009, 09:24 PM

Yes, that's it. Thanks again for suggestion.

Rgds,
WiD

**wdn1508** · 09-09-2009, 10:21 PM

I was just googling and found an interesting articles that give an example step by step how to create this gaussian curve using vb macro.
http://books.google.com/ , book title Microsoft Excel VBA Professional Projects by Duane Birnbaum (pages 601-638).

I will start learn from it. Again thanks for the replies.

Rgds,
WiD