I have a range on several sheets in the same workbook, with 35 off "X" values in a column and need to return "Y" values corresponding to a customized 5th Polynomial Curve / Function on a fixed sheet called "DB" with supporting "x" values on C37:C76 and "y" values on D37:D76 for the aforementioned curve.
Essentially a graphical VLOOKUP function instead of a table with 1000s of rows.
As a once-off calculation, the normal calculation method works 100% but it becomes an issue if you need to calculate Y for several values of X over several sheets. I thought a UDF would work, but I'm at a loss of how to actually do it. I tried a few code variations and cleaned it up for this post as a starting point.
Any help to make this UDF work would be greatly appreciated.
Function ADJ(X As Single) As Single
'X = FUNCTION INPUT VALUE
'Y = RESULT = ADJ = C5*X^5 + C4*X^4 + C3*X^3 + C2*X^2 + C1*X^1 + A
C1 = Sheets(11).Evaluate("=INDEX(LINEST(R37C3:R76C3, R37C4:R76C4^{1,2,3,4,5}), 1, 5)")
C2 = Sheets(11).Evaluate("=INDEX(LINEST(R37C3:R76C3, R37C4:R76C4^{1,2,3,4,5}), 1, 4)")
C3 = Sheets(11).Evaluate("=INDEX(LINEST(R37C3:R76C3, R37C4:R76C4^{1,2,3,4,5}), 1, 3)")
C4 = Sheets(11).Evaluate("=INDEX(LINEST(R37C3:R76C3, R37C4:R76C4^{1,2,3,4,5}), 1, 2)")
C5 = Sheets(11).Evaluate("=INDEX(LINEST(R37C3:R76C3, R37C4:R76C4^{1,2,3,4,5}), 1, 1)")
A = Sheets(11).Evaluate("=INDEX(LINEST(R37C3:R76C3, R37C4:R76C4^{1,2,3,4,5}), 1, 6)")
ADJ = C5 * X ^ 5 + C4 * X ^ 4 + C3 * X ^ 3 + C2 * X ^ 2 + C1 * X ^ 1 + A
End Function
Running it as a Sub:
Option Explicit
Sub ADJ1()
Dim ADJ As Variant
Dim X As Variant
Dim A As Variant
Dim C1 As Variant
Dim C2 As Variant
Dim C3 As Variant
Dim C4 As Variant
Dim C5 As Variant
X = 3
C1 = Sheets(11).Evaluate("=INDEX(LINEST(D37:D76, C37:C76^{1,2,3,4,5}), 1, 5)")
C2 = Sheets(11).Evaluate("=INDEX(LINEST(D37:D76, C37:C76^{1,2,3,4,5}), 1, 4)")
C3 = Sheets(11).Evaluate("=INDEX(LINEST(D37:D76, C37:C76^{1,2,3,4,5}), 1, 3)")
C4 = Sheets(11).Evaluate("=INDEX(LINEST(D37:D76, C37:C76^{1,2,3,4,5}), 1, 2)")
C5 = Sheets(11).Evaluate("=INDEX(LINEST(D37:D76, C37:C76^{1,2,3,4,5}), 1, 1)")
A = Sheets(11).Evaluate("=INDEX(LINEST(D37:D76, C37:C76^{1,2,3,4,5}), 1, 6)")
'ADJ = C5 * X ^ 5 + C4 * X ^ 4 + C3 * X ^ 3 + C2 * X ^ 2 + C1 * X ^ 1 + A
ADJ = X ^ 5 + X ^ 4 + X ^ 3 + X ^ 2 + X ^ 1
MsgBox ADJ
End Sub
I have a solution and opinion. Still I am not sure that the opinion is quite correct. Opinion: I would never use UDF with reference to a range. The Range you are working with must be an argument of the function. See this solution:
Public Function ADJ(X As Range, Y As Range, xvalue As Double) As Double
Dim pol As Variant
Dim arrPolNth As Variant
Dim n As Integer
n = 5
arrPolNth = Array(1, 2, 3, 4, 5)
pol = Application.LinEst(Y, Application.Power(X, arrPolNth))
Dim i As Integer
For i = LBound(pol) To UBound(pol)
ADJ = ADJ + Application.Power(xvalue, n - i + 1) * pol(i)
Next i
End Function
It is not the nicest because the order of the polynomial should be also a parameter.