I need a y = f(x)
formula in Excel to estimate a value. The only way I could think of to accurately find the estimate is with a NURBs curve:
There’s a lot of acceleration from 0 to 1, it’s flat out to 10, and slopes up gradually out to 100.
Degree 3 Curve
Knot Vector ( 7 knots )
index value mult delta
0 0 3
3 1 1 1
4 2 3 1
Control Points 5 non-rational points
index value
CV[ 0] (0, 5, 0)
CV[ 1] (0, 3, 0)
CV[ 2] (1, 2, 0)
CV[ 3] (10, 2, 0)
CV[ 4] (100, 5, 0)
- Does anyone know of a native Excel function that can do something like this?
- If not, does anyone know how I can evaluate a NURBs curve to get the
y
at a givenx
?
I found a macro that will evaluate a curve, but it only works on the domain. I need to evaluate it in physical space to get the y
for a specific x
.
Option Explicit
Function BSpline(u, degree, CVs)
' computes the coordinate of a point on a B-spline
' u = indpendent parametric variable of the spline
' degree of the spline
' CVs = an array of the coordinates of the control vertices (x, or y, or z)
' L. Minardi 3/21/2019
' Reference https://en.wikipedia.org/wiki/B-spline
Dim cPoints(20) As Double, numCPoints As Integer
Dim i As Integer, j As Integer, k As Integer
Dim c As Double
Dim numKnots As Integer, knotMax As Integer
Dim knots(20) As Double ' array of knot values
numCPoints = CVs.Count
k = degree
For j = 1 To numCPoints
cPoints(j) = CVs(j)
Next j
numKnots = degree + numCPoints + 1
Call KnotValues(degree, numKnots, knots, knotMax)
If u > knotMax Or u < 0# Then
BSpline = "Error, u is outside range of 0 to " & knotMax
Else
If u = knots(numKnots) Then
c = cPoints(numCPoints)
Else
' compute i based on index of u
For j = 1 To numKnots
If u < knots(j) Then
i = j - 1
j = numKnots + 1
End If
Next j
c = deBoor(k, degree, i, u, knots, cPoints)
End If
BSpline = c
End If
End Function
Function deBoor(k, degree, i, u, knots, cPoints) As Double
'deBoor recursive algorithm
' k = index. initial value = degree
' i = index indicating maximum knot number where u < knot value
' u = indpendent parametric variable
' knots = array of knot values
' cPoints = one dimensional array of control point coordinates
' reference https://en.wikipedia.org/wiki/De_Boor%27s_algorithm
Dim alpha As Double
If k = 0 Then
deBoor = cPoints(i)
Else
alpha = (u - knots(i)) / (knots(i + degree + 1 - k) - knots(i))
deBoor = deBoor(k - 1, degree, i - 1, u, knots, cPoints) * (1 - alpha) + _
deBoor(k - 1, degree, i, u, knots, cPoints) * alpha
End If
End Function
Sub KnotValues(degree, numKnots, knots, knotMax)
' set knot values for uniform B-spline
Dim j As Integer, KnotValue As Integer
KnotValue = 0
' set to 0 for initial (degree + 1) knots
For j = 1 To degree + 1
knots(j) = KnotValue
Next j
KnotValue = KnotValue + 1
' increment knot value for successive knots
' up to (degree -1)
For j = degree + 2 To (numKnots - degree - 1)
knots(j) = KnotValue
KnotValue = KnotValue + 1
Next j
' set to last value for last (degree + 1) knots
For j = numKnots - degree To numKnots
knots(j) = KnotValue
Next j
knotMax = KnotValue
End Sub