• 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...

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

Bell Curve

Lasantha

Member
Dear Team,

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

thank you,
Lasantha.
 

Attachments

  • Bell Curve.xlsx
    13.4 KB · Views: 13
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
 

Attachments

  • Copy of Bell Curve.xlsx
    14.5 KB · Views: 8
Hi,

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

Lasantha.
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.
 
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

View attachment 48988

This should be the bell curve would look like
View attachment 48986


Hi James Carlo Cruz,

Thank you very much for this.

Lasantha.
 
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.
 
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
 

Attachments

  • Copy of Bell Curve.xlsx
    29.3 KB · Views: 12
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.
 

Attachments

  • Chandoo37012Bell Curve.xlsx
    17 KB · Views: 20
@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).
 
Back
Top