Forum Discussion
IMPRODUCT & IMCONJUGATE alternatives
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)))
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
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.
- Thanks Peter and Jos ..
