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

 

 

Question Info


Last updated August 16, 2019 Views 25,371 Applies to:
Answer
Answer
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

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

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

How satisfied are you with this response?

Thanks for your feedback.