Finding partial derivatives in Excel

Question

What I want to do:

I am trying to write a user-defined function in Excel to calculate the partial derivative of a function,
f(x, y,...n), with respect to, x. My initial approach was to have the function change the value of x by ±0.01 %, and record the response in f. Then the slope is simply the partial derivative.

My function should work like this:

    =PARTIAL(A2, A1)

Where cell A1 is some function that relies on the cells A2, A3, A(n). My function would return the partial derivative of A1 with respect to A2.

The full non-working code is shown below:

Function PARTIAL(x As Range, f As Range) As Double
    Dim e As Double
    e = 0.0001 * x

    Dim y2 As Double
    Dim y1 As Double

    x = x + e
    y2 = f

    x = x - (2 * e)
    y1 = f

    x = x + e

    PARTIAL = (y2 - y1) / (2 * e)
End Function

Why I can't do it:

The problem is that my function relies on the ability to modify A2, and record the response in A1. Unfortunetly, Excel is very strict about not allowing functions to modify the worksheet they are operating in, and calls like that below will throw an error:

    Function myFunction(x as Range) as Double
        x.Cells(1, 1).Value = 5
    End Function

What I would like to know:

Is there a workaround for this problem? Does someone have a better way to conveniently calculate a partial derivative in Excel. I am trying to build a bigger function that eventually propagates the error through a series of analytical calculations, and this propagation relies on the partial derivative.

Thank you,

Michael

Answer 1

You could use a Sub rather than a UDF. Say A1 contains the function:

=3*A2^3-12*A2^2+7*A2-11+1/A3

with A2 containing -2 and A3 containing 1.

We want to calculate the partial and store its value in B1:

Sub MAIN()
  Call partiall(Range("A2"), Range("A1"))
End Sub

Sub partiall(x As Range, f As Range)
  Dim e As Double

  e = 0.0001 * x.Value
  x.Value = x.Value + e
  DoEvents
  y2 = f.Value

  x.Value = x.Value - (2 * e)
  DoEvents
  y1 = f.Value

  x.Value = x.Value + e

  partial = (y2 - y1) / (2 * e)
  Range("B2").Value = partial
End Sub

EDIT#1:

You can use a UDF() if you get the formula from the worksheet, but compute the ∆y/∆x within the UDF():

Public Function PartialDerivative(x1 As Range, x2 As Range, f As Range) As Double
  Dim sFormula As String, addy1 As String, v1 As Double
  Dim e As Double

  sFormula = f.Formula
  addy1 = x1.Address(0, 0)
  v1 = x1.Value
  e = 0.0001 * v1

  y2 = Evaluate(Replace(sFormula, addy1, "(" & v1 + e & ")"))
  y1 = Evaluate(Replace(sFormula, addy1, "(" & v1 - e & ")"))
  PartialDerivative = (y2 - y1) / (2 * e)

End Function

EDIT#2:

To test the accuracy of the UDF(), we can compare it to the first derivative formula

So in C2 enter:

=9*A2^2-24*A2+7

and the results are nearly identical: