Create Histogram with bell curve

Question

the below code intends to create histogram with bell curve but it is not creating histogram and producing simple chart , pls suggest what changes should be made to create histogram with bell curve:

code:

private void button2_Click(object sender, EventArgs e)
        {
            // Get the range of data for the chart
            string range = textBox2.Text;

            // Get the LSL and USL values
            double lsl = double.Parse(textBox3.Text);
            double usl = double.Parse(textBox4.Text);

            // Select the data for the chart
            var dataRange = (Range)Globals.ThisAddIn.Application.ActiveSheet.Range[range];
            var sheet = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;

            // Add a histogram chart
            ChartObjects chartObjects = (ChartObjects)sheet.ChartObjects();
            ChartObject chartObject = chartObjects.Add(0, 0, 300, 300);
            Chart chart = chartObject.Chart;
            chart.ChartType = XlChartType.xlColumnStacked;
            chart.SetSourceData(dataRange);

            // Get the mean and standard deviation of the data
            double mean = (double)Globals.ThisAddIn.Application.WorksheetFunction.Average(dataRange);
            double stdDev = (double)Globals.ThisAddIn.Application.WorksheetFunction.StDevP(dataRange);


            // Add a new column to the data range for the bell curve data
            dataRange.Columns[1].EntireColumn.Insert(XlDirection.xlToRight);


            // Calculate the probability density function of the normal distribution
            for (int i = 1; i <= dataRange.Rows.Count; i++)
            {
                var x = dataRange.Columns[1].Cells[i].Value;
                var y = Globals.ThisAddIn.Application.WorksheetFunction.NormDist(x, mean, stdDev, false);
                dataRange.Columns[2].Cells[i].Value = y;
            }

            // Add a new series to the chart using the bell curve data
            chart.SeriesCollection().NewSeries();
            chart.SeriesCollection(2).XValues = dataRange.Columns[1];
            chart.SeriesCollection(2).Values = dataRange.Columns[2];
            chart.SeriesCollection(2).ChartType = XlChartType.xlLine;

            // Add a trendline to the new series
            var trendline = chart.SeriesCollection(2).Trendlines().Add();
            trendline.Type = XlTrendlineType.xlExponential;
            trendline.DisplayEquation = true;
        }

Intended output(ignore the center vertical redlines):

actual output:

sample data:

Answer 1

There was an old MSChart example "Samples Environments for Microsoft Chart Controls" which you can see here or download here using wayback machine, or see in the MSDN code gallery archive here.

One of the examples is ChartTypes > DataDistributionCharts > Histogram.cs, which uses HistogramChartHelper to create a histogram chart. You may find it useful.

Just to make it easier for everyone to play with it, you can use the code files which I've shared here:

• HistogramChartHelper.txt
• HistogramChart.txt
• HistogramSampleMainForm.txt

Download the files, and change their extension to .cs and add them to a Windows Forms project, set up the startup form and run the project. You get an output like this:

Now having this example, you can take a look at how the histogram is generated, and then you can use the same way to add your real data instead of the random data of the sample code.

If you are interested to spline, set the chart type of Histogram series to Spline, or create a new series with the same data having Spline chart type, in the same HistogramArea.