# How does excel plot smooth curves

I have parameterized a shape in excel. It plots a smooth curve, and I am perfectly fine with the shape it gives. Now, I want to find out the way, the smooth curves are fit, for me to calculate the curve data at very small intervals.

I have the data of the curve, for which excel has plotted a smooth curve as follows,

 x y -0.25 0 1.53144E-17 0.25 2 0.75 5 1.5 15 0.45 20 0.15 20.15 -1.83772E-17

I plot the above data using scatter with smooth lines option. Now, I want to find out the values of Y, for this curve that excel has fit, as a smooth line, for smaller intervals of X,

x

-0.25

-0.23

-0.2

-0.18

and so on..

I need to find what is the value of y, for this curve that excel has fit (by scatter with smooth lines option), for different intervals of X

| I don't think MS has documented it but detailed analysis shows that the smooth line option is a type of Catmull-Rom spline as is widely used in Computer Graphics. An adjustment to the basic method is applied if consecutive points are a factor of three or more apart so as to reduce the amount by which the curve overshoots points. A UDF that applies this method is at the end of this post. (The xlrotor link uses Bezier coordinates to achieve the same result, but it won't always match exactly when points are unevely spaced due to a different scaling method).

In this case, having created the chart you could enter with Ctrl+Shift+Enter: =ChartCurve(D2) in E2:F2 for instance. This returns the X and Y values at position D2 which is a value between 0 and 6. Filling down twenty rows and running the TestExample code below gives the following results. Note that the points between 16 and 19 can vary depending on how the chart is sized due to the interval lengths between (5,1.5) and (15,0.45) being more than three times the distance between (15,0.45) and (20,0.15). Also note that the values may not be unique, for example (20.15,-1.83772E-17) and (20.15,0.134) both lie on the curve.

X    Y
0    0.25
1    0.509589535
2    0.75
3    1.221875
4    1.468256267
5    1.5
6    1.44596958
7    1.358697456
8    1.255555556
9    1.143326861
10    1.025724039
11    0.90530631
12    0.784278921
13    0.665075565
14    0.551240559
15    0.45
16    0.370116539
17    0.306375804
18    0.25374806
19    0.206844016
20    0.15

Sub TestExample()

Dim i As Integer

Application.MaxChange = 0.00000000001
Application.MaxIterations = 1000

For i = 0 To 20
Cells(i + 2, 5).GoalSeek i, Cells(i + 2, 4)
Next i

End Sub

___________
Function ChartCurve(Position As Double, Optional Series = 1, Optional ChartObj = 1)

'Returns x,y values at a given position along a smoothed chart line

Dim Chrt As Chart, ChrtS As Series, A, i As Integer, n As Integer, _
s As Double, t As Double, l(1) As Double, p(1, 3) As Double, _
d(1, 2) As Double, u(2) As Double, q(1) As Double, z As Double

Application.Volatile

Set Chrt = Application.Caller.Worksheet.ChartObjects(ChartObj).Chart
Set ChrtS = Chrt.SeriesCollection(Series)

l(0) = (Chrt.Axes(xlCategory).MaximumScale - _
Chrt.Axes(xlCategory).MinimumScale) / Chrt.PlotArea.InsideWidth
l(1) = (Chrt.Axes(xlValue).MaximumScale - _
Chrt.Axes(xlValue).MinimumScale) / Chrt.PlotArea.InsideHeight

A = Array(ChrtS.XValues, ChrtS.Values)
n = UBound(A(0)) - 2
s = Int(Position) + (Position = n + 1)
t = Position - s

For i = 0 To 1
p(i, 1) = A(i)(s + 1)
p(i, 2) = A(i)(s + 2)
p(i, 0) = A(i)(s - (s = 0)) - (s = 0) * (p(i, 1) - p(i, 2))
p(i, 3) = A(i)(s + 3 + (s = n)) + (s = n) * (p(i, 1) - p(i, 2))
d(i, 0) = (p(i, 2) - p(i, 1)) / l(i)
d(i, 1) = (p(i, 2) - p(i, 0)) / l(i) / 3
d(i, 2) = (p(i, 3) - p(i, 1)) / l(i) / 3
Next i

For i = 0 To 2
u(i) = d(0, i) ^ 2 + d(1, i) ^ 2
Next i
z = (u(0) / WorksheetFunction.Max(u)) ^ 0.5 / 2

For i = 0 To 1
q(i) = t ^ 2 * (3 - 2 * t) * p(i, 2) + _
(1 - t) ^ 2 * (1 + 2 * t) * p(i, 1) + _
z * t * (1 - t) * (t * (p(i, 1) - p(i, 3)) + _
(1 - t) * (p(i, 2) - p(i, 0)))
Next i
ChartCurve = q

End Function

·

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?