Formula to Spread Costs Between Months and Years

[Cardan]

Hello,
I have an issue I think I may be looking at incorrectly and can't seem to find a simple solution. See attached. (I added in some arrows for the first month so it is easier to follow the logic)

I am working with monthly revenues. Revenues are earned in a specific month but only a % is received in that month and over the next 4 months. I need a formula that will spread these revenues earned in 1 month over the next 4 months.

Currently, I have a complex Index/Match formula which works until September. September, October, November, and December do not work correctly because once the months roll over from month 12 (December) back down to month 1 (January), my formula cannot recognize the range.

Essentially, I need the formula to return the correct amount that is being paid, even across different years.

I don't know if my formula can be modified or a new formula would be more efficient. Any feedback would be helpful. Thank you

(This post originated from Commercial Services, which I just requested be removed from that forum)

[Ace_XL]

Try this..

In Q21
=IFERROR(INDEX($Q$14:Q$18,MOD(COLUMN(A$1)+1-ROW($A1),12),IF(ROW($A1)>=9,0,COLUMN(A$1)-MOD(COLUMN(A$1),12))+ROW($A1)),0)

Copy down and across

[Cardan]

Wow! Thank you!
This formula is a thing of beauty. I don't think I have ever used INDEX without MATCH. I am going to study this. I am sure I will need it in the future. Thank you!

[arlu1201]

Cardan,

Its against the forum rules to post in both the paid forum and the free forum at the same time.

Please keep in mind for future threads.