+ Reply to Thread
Results 1 to 10 of 10

Create a chart with best-fit bell curve for set of data

  1. #1
    Registered User
    Join Date
    04-04-2021
    Location
    Europe
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    7

    Create a chart with best-fit bell curve for set of data

    Hello everyone,

    I have percentage ranges and the number of times that values are within each range.
    For instance, range -2% to 0% contains 3 values, range 0% to 2% contains 6 values, range 2% to 4% contains 2 values. Though in reality I have far more ranges than three.

    I would need to draw a chart with one column per range, which also shows a bell curve of the results.
    Something like the attached drawing.

    If this is feasible, can you please explain how to do it?

    Thanks,
    E

    example.png
    Last edited by 6StringJazzer; 04-05-2021 at 09:59 AM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,932

    Re: Create a chart with columns and a bell curve

    If you attach a file with your data, I can show you how to do the chart.

    Please see yellow banner at the top of the page.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-04-2021
    Location
    Europe
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    7

    Re: Create a chart with columns and a bell curve

    Attaching a sample spreadsheet.

    I would need to add a bell curve to the column chart.
    Attached Files Attached Files

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,932

    Re: Create a chart with columns and a bell curve

    It looks to me like you want to find the best-fit normal distribution for your data. Is that correct? This is more than a chart problem. It requires some statistical analysis. I will take a look at this but in the meantime someone else may already have a solution in hand to offer.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,371

    Re: Create a chart with best-fit bell curve for set of data

    Assuming the values in F2 and G2 represent your best fit normal distribution (so, I'm assuming you already know how to do that), it looks to me like you just need to compute the values for the normal distribution (probability density function PDF -- I expect you can use Excel's built in NORMDIST() function(s)), and then add them to the chart. Here's what I did:

    1) Excel's NORMDIST() function cannot use the text strings in column C, so I add a column of corresponding numbers. I chose the midpoint for each bin (0.29, 0.27, etc.) and add those to I2:I36.
    2) Then enter a NORMDIST() function in J2 =NORMDIST(I2,$F$2,$G$2,FALSE) (note the mix of relative and absolute references). Copy/paste/fill into J2:J36.
    3) Add J2:J36 to the chart. You can copy/paste to do this, or you can use the Select Data dialog.
    4) Select the new data series and change the chart type to a regular line chart to complete the column + line combination chart.

    Is that what you need to do?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Registered User
    Join Date
    04-04-2021
    Location
    Europe
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    7

    Re: Create a chart with best-fit bell curve for set of data

    Sorry, I'm not sure I want to make it a statistical problem.
    I calculated mean and standard deviation because a couple of tutorials I found on the web said they were needed. However, If I draw a curve based on the mean and the deviation from it, I don't get the result I need.
    I'm not trying to chart the probability of events. I simply need a curve which represents the existing data.

    I've tried the 'Scatter with smooth lines' but it's not shaped like a bell curve: For groups with frequency zero, the line is flat on the axis.

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,932

    Re: Create a chart with best-fit bell curve for set of data

    "I simply need a curve which represents the existing data." Your data is not a normal distribution, so it cannot be shown as a bell curve. The only way to do what you showed in your image above is to synthesize new data.

    When you have a scatter plot of test points, Excel has built-in features to calculate trend lines. It can do linear, quadratic, logarithmic, etc., to automatically find the best fitting curve for the data. But a normal distribution (bell curve) is not one of those options, so the only way to do it is how MrShorty described.

  8. #8
    Registered User
    Join Date
    04-04-2021
    Location
    Europe
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    7

    Re: Create a chart with best-fit bell curve for set of data

    Alright, I followed MrShorty's instructions and now have the chart as shown in the attached spreadsheet.
    I'm not sure that I've done everything correctly, but I got a graph similar to what I was looking for.
    I then changed the two axes' bounds so that the curve is above the bars, which probably means I'll have to manually adjust the bounds if the values change. I can probably deal with that.

    Do you confirm that I've correctly followed the steps (except modifying the axes)?
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,371

    Re: Create a chart with best-fit bell curve for set of data

    That looks fine. I am not fond of using the secondary axis in this way, because I feel it obscures the comparison between the histogram and the bell curve, but it's your chart. If you like the way it turned out, then keep it.

  10. #10
    Registered User
    Join Date
    04-04-2021
    Location
    Europe
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    7

    Re: Create a chart with best-fit bell curve for set of data

    It's just that I once saw a chart the had the bell curve above the histogram and it looked good to me.
    I can always give it it a try with axes aligned.

    Thanks for your help, MrShorty!
    Thanks to 6StringJazzer too!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to create bell curve chart
    By Arpita_Excel in forum Excel General
    Replies: 2
    Last Post: 07-17-2015, 01:45 AM
  2. Replies: 2
    Last Post: 02-19-2015, 02:00 PM
  3. Bell Curve Chart
    By Michellesd in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-26-2013, 02:45 PM
  4. [SOLVED] Create bell curve
    By Starkey in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-22-2013, 08:13 PM
  5. [SOLVED] Create a Skew bell curve chart
    By jeffreybrown in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 06-30-2013, 09:10 PM
  6. How to create Bell Curve
    By Madan in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-30-2009, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1