Replicate a range pattern in another range

[Marcelo Machado]

hi everyone.
I've searched something similar in forum to avoid repeated questions, but I didn't find it. Sorry if it's repeated.

I have a specific range pattern that I'd like to repeat it every time a occurrence appear.
Look at the file. We have 3 regions:
- the pattern I'd like to replicate
- the occurrences
- the result I need (with no formula)

The help I need is to reach these formula that return the result I need.
I've included a fourth region just to show you how the formula should work to return the expected result.

I hope I made my point clear.
Thanks in advance.
Marcelo Machado.

 

[Peter Bartholomew]

What you have asked for is known as a 'convolution' (sometimes as a 'spread' depending on the application domain). The difficult part of the solution is getting the terms referenced using INDEX to sum as an array. This requires array coercion involving the IF and N functions.

The attached is a quick edit of a file I had lying around from 2015, so it is not as fresh in my memory than it might be!

 

[Marcelo Machado]

Tks a lot, Peter! Elegant solution.
It's amazing what we can do with excel.
See you.