SUM and OFFSET Formula

[idontexcelinexcel]

Hello, I'm working on a forecast project.
need to come up with a formula (I think it's offset).
Basically, if I have revenue in Week 1 and it takes me 5 weeks to collect revenue, then I want to take that week 1 Revenue and place it in Week 5 (different row of course).

But, there may be some weeks where I can collect two different prior weeks revenue.

I think this is a sum offset formula but I'm not sure.

See attached as my explanation may not be so clear.

Thanks!

[José Augusto]

Hi
Use this in H6

Please Login or Register  to view this content.

copy to right
Regards

[idontexcelinexcel]

Hello Jose, thank you so much. The formula worked. However, when I copy it all the way across it doesn't work. (My original spreadsheet goes out for 2 years) but I only attached a few months as an example.
how can I modify your formula so it can go out to two years.
Also, can you explain the 7 in the formula? I don't understand that part of it.

Thanks again!

[José Augusto]

Sorry for the delay.
The value 7 refers to the number of days of the week
I need more data to see what the problem copying forward
Regards

[FlameRetired]

See if this works with your extended data. In H6 and copied across

Please Login or Register  to view this content.

[FlameRetired]

Or this.

Please Login or Register  to view this content.

I am confused about what you want to happen at week 3/7/14.

[FlameRetired]

I extended some forced data and used this.

Please Login or Register  to view this content.

Please see attached.