Even Allocation to Buckets

[marco13187]

Hello, I am hoping someone can help me with the formula on this problem.

If i have three sets of contracts, I need the value of the contract, evenly disbursed along the remaining months starting from the month it starts.


ABC | $1,000| 04/01/2016 | All months from April to December would be $111.11
DEF | $2,000| 02/04/2016 | All months from February to December would be $200
GHI | $5,000| 09/01/2016 | All months from September to December would be $1250

[BlindAlley]

The attached should work for you.

If you change the formula cell references to match your worksheet pay attention to the start & end dates and where the dollar signs are.

[FlameRetired]

This formula in D2 filled down and across.

Please Login or Register  to view this content.

BTW I get 181.82/month for DEF.

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1				Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	ABC	$1,000	4/1/2016				$111.11	$111.11	$111.11	$111.11	$111.11	$111.11	$111.11	$111.11	$111.11
3	DEF	$2,000	2/4/2016		$181.82	$181.82	$181.82	$181.82	$181.82	$181.82	$181.82	$181.82	$181.82	$181.82	$181.82
4	GHI	$5,000	9/1/2016									$1,250.00	$1,250.00	$1,250.00	$1,250.00

[BlindAlley]

Slight change to the formula

[marco13187]

Hi Dave,

Thanks for all the help! Lets say i added a January through December for 2015. How would the formula change if my data contained a date of 12/05/2015. how would i get it to go in the December 2015 bucket and not the December 2016 bucket?

[JohnTopley]

Try

=IF(EOMONTH($C2,-1)+1<=D$1,$B2/((12-MONTH($C2))+1),"")