User Profile
MarkolaKrai
Copper Contributor
Joined 2 years ago
User Widgets
Recent Discussions
Convolution of a vector and a kernel with offsetting using dynamic arrays
Hey guys, hope you doing great! I have been playing with some data at work and wondered what would happen if i made some kernel to convolve with my main vector to smooth it with a moving-average like operation as shown in this awesomevideoby Grant at 3Blue1Brown I started searching the forum for some solution to this and came by some made byPeterBartholomew1while discussing about Fourier Transforms, but i found the solution to be too complicated for my use as it needed recursion and a lot of defined names, 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. The first thing i thought was that i needed a sequence that could be used to offset the vector so i could use this offsetted vector to SUMPRODUCT with the kernel and since the size of the convolution must be: vector + kernel - 1 by definition i made a formula to make this sequence: =SEQUENCE( ROWS(vector) + ROWS(kernel) - 1; 1; -(ROWS(kernel) - 1); 1 ) *im using a vector of 100 rows and a kernel of 4 rows* This sequence then starts at -3 and goes to 99 one by one thus obeying convolution size definition, but why do i need it in the first place? so i can generate an array of height 4 (kernel size) which offsets the vector taking the sequence as its row offset argument: =TRANSPOSE(OFFSET(vector;A4;0;4)) where A4 is the first result of the sequence formula, now i can drag down the formula for all numbers in the sequence and it will generate the output i need to transpose again and then sumproduct: =SUMPRODUCT(TRANSPOSE(F4#);kernel) where F4# is the output of the transpose offset formula above and then i drag down for each result and voila the result of the convolution is done (assuming the kernel was already flipped) Now to make it dynamic i first made a formula that takes the vector and the kernel and automatically generates the sequence: =LAMBDA(vector1; kernel1; LET( vector_r; ROWS(vector1); kernel_r; ROWS(kernel1); convolution_r; ((vector_r) + (kernel_r) - 1); offset_sequence; SEQUENCE(convolution_r;1;-(kernel_r - 1)); offset_sequence ) )(vector; kernel) And a second formula to take the results: =MAP( M4#; LAMBDA(seq; SUMPRODUCT( OFFSET( vector; seq; 0; ROWS(kernel) ); kernel ) ) ) where M4# is the result of the first dynamic formula This does the trick but has space for improvement, first of all the vector needs space to be pad with zeroes which should be handled inside the formula but i couldnt do it. Second, i should be able to combine the two formulas at least in my head it should be possible i tried to do it but dindnt work... ill be leaving my test file to show it more properly what i have done The goal here is to learn how to properly pad arrays for this kind of offsetting and how to properly combine these formulas if possible, and, if this is not proper at all i would like to know... Also note: i use semicolon instead of commas at the formula because i live in Brazil and the formula syntax here works like that for some reason...Solved3.2KViews0likes2CommentsSet theory operations with dynamic arrays
I wonder if any of you have figured this out already (and i would like to have feedback about what i have done so far), i have been playing with columns of data and creating easy set theory operations. The addition of VSTACK and TOCOL basically nailed the sum operation as i can just select an entire array and merge it easily (and then apply a UNIQUE if i want to), the subtraction and intersection are not that straightforward though... My approach was fairly simple and also known already (as shown here How can we perform common set operations (union, intersection, minus) in MS Excel?) but i wanted a solution that was neither VBA or ADD-IN based, and of course, that used dynamic arrays. For the subtraction operation i started with: =FILTER(list1 , COUNTIF( list2 , list1) = 0) As the second argument of COUNTIF is the list1 itself the size must be the same so i can apply FILTER to it and get only the elements on list1 that did not appear in list2, a similar solution without COUNTIF but with MATCH would be: =FILTER(list1 , NOT(ISNUMBER(MATCH(list1 , list2 , 0)))) The approach with MATCH is actually better because it can be substituted with XMATCH and used wildcards and so on, but the main reason is that MATCH can accepct a lookup_vector that is a VSTACK or a TOCOL (an array instead of a range) which COUNTIF cannot handle (for reasons), the thing is now i can have a more general solution: = LAMBDA( list1, list2, FILTER( list1, NOT(ISNUMBER(MATCH(list1, list2, 0))))) (listA , VSTACK(listB, listC)) And with this anonymous lambda i can pass the parameters as ranges or arrays and get the difference between combined sets in a general formula. Now for the intersection, i have used a similar solution to the difference since i need to get values that are present in both lists at the same time so basically: COUNTIF Solution: =FILTER(list1 , COUNTIF( list2 , list1) > 0) MATCH Solution: =FILTER(list1 , ISNUMBER(MATCH(list1 , list2 , 0))) The problem is that i was not able to make a formula that could do intersection with more lists in a general way, sure i could just add more conditions to the filter using boolean logic and repeating the arguments for each list with a * sign, but this is far from ideal compared to the difference solution. I started to work with some BYROW and BYCOL combinations but as you guys know nested arrays are not supported, i managed to get away with it in Google Sheets using: =FILTER(list1, BYROW( ArrayFormula( BYCOL(matrix_lists, LAMBDA(main, COUNTIF(main, list1)))), LAMBDA(matrix, PRODUCT(matrix) > 0))) The idea is to create a COUNTIF (or MATCH with a few tweaks) result for each column and then do the product of each row of results and FILTER out the ones that had a zero because that means the value does not appear in all lists at the same time. Still i was not capable of implementing this idea in Excel as i dont know how to overcome nested arrays not being supported, does any of you have some ideas to share?5.2KViews0likes5Comments
Groups
Recent Blog Articles
No content to show