Apex of a bell curve

[jondavis1987]

I have a scatter chart with smooth lines that creates a bell curve. Is there a formula i can make based off of the data to calculate the apex of the bell curve or is there an excel function to show the apex number and label it?

 

[Perpa]

jondavis1987,
You want the values of 'x' and 'y' when the slope (rise/run) is a minimum or zero, so...for any 2 consecutive points on the bell curve (x1,y1 and x2,y2), you want to find the minimum slope,

slope = (y2-y1)/(x2-x1) = 0, OR the minimum value

You didn't say where your curve data was located, so assuming 'x' values are in column A, and 'y' values are in column B beginning in row 2, give the following code a try. It should get you pretty close. The more points you have on the curve near the Apex, the better the results.
Perpa

Sub Test()
Dim lr, rw As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For rw = 3 To lr
    'find minimum slope
     slope2 =  (Cells(rw, "B") - Cells(rw - 1, "B"))/(Cells(rw, "A") - Cells(rw - 1, "A")) 
     If rw = 3 Then GoTo Passem
     If slope2 = 0 Then      'The 'y' value may be a little low unless you have sufficient points on the curve in the Apex area.
         MsgBox ("The location of the Apex is x = " & Cells(rw, "A") + ((Cells(rw, "A") - Cells(rw - 1, "A")) / 2) & " and y = " & Cells(rw, "B"))
         Exit Sub
    End If
    If slope2 < 0 Then
        MsgBox ("The location of the Apex is x = " & Cells(rw - 1, "A") & " and y = " & Cells(rw - 1, "B"))
        Exit Sub
    End If
Passem:
    slope1 = slope2
Next rw
End Sub

 

[shg]

If you want to do it from the curve's statistics, the peak value is

={Average(Data), 1 / (StDev(Data) * SQRT(2*PI()))}

You can plot plot that as a single-point series.

 

[shg]

That may be unclear.

The peak of a normal distribution occurs at

x =AVERAGE(Data)

... and the value at the peak is

y = 1 / (STDEV(Data) * SQRT(2*PI()))

 

[jondavis1987]

The X values are D21:G21 and the y values are D29:G29. I am not great with macros but i will try to edit this to make it work. Thank you!

 

[jondavis1987]

So i don't seem to understand because I can't seem to make the macros or the formulas listed here. Feel like it's all slightly above me.

 

[shg]

Are there formulas in those cells? If so, what are they?

 

[Perpa]

jondavis1987,
Since you are using only 4 sets of x and y values, you would be better served using shg's approach.
But the macro version can be revised so for any four sets of x and y values the revised macro will locate the approximate apex of the bell curve. I have revised the macro to look for X values in D21:G21, and Y values in D29:G29.

To install the macro:
1. Copy the below code without the brackets '[ Code ] and [ /Code ]'
2. Open your workbook
3. Press ALT + F11 to open the Visual Basic Editor
4. From the window that opens, click Insert > Module
5. Paste the code you have copied onto the window that opens
6. Close the Editor and Save As a macro enabled workbook
7. To run the macro from Excel, press ALT + F8 to display the Run Macro Dialog Box, then
Double Click the macro's name 'Test2', then select 'RUN'.
Depending on the version of Excel you are using, you may have to enable macros before you can run them.
Perpa

Sub Test2()
Dim col As Long
For col = 5 To 7    'Columns E, F, and G
    'find minimum slope where y-values are in row 29, and the x-values are in row 21
     slope2 = (Cells(29, col) - Cells(29, col - 1)) / (Cells(21, col) - Cells(21, col - 1))
     If col = 4 Then GoTo Passem
     If slope2 = 0 Then      'The 'y' value may be a little low unless you have sufficient points on the curve in the Apex area.
         MsgBox ("The location of the Apex is x = " & Cells(21, col) + ((Cells(21, col) - Cells(21, col - 1)) / 2) & " and y = " & Cells(29, col))
         Exit Sub
    End If
    If slope2 < 0 Then
        MsgBox ("The location of the Apex is x = " & Cells(21, col - 1) & " and y = " & Cells(29, col - 1))
        Exit Sub
    End If
Passem:
    slope1 = slope2
Next col
End Sub

 

[jondavis1987]

Thank you. I hope to be able to try the macro today. There are formulas in the cells. Cell D21 formula is

=IFERROR((D19/D20)*100,"")

it's like that for D21:G21.

D29 formula is

=IFERROR(D28/(1+(D21/100)),"")

. Similar for D29:G29

 

[shg]

One step further back -- what's the source data for this bell curve?