Find a real number in a set of complex numbers

Question

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:

Determine the value of the Real component of each of the three results.
Determine the absolute value of the Imaginary component of each of the three results.
Determine the minimum value of these three results.
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!

These are worksheetfunctions specifically for use with complex numbers. ImReal returns the Real component of a complex number, Imaginary returns the Imaginary component of a complex number and ImSum adds up complex numbers. — Enantiomeer
well, then I suppose you have to call them like Application.WorksheetFunction.ImReal(...) and the likes — DisplayName
Oops. I didn't realise this because I copied the formulas from a worksheet and just replaced the cell references with the appropriate variables. I modified the code accordingly now. — Enantiomeer
now go on with your tests and come back if you have any issue — DisplayName

user1016274 user1016274 · Accepted Answer · 2018-08-18T12:00:01

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.

Find a real number in a set of complex numbers

1 Answers