Forum Discussion
Would you recommend the use of Excel complex numbers where performance is required?
That's a significant performance increase, yours beats mine now! It goes to show lambda functions can scale well when built-in array shaping and multiplication functions are utilised.
Mine essentially follows the introductory section of this link https://www.osti.gov/servlets/purl/1559731 with N= N1 x N2 but without implementing the suggested algorithm with radix = 4. The attachment includes a lambda random number generator for testing - I actually came across a few references to DFTs for testing randomness but wouldn't have high expectations for my simple xorshift lambda function.
I think this is the file I sent to you attached to a LinkedIn message but I will put it here before I lose it! Having written the FFT, I made the slight modifications to produce the IFFT and then went on to implement the two functions as a convolution by complex multiplication of the the two FFTs and applying the IFFT to the product.
The uses I had in mind were for calculating the depreciation of capital expenditure or the cash balances that result from the delay between invoice and payment. What reminded me of all this was a blog post by Jon Peltier.
Improved Excel Lambda Moving Average - Peltier Tech
The Convolveλ function represents overkill, taking 2.5ms rather than 0.5ms for Jon's direct approach but I rather like the way the convolution can be written so that the average does not lag the data points. I might be a bit more competitive were we averaging 4096 points over a spread of 256.
= Convolveλ(datarange, nPoints, 32)The convolution seems to be pretty flexible in terms of having both a number of different applications and allowing experimentation to look at the effect of selecting different kernels for the smoothing. In my latest attempt I chose to use the binomial coefficients which I then looked up and found it is known as a Gaussian Filter, published in 1958.
Both the smoothness and the fit look pretty good to me, though Jon's dataset is so noisy that it is difficult to judge.
I was surprised by the work you published on GitHub. Whilst I had anticipated that there would be others working with FFT who would be far more knowledgeable than I (I had never even thought to look under the covers of the discrete form of Fourier transformations), I did not anticipate that any would be working with Excel! I wrongly assumed it would be all Python or MATLAB/Simulink.
- It was pretty fun to write and optimize it! Matlab/Python and friends are very difficult to deploy for other users. Lambda function live in standard spreadsheets, they don't even need a Macro-Enabled file!
I find it something of a surprise that it is possible to deploy quite advanced numerical algorithms as a spreadsheet formula. I suspect there is a long way to go before we see Excel solutions deployed to the full.
One thing that still troubles me is the limitation preventing the return of 'arrays of array' from helper functions. Most algorithms I implement require arrays of arrays and REDUCE/VSTACK is a cumbersome way of achieving what should be inbuilt functionality.
To have multidimensional arrays displayed as a two 2D array rather than mapped to 1D memory locations helps algorithm development because one dimension can be used to hold the input to each specific calculation whilst the other is free to to hold multiple instances of the calculation. That reduces the need for the complicated manipulation of indices.
Have you implemented a Lambda function for this or is it simply a suggestion for a worthwhile task? It is an algorithm I have thought of implementing, if only because it is one I used in the early days of finite element analysis in which the matrix represented the strain energy of possible deformations and was necessarily positive definite.
