How to do cumulative addition based on a duration?

[Requnix]

So, we have sales values that present a "perm fee" but we want to tier it out over 6 months. The challenge is integrating this into a row-based layout and taking each month's value (and somehow remembering it) as we progress down each row yet only divvying it out for 6 months. For example:

Month | Perm Fee | Payout
1 | $3000 | 500
2 | $3000 | 1000
3 | $0 | 1000
4 | $0 | 1000
5 | $3000 | 1500
6 | $0 | 1500
7 | $0 | 1000
8 | $0 | 500
9 | $0 | 500
10| $6000 | 1500

The trick is to know when the $500 (1/6 of 3k) expires from month 1. Any way to put that into a row-based formula?

[shg]

For example, ...?

[Requnix]

The example is above; manually entered. I need a formula that accomplishes the same thing.

[shg]

So the first row is input and everything below is calculated from that? Color me clueless.

[Requnix]

The payout is based on the Month's perm fee, but is paid out over 6 months (1/6th each month). So I need a formula that can take a perm fee and pay it out monthly over 6 months while taking into account additional cumulative payouts.

[shg]

Right. And I asked for an example of expected results.

[Requnix]

It's all in the initial post. The Payout is the column that needs a custom function/definition - 1/6th of the perm fee computed for 6 months, taking into account additional perm fees. What I put in the payout was a manual entry; I need it automatically filled.

[shg]

So this is the example, and columns A and B are input, and column C is supposed to be the calculated result?

	A	B	C
1	Month	Perm Fee	Payout
2	1	$3,000	500
3	2	$3,000	1000
4	3	$0	1000
5	4	$0	1000
6	5	$3,000	1500
7	6	$0	1500
8	7	$0	1000
9	8	$0	500
10	9	$0	500
11	10	$6,000	1500

[Requnix]

Correct; Column C needs to be 1/6th of each month's perm fee, but paid out over 6 months. Why I don't know how to do is compute the 6-month payout in a single row-based formula (if it's even possible) without creating another tracking grid. So Month 1 pays out 500 for 6 months, month 2 pays 500 for 6 months, month 6 pays 500 for 6 months, etc. And it's cumulative in the payout.

[shg]

One way, not pretty:

	A	B	C	D
1		DoNotUse
2		DoNotUse
3		DoNotUse
4		DoNotUse
5	Month	Perm Fee	Payout
6	1	$ 3,000	$ 500	C6: =SUM(B1:B6)/6
7	2	$ 3,000	$ 1,000
8	3	$ 0	$ 1,000
9	4	$ 0	$ 1,000
10	5	$ 3,000	$ 1,500
11	6	$ 0	$ 1,500
12	7	$ 0	$ 1,000
13	8	$ 0	$ 500
14	9	$ 0	$ 500
15	10	$ 6,000	$ 1,500

[Requnix]

The problem is that doesn't take into account only a 6-month dispersion for a specific row. That's the conundrum I'm dealing with. I might just have to have a second reference chart and use that.

[shg]

?

It generates the same results as your example, no?

[Ochimus]

Requnix,

No need for a second chart, assuming your data is in month order, as in your example.

As shown on the attached example, for the first six months (rows 2 - 7) the formula is =SUM(B$2:B2)/6 carried down. It adds a row automatically so by your sixth month (row seven) it is =SUM(B$2:B7)/6

From that point onward, you just need to "lose" the "seven months before" value, so the formula becomes =SUM(B3:B8)/6, copied down. So whatever row you are on, you are always summing only the previous six values (and in your case dividing them by six).

Ochimus

[Requnix]

Thank you Ochimus; I figured I could "hardcode" it in the Sum reference, but I was hoping to get a sort of IF based auto-adjuster that could (for example) go: SUM(Month-6 but not less than Row 15:Y). That way I wouldn't have to hardcode the row line (e.g. I could hardcode just the origin - starting line). Is there a way to do this properly?

[Ochimus]

Requnix,

Believe the attached does what you want with one formula copied down from whichever row starts the data,

It assumes: Month is a NUMBER in Col A, and not text, Capital is Col B, Payout is COl C,

This formula in C2 and copied down:

=IF(COUNT(A$2:A2)<=6,SUM(B$2:B2)/6,SUM(OFFSET(A$2,COUNTA(A$2:A2)-6,1,6))/6)

If the payment starts in row 2, and the current row is less than row 7, add the payments from row 2 as a "fixed" start down to the current row, and divide by six.
Otherwise class the current row as the "last" row, go back up five rows, add the six together and divide the total by six.

COl F - I show how it works if you start on a different row:

=IF(COUNT(F$4:F4)<=6,SUM(G$4:G4)/6,SUM(OFFSET(F$4,COUNTA(F$4:F4)-5,1,6))/6)

Hope that sorts it for you.

Ochimus

[Requnix]

Yup. Got it taken care of. Thank you to everyone for your feedback!