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.

Messages:
83
Dear Team,

thank you,
Lasantha.

File size:
13.4 KB
Views:
10
2. ### GraH - GuidoWell-Known Member

Messages:
751

#### Attached Files:

• ###### Bell Curve.xlsx
File size:
13.4 KB
Views:
1
Lasantha likes this.
3. ### LasanthaMember

Messages:
83
Hi,

actually i need curved trend line. like attached.

Lasantha.

File size:
11.1 KB
Views:
24
4. ### James Carlo CruzMember

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

This should be the bell curve would look like

#### Attached Files:

• ###### Copy of Bell Curve.xlsx
File size:
14.5 KB
Views:
4
Lasantha and GraH - Guido like this.
5. ### GraH - GuidoWell-Known Member

Messages:
751
I thought already that looked way too simple... 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.

Messages:
4,820
7. ### LasanthaMember

Messages:
83

Hi James Carlo Cruz,

Thank you very much for this.

Lasantha.

Messages:
83
9. ### HuiExcel NinjaStaff Member

Messages:
11,482

see attached file

File size:
23.7 KB
Views:
5
10. ### GraH - GuidoWell-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. ### ChihiroExcel 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 - GuidoWell-Known Member

Messages:
751
Dearly noted!

Messages:
83
14. ### LasanthaMember

Messages:
83
Dear All,

Thank you very much for your kind support.

Lasantha.
15. ### LasanthaMember

Messages:
83
Dear Sir,

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

Lasantha.

File size:
29.9 KB
Views:
7
16. ### HuiExcel NinjaStaff 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. ### HuiExcel NinjaStaff 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

see attached file

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

#### Attached Files:

• ###### Copy of Bell Curve.xlsx
File size:
29.3 KB
Views:
1
Thomas Kuriakose and Lasantha like this.
18. ### LasanthaMember

Messages:
83
Dear Sir ,

How you get the -3SD to +3SD

thank you.
19. ### HuiExcel NinjaStaff Member

Messages:
11,482
Itâ€™s a property of Normal Distributions
Lookup Normal Distribution in Wikipedia
Lasantha likes this.
20. ### p45calWell-Known Member

Messages:
1,154
In the attached I have:

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:

• ###### Chandoo37012Bell Curve.xlsx
File size:
17 KB
Views:
5
Thomas Kuriakose and Lasantha like this.

Messages:
83
THANK YOU
22. ### ChihiroExcel 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. ### p45calWell-Known Member

Messages:
1,154
Mine is normal distributionâ€¦ isn't it? I hope it is.

Messages:
4,820