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?

 

[jondavis1987]

I'm plotting a proctor for soils. The D21:G21 contains moisture contents. D29:G29 contains densities. The top of the curve shows me where the optimum density and the optimum moisture is. I tried the macro on it. It's not really close enough for what i need to do. I tried some sample data with it and it produced a number that was decently far away from the apex.

 

[shg]

You could help yourself if you'd be a little more forthcoming with information. No one has any idea what data you're looking at or what you're trying do do.

Post your data, explain what it means, explain what you're trying to do, post a web link for reference, something.

If you'd invest a little effort, you would likely be well rewarded.

 

[jondavis1987]

I'm plotting basically a polynomial line with an order of 3 (I was using a scatter chart with smooth curves but i just switched to polynomial trendline) using the x cordinates from D21:G21 and the Y cordinates from D29:G29. The apex (or the top of the line before it starts going down in the curve) where it cordinates with the y axis and the x axis is what i want it to lift. So the y axis is going to be between 100 and 110 for instance. The x axis is going to be between 9 and 20. If the top of the curve on a polynomial line falls on the x axis at 14.2 and the y axis of 105.6, I would like it to automatically generate those numbers.

It charts something like this chart in the link http://www.engineeringcivil.com/det...and-the-optimum-moisture-content-of-soil.html

[TABLE="width: 617"]
<tbody>[TR]
[TD="colspan: 3"]I want something to analyze the numbers that plug into it from D21:G21 (Xaxis) & D29:G29 (Y Axis) and generates those numbers[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I've invested a fair amount of effort into this. I've given all of the information there is to have on this.

 

[shg]

You can get the coefficients of the polynomial using LINEST.

The derivative of the polynomial a3 * x^3 + a2 * x^2 + a1 * x + a0 is 3 * a3 * x^2 + 2 * a2 * x + a1

Solve that for zero using the quadratic formula.

 

[shg]

If you post your actual data in copyable format, I'll show you.

 

[jondavis1987]

https://www.dropbox.com/s/50nisl0ml754m59/Soils Workbook 1.xlsx?dl=0 I have some sample data filled into this. It's worksheet T-99

The data points are
X axis D21-13.8 E21-15.0 F21-16.4 G21-18.5
Y axis D29-102.2 E29-107.6 F29-108.7 G29-103.6

It might also change to three data points or five but if i get the basic formula of it i should be able to figure that part out on my own i think

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

21​

[/td][td="bgcolor:#F3F3F3"]

x​

[/td][td]

13.8​

[/td][td]

15​

[/td][td]

16.4​

[/td][td]

18.5​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29​

[/td][td="bgcolor:#F3F3F3"]

y​

[/td][td]

102.2​

[/td][td]

107.6​

[/td][td]

108.7​

[/td][td]

103.6​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

30​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

31​

[/td][td="bgcolor:#F3F3F3"]

exp​

[/td][td]

3​

[/td][td]

2​

[/td][td]

1​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

32​

[/td][td="bgcolor:#F3F3F3"]

coeff​

[/td][td="bgcolor:#E5E5E5"]

0.1085541​

[/td][td="bgcolor:#E5E5E5"]

-6.33521​

[/td][td="bgcolor:#E5E5E5"]

119.3858​

[/td][td="bgcolor:#E5E5E5"]

-624.134​

[/td][td][/td][td]D32:G32: {=LINEST(D29:G29, D21:G21^{1;2;3})}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

33​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

34​

[/td][td="bgcolor:#F3F3F3"]

fit​

[/td][td="bgcolor:#E5E5E5"]

102.2​

[/td][td="bgcolor:#E5E5E5"]

107.6​

[/td][td="bgcolor:#E5E5E5"]

108.7​

[/td][td="bgcolor:#E5E5E5"]

103.6​

[/td][td][/td][td]D34 and across: =SERIESSUM(D21, 3, -1, $D$32:$F$32) + $G$32[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

35​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

36​

[/td][td="bgcolor:#F3F3F3"]

exp​

[/td][td]

2​

[/td][td]

1​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

37​

[/td][td="bgcolor:#F3F3F3"]

derivative​

[/td][td="bgcolor:#E5E5E5"]

0.3256622​

[/td][td="bgcolor:#E5E5E5"]

-12.6704​

[/td][td="bgcolor:#E5E5E5"]

119.3858​

[/td][td][/td][td][/td][td]D37 and across: =D31*D32[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

38​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

39​

[/td][td]

Extrema 1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

40​

[/td][td="bgcolor:#F3F3F3"]

x​

[/td][td="bgcolor:#CCFFCC"]

16.012668​

[/td][td][/td][td][/td][td][/td][td][/td][td]D40: =(-E37 - SQRT(E37^2 - 4 * D37 * F37)) / (2 * D37)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

41​

[/td][td="bgcolor:#F3F3F3"]

y​

[/td][td="bgcolor:#CCFFCC"]

108.8618​

[/td][td][/td][td][/td][td][/td][td][/td][td]D41: =SERIESSUM(D40, 3, -1, $D$32:$F$32) + $G$32[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

42​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

43​

[/td][td]

Extrema 2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

44​

[/td][td="bgcolor:#F3F3F3"]

x​

[/td][td="bgcolor:#E5E5E5"]

22.893999​

[/td][td][/td][td][/td][td][/td][td][/td][td]D44: =(-E37 + SQRT(E37^2 - 4 * D37 * F37)) / (2 * D37)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

45​

[/td][td="bgcolor:#F3F3F3"]

y​

[/td][td="bgcolor:#E5E5E5"]

91.175644​

[/td][td][/td][td][/td][td][/td][td][/td][td]D45: =SERIESSUM(D44, 3, -1, $D$32:$F$32) + $G$32[/td][/tr]
[/table]

 

[jondavis1987]

Thank you for all the details. I have made every bit of that work except the linest function for the zero exponent. When i manually type in the -624 everything else generates correctly. Can you help me out just a little bit more please.

 

[jondavis1987]

Not sure if that was clear. The only thing in this i can't seem to generate is G32. I can't seem to make the formula work there.

 

[shg]

Select all four cells, paste the formula in the formula bar (sans curly braces), press and hold the Ctrl and Shift keys, then press Enter.