Forum Discussion
Convolution of a vector and a kernel with offsetting using dynamic arrays
The video to which you provided the link is very good. I had watched others by the same presenter.
"too complicated for my use as it needed recursion"
I try to avoid recursion where possible but it can be valuable, especially when it is not possible to predict in advance how many steps will be needed. I have a non-recursive version of the convolution but it is no simpler.
"and a lot of defined names", I have avoided direct cell referencing for the past 8 years, preferring instead to use names both for identifying ranges and to define formulas for evaluation. There are plusses, I no longer need to use relative references, so a formula will evaluate to the same result wherever I place it, and I no longer need to worry about following precedents to help decode a formula.
"so I went for my own solution which involved offsetting the vector while the kernel stays which is the opposite of what is shown in the video" One learns more by going one's own way! The convolution has an inherent symmetry allowing the role of the value array and the kernel to be reversed.
I did try using INDEX in place of OFFSET in case the input range was being dereferenced to produce an array rather than a reference but without success. Sorry about that.
An advantage of Lambda functions is that they are not as difficult to deploy as they are to develop! The first file attached has links to the theoretical basis, which is interesting but something of a nightmare nonetheless. I also applied the Convolve function to a ModelOff problem recently, as presented by Diarmuid Early . The links to his problem solution show how traditional techniques may be used, especially if you are a World-class Excel modeller!
The video to which you provided the link is very good. I had watched others by the same presenter.
"too complicated for my use as it needed recursion"
I try to avoid recursion where possible but it can be valuable, especially when it is not possible to predict in advance how many steps will be needed. I have a non-recursive version of the convolution but it is no simpler.
"and a lot of defined names", I have avoided direct cell referencing for the past 8 years, preferring instead to use names both for identifying ranges and to define formulas for evaluation. There are plusses, I no longer need to use relative references, so a formula will evaluate to the same result wherever I place it, and I no longer need to worry about following precedents to help decode a formula.
"so I went for my own solution which involved offsetting the vector while the kernel stays which is the opposite of what is shown in the video" One learns more by going one's own way! The convolution has an inherent symmetry allowing the role of the value array and the kernel to be reversed.
I did try using INDEX in place of OFFSET in case the input range was being dereferenced to produce an array rather than a reference but without success. Sorry about that.
An advantage of Lambda functions is that they are not as difficult to deploy as they are to develop! The first file attached has links to the theoretical basis, which is interesting but something of a nightmare nonetheless. I also applied the Convolve function to a ModelOff problem recently, as presented by Diarmuid Early . The links to his problem solution show how traditional techniques may be used, especially if you are a World-class Excel modeller!