Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Feb 23, 2023

IMPRODUCT & IMCONJUGATE alternatives

Dear Experts,

                 Could you please help me with the attached Excel, and create another sheet with the same result but with different formulae? probably if it can be done in more than 2 ways?

Thanks in Advance,

Br,

Anupam

Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 23, 2023

    anupambit1797 

    The product of a complex number and its conjugate is the square of its amplitude.

    Hence
= SQRT(IMPRODUCT(L4,IMCONJUGATE(L4)) + IMPRODUCT(M4,IMCONJUGATE(M4)) )
becomes
= SQRT(IMABS(L4)^2 + IMABS(M4)^2)
or
= SQRT(SUMSQ(IMABS(L8), IMABS(M8)))
    • JosWoolley's avatar
      JosWoolley
      Iron Contributor
      Feb 24, 2023

      PeterBartholomew1 

      Just to mention that, via use of a suitable operator, 

      =SQRT(SUMSQ(IMABS(L4), IMABS(M4)))

      can be abbreviated to, for example

      =SQRT(SUMSQ(IMABS(+L4:M4)))

      or

      =SQRT(SUMSQ(IMABS(--L4:M4)))

       

      Obviously these abbreviations are only feasible if the cells being referenced form part of a contiguous range, and arguably not worth the effort where only two cells are concerned.

       

      However, given a larger range of cells, such formula abbreviation can be worthwhile. What's more, it's an interesting theoretical observation on how functions such as IMABS can be coerced into operating over an array, the more so perhaps since, with the new dynamic array functionality in Office 365, we perhaps tend to assume that a given function will operate over an array without further coercion.

       

      Regards

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Feb 24, 2023

        JosWoolley 

        Well spotted!  I got as far as referencing the range but was put off by the value error.  I should have persisted because I am used to the same issue with EOMONTH.  My preference is 

        = SQRT(SUMSQ(IMABS(+Z)))

         Mind you, from recent work I have posted on the forum, there appears to be a consensus that using a pair of adjacent cells to hold the real and imaginary parts of a complex number is more efficient computationally than converting back and forth to the Excel text representations.

        = BYROW(z, LAMBDA(z, 
      SQRT(SUMSQ(z))
  ))

         I was somewhat surprised that the new array methods and Lambda functions allowed me to implement a Fast Fourier Transform from scratch.

Resources