1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Bell Curve

Discussion in 'Discuss Data Visualizations and Charting' started by Lasantha, Jan 17, 2018.

  1. Lasantha

    Lasantha Member

    Messages:
    83
    Dear Team,

    Could you please help me to add a bell curve to attached details.

    thank you,
    Lasantha.

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751

    Attached Files:

    Lasantha likes this.
  3. Lasantha

    Lasantha Member

    Messages:
    83
    Hi,

    thanks for the reply,
    actually i need curved trend line. like attached.

    Lasantha.

    Attached Files:

    • img.jpg
      img.jpg
      File size:
      11.1 KB
      Views:
      24
  4. James Carlo Cruz

    James Carlo Cruz Member

    Messages:
    40
    Hi Lasantha,

    In order to have a bell curve like the attached file, your data should be more precise. To get the output you want you need to get the average score and the Standard Deviation for your data.

    To get the Average Score or Mean of your data you need to get total score divide by Count of score you may use =Sum(E4:E8)/Count(E4:E8) or simply =Average(E4:E8)

    To get the Standard Deviation use the formula =STDEV.S()

    Once done, you can write this formula beside the first score :
    =NORM.DIST(E4,$H$1,$H$2,FALSE)

    Then Insert Scatter graph and choose Scatter with Smooth Lines

    I have attached the file that you have sent, but it will not result to a bell curve.
    I have created a sample for you. My advise is you have the score data sort it first to lowest to highest.

    Here is how your bell curve would look like if I use the data you sent

    upload_2018-1-17_22-43-10.png

    This should be the bell curve would look like
    upload_2018-1-17_22-41-33.png

    Attached Files:

    Lasantha and GraH - Guido like this.
  5. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    I thought already that looked way too simple... :rolleyes: I was going to suggest to look in the analysis tool pack histogram (activate the add-on). But it seems to miss the median curve you are after.
    In the mean time some-one else has replied it seems with something that makes sense.
    Lasantha likes this.
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,820
  7. Lasantha

    Lasantha Member

    Messages:
    83

    Hi James Carlo Cruz,

    Thank you very much for this.

    Lasantha.
  8. Lasantha

    Lasantha Member

    Messages:
    83
  9. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,482
    upload_2018-1-18_0-24-57.png
    see attached file

    Attached Files:

  10. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    I should say, thank you for the question Lasantha.
    Actually learned that Bell is Gaussian. Jargon can be a b....
  11. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,820
    Bell curve isn't really a jargon, but more a layman's term. ;)

    Normal Curve/Distribution is a statistical term and Gaussian a mathematical term.
    GraH - Guido likes this.
  12. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    Dearly noted! :)
  13. Lasantha

    Lasantha Member

    Messages:
    83
  14. Lasantha

    Lasantha Member

    Messages:
    83
    Dear All,

    Thank you very much for your kind support.

    Lasantha.
  15. Lasantha

    Lasantha Member

    Messages:
    83
    Dear Sir,

    i am unable to understand following calculation. Could you please explain this to me. (orange colored)

    Lasantha.

    Attached Files:

    • BC.png
      BC.png
      File size:
      29.9 KB
      Views:
      7
  16. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,482
    The Mean is 1.99 (~2)
    the SD is 0.88 (~1)
    so a Normal Curve is 99.7% between -3SD to +3SD
    so that is 2 - 3x1 to 2+3x1
    = -1 to 5
    hence the 5 points on the chart
    Thomas Kuriakose and Lasantha like this.
  17. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,482
    Here is a more accurate version that has more data points and uses the secondary axis
    I was rushed last night
    upload_2018-1-18_18-59-4.png

    see attached file

    You can remove the secondary X Axis (Upper) when you understand it

    Attached Files:

    Thomas Kuriakose and Lasantha like this.
  18. Lasantha

    Lasantha Member

    Messages:
    83
    Dear Sir ,

    How you get the -3SD to +3SD

    thank you.
  19. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,482
    It’s a property of Normal Distributions
    Lookup Normal Distribution in Wikipedia
    Lasantha likes this.
  20. p45cal

    p45cal Well-Known Member

    Messages:
    1,154
    In the attached I have:
    upload_2018-1-18_15-41-40.png
    It's a nicer fit?

    Where:
    • the orange plot is a 5-point scatter plot smoothed (it may be good enough)
    • the grey plot is a 40-point scatter unsmoothed
    SD formula swiped from Hui.

    Attached Files:

    Thomas Kuriakose and Lasantha like this.
  21. Lasantha

    Lasantha Member

    Messages:
    83
    THANK YOU
  22. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,820
    @p45cal

    I assumed op needed normal distribution.

    I assumed it was for adjusting score using grading curve (i.e. adjust actual scores to yield normal distribution or close to it).
  23. p45cal

    p45cal Well-Known Member

    Messages:
    1,154
    Mine is normal distribution… isn't it? I hope it is.
  24. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,820

Share This Page