Modelling shape of a curve

[JLB123]

Hi,

In many ways, this is more a general maths question than an excel question, but have always gotten good answers here so figured I would try.

I am trying to model the shape of a curve, based on several data points that I have. However, the data points are not in a format immediately useful to excel.

Specifically, I have the following data points for the curve. Roughly speaking, the curve is an upside down parabola.

(X,Y) start point is 0,0 by definition
I then have the X end point (130)
I have the Y maximum value (500)
Finally, I have the X value of when the Y value is at it's peak (50)
Area under the curve (220)

So I have 3 points only for the curve: (0,0), (50,500), (130,0).

Turning these 3 into a curve doesn't reflect the area under the curve, which is something I would like to do. So, my question, is how to derive a formula for the curve which both passes through the 3 points, but also has the correct area.

Visually, I see this as being an adjustment to the curvature either side of the peak point, either making it more like a triangle, or less so. In terms of assumptions, I am assuming no inflection points.

For reference, I have ~600 of these curves to model, so a formula driven approach is a must.

Thanks!

 

[Ben Nevis]

Are you sure about the area under the curve being 220?
If you plot those 3 points you get triangle 500 units high with a base of 130 units, that means the area of the triangle is 32,500!

 

[JLB123]

Hi Ben,

Thanks for your reply - evidently I've forgotten a lot of my A level physics re: units!

Converting everything to helpful units (and actually using the right outputs)

X end point: 1.3 (meters)
Y max: 500 (Newtons)
X value when Y is at its peak: 0.5 (meters)
Area under curve: 590 (joules)

Gives if it were a triangle it would be 325 joules, if it were a square it would be 650, which is largely sensible.

Thanks

 

[Ben Nevis]

In your original post you say you are assuming no inflection points but if the area is 220 it is less than the area of the triangle made by the 3 known points; that means the curve would have to be a skewed bell curve.
Because the maximum point is not centred across the domain of the X values a continuous function will not give a curve with that area with no inflection points but you could produce separate curves for 0 < X <= 0.5; 0.5 <= X < 1.3

 

[Ben Nevis]

I set this up as a scatter plot in Excel and added in two intermediate points on the X axis; 0.25 and 0.9 I experimented with Y values for these 2 points and settled with 300 and 450 respectively. I added a polynomial trendline order 6 and ticked "Display Equation on chart" which gave me the following equation; y = 659.34x4 - 1780.2x3 + 246.7x2 + 1239.3x - 2E-10
Integrating from 0 to 1.3 gives an area under the curve of 446.38 which isn't quite there but you could experiment along the same lines yourself.
As an alternative to this approach you could partition the interval between 0 and 1.3 so you can produce a chart with 13 columns, adjust the height of each column by eyeballing what fits your expected distribution. add a formula to calculate the sum of the column areas Height * Width until you get as close as possible to the area you are looking for "590" and then calculate a trendline. NB you can create an equation that will fit any group of points provided you use a polynomial of a high enough degree (Excel tops out at 6 though).
Hope this helps!