vbaexcelcomplex-numberscubic

Find a real number in a set of complex numbers


I am looking for a way to find a real number in a set of complex numbers in excel-vba. More specifically I have a set of three results out of which one is known to be real and two are known to be complex, however, I don't know which of the results is the real one. The problem is made more complicated by the fact that due to rounding errors in intermediate calculation steps it often happens that the imaginary component of the real number doesn't cancel out to exactly 0 (as it should).

The method I'm currently considering to use consists of the following steps:

  1. Determine the value of the Real component of each of the three results.
  2. Determine the absolute value of the Imaginary component of each of the three results.
  3. Determine the minimum value of these three results.
  4. Compare each of the Absolute Imaginary components to the minimum value. When these match, take the corresponding real component as the final result.

The code would look like this:

Z1 = Application.WorksheetFunction.ImReal ( Application.WorksheetFunction.ImSum (xi1, x1i2, x1i3) )
Z2 = Application.WorksheetFunction.ImReal ( Application.WorksheetFunction.ImSum (xi1, x2i2, x2i3) )
Z3 = Application.WorksheetFunction.ImReal ( Application.WorksheetFunction.ImSum (xi1, x3i2, x3i3) )
ZIm1 = Abs ( Application.WorksheetFunction.Imaginary ( Application.WorksheetFunction.ImSum (xi1, x1i2, x1i3) ) )
ZIm2 = Abs ( Application.WorksheetFunction.Imaginary ( Application.WorksheetFunction.ImSum (xi1, x2i2, x2i3) ) )
ZIm3 = Abs ( Application.WorksheetFunction.Imaginary ( Application.WorksheetFunction.ImSum (xi1, x3i2, x3i3) ) )
ZImMin = Min (ZIm1, ZIm2, ZIm3)
If Zim1 = ZImMin Then
    ZImID = Z1
    ElseIf Zim2 = ZImMin Then
    ZImID = Z2
    Else ZImID = Z3
EndIf

I think this should work, however, I haven't tried to run it yet. Can anyone suggest a better way to find the Real solution?

This issue is part of finding the solution(s) to a cubic equation according to this method:

Thanks!


Solution

  • I would consider not only the imaginary part to be closest to zero but the relation between real and imaginary part. Example:
    z1=2,35+0,25i
    z2=14+1,3i

    where in fact z2 is closer to a real number. The measure for this is the angle between the real and complex part. IMARGUMENT(z) returns this angle. Example:

    Public Function realIndex(rng As Range) As Long
    ' returns the row index into a column of complex values closest to a real number
    
        Dim values() As Variant
        Dim angle As Double, minangle As Double, i As Long, idx As Long
    
        values = rng  ' complex numbers in values(i,1)
    
        minangle = 100#
        For i = LBound(values, 1) To UBound(values, 1)
            angle = Application.WorksheetFunction.ImArgument(values(i, 1))
            If angle < minangle Then
                minangle = angle
                idx = i
            End If
        Next i
        realIndex = idx
    End Function
    

    edit in response to comments:
    Taking abs(sin(angle)) reduces the ambiguity of a negative angle around -pi. But, as ImArgument essentially is arctan(Im(x)/Re(x)) and there is an equivalence for sin(arctan(x)), we can use this:

    Public Function MostReal(rng As Range) As Double
    ' returns from a column of complex values the one closest to a real number
    
        Dim values() As Variant
        Dim val As Double, minval As Double, absSize As Double, imSize As Double
        Dim i As Long, idx As Long
    
        values = rng  ' complex numbers in rows = values(i, 1)
    
        For i = 1 To UBound(values, 1)
            With Application.WorksheetFunction
                absSize = Abs(.Imaginary(values(i, 1)))
                imSize = .ImAbs(values(i, 1))
                val = IIf(imSize > 0#, absSize / imSize, 0#)
            End With
            If i = 1 Or val < minval Then
                minval = val
                idx = i
                If minval = 0# Then Exit For ' it doesn't get any better than this
            End If
        Next i
        realIndex = values(idx, 1)
    End Function
    

    The criterion is the ratio of the imaginary part to the absolute value of the complex number - the closer to zero, the closer to a real number. The second code returns that value (instead of the index into the column of values), and it chooses the initial minimum value in a more safe way.