Apex of a bell curve

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
421
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 18, 2012
Messages
635
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
Joined
May 7, 2008
Messages
21,829
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 7, 2008
Messages
21,829
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 31, 2015
Messages
421
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 31, 2015
Messages
421
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
May 7, 2008
Messages
21,829
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Perpa

Well-known Member
Joined
Dec 18, 2012
Messages
635
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

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
Joined
Dec 31, 2015
Messages
421
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
May 7, 2008
Messages
21,829
Office Version
  1. 2010
Platform
  1. Windows
One step further back -- what's the source data for this bell curve?
 
Master Excel Bundle

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.

Forum statistics

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