How can I port bell shape?

danial

Board Regular
Joined
Apr 29, 2006
Messages
107
I have a data;
Score
900
920
933
890
1010
1000

can you show me to how to port graph bell shape
Example
Step 1
Find mean ...
Step 2

Please accept my apologize with my bad enlish write.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
L

Legacy 14611

Guest
I can't write English well.
Nevertheless could you try re-explaining what you mean by "port graph bell shape"?

Does this mean you want to fit the normal or Gaussian (or "bell shaped" - although it's not the only bell shape) curve to your data?
 

danial

Board Regular
Joined
Apr 29, 2006
Messages
107
I need to create graph bell shape from my raw data
I need to know that bell shap will display wide of bell base
I have ever seen another person show curve -3,-2,-1,0,1,2,3
not sure I should trasform my score to any value before create graph
 
L

Legacy 14611

Guest
not sure I should trasform my score to any value before create graph
Whichever way you like.
Run the following code on Sheet1 of a workbook. It uses your untransformed data:
Code:
Sub bellcurve()
[a:f].ClearContents
Dim m As Integer, n As Integer, dt()
n = 6

'specify data to be used
ReDim dt(1 To n)
dt(1) = 900: dt(2) = 920: dt(3) = 933
dt(4) = 890: dt(5) = 1010: dt(6) = 1000
'calculate mean and standard deviation
For j = 1 To n
mean = mean + dt(j): std = std + dt(j) ^ 2
Next j
mean = mean / n
std = ((std - n * mean ^ 2) / (n - 1)) ^ 0.5
[e1] = Format(mean, "0.00")
[e2] = Format(std, "0.00")

'generate normal curve with average and standard deviation which are ...
'...the same as for the specified data
m = 26
[a2] = mean - 3 * std: [a3] = mean - 2.75 * std
[a2:a3].AutoFill Range("A2:A" & m), xlFillSeries
Range("A2:A" & m).NumberFormat = "0"
Range("B2:B" & m).NumberFormat = "0.0000"
For i = 2 To m
    x = Cells(i, 1).Value
    Cells(i, 2) = 0.39894 * Exp(-0.5 * ((x - mean) ^ 2) / (std ^ 2)) / std
Next i
Cells(m + 1, 1).Resize(n, 1) = Application.Transpose(dt)
Cells(m + 1, 3).Resize(n, 1) = 0.0001
Cells(m + 1, 1).Resize(n, 3).Font.ColorIndex = 3
Cells(m + 1, 1).Resize(n, 1).Font.Bold = True
[b1] = "Freq": [c1] = "Data marker"
[f1] = "Data Average": [f2] = "Data StDev"

'Charting
Charts.Add
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C" & (n + m))
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart.Axes(xlCategory)
        .MinimumScale = mean - 3.5 * std
        .MaximumScale = mean + 3.5 * std
    End With
    With ActiveChart.Legend.LegendEntries(2).LegendKey.Border
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With ActiveChart.Legend.LegendEntries(2).LegendKey
        .MarkerBackgroundColorIndex = 3
        .MarkerSize = 5
    End With
Sheets("Sheet1").[a1].Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top