# Apex of a bell curve

#### jondavis1987

##### Active Member
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?

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Perpa

##### Well-known Member
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

Code:
``````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

##### MrExcel MVP
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.

Last edited:

#### shg

##### MrExcel MVP
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

##### Active Member

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

##### Active Member
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

##### MrExcel MVP

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

#### Perpa

##### Well-known Member
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 ]'
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

Code:
``````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

##### Active Member
Thank you. I hope to be able to try the macro today. There are formulas in the cells. Cell D21 formula is
Code:
``=IFERROR((D19/D20)*100,"")``
it's like that for D21:G21.

D29 formula is
Code:
``=IFERROR(D28/(1+(D21/100)),"")``
. Similar for D29:G29

#### shg

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

Replies
12
Views
203
Replies
0
Views
71
Replies
13
Views
483
Replies
9
Views
1K
Replies
0
Views
572

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,856
Messages
5,766,784
Members
425,378
Latest member
kapoor2892

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

### Which adblocker are you using?

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

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