Monthly Spreading remaining values between two dates

[tamill35]

The formula works good for linear spreading values in each month between two dates =IF(AX$2<$AE3,0,IF(AX$2>$AF3,$AU3,((AX$2-MAX($AE3,$AW$1)+1)/$AV3)*$AU3)) where AX2 is monthly cut off date, AE is start date, A4 is End date, AU is value to be spread, AW1 is current date after which remaining values are to be distributed.

My questions are:-
1) This formula gives linear spreading for each month. Is there a way to spread non linear such as Back loaded distribution eg 3.5% for first 10 steps, then 6.5% for remaining 10 steps
2) I tried to summarise a range by the formula =SUMPRODUCT(IF(AX$2<($AE3:$AE384),0,IF(AX$2>($AF3:$AF384),($AU3:$AU384),((AX$2-MAX(($AE3:$AE384),$AW$1)+1))/($AV3:$AV384))*($AU3:$AU384))) But the formula is not giving the correct value. I dont understand what mistake I have done.

Can somebody help.

Thanks,
Selvam

 

[Dave Patton]

T202005c.xlsm
	A	B	C	D	E	F	G	H	I	J
1								Distribute to months
2	Months in Contract	Month Start	Amount	Jan	Feb	Mar	Apr	May	Jun	Jul
3	20	2	3,000.00		105.00	105.00	105.00	105.00	105.00	105.00
4	6	4	24,000.00				2,800.00	2,800.00	2,800.00	5,200.00
5
1d

Cell Formulas
Range	Formula
D3:J4	D3	=IF((COLUMNS($D:D))<$B3,"",IF((COLUMNS($D:D)-$B3)<$A3*0.5,$C3*0.35/($A3/2),IF((COLUMNS($D:D)-$B3)<$A3,$C3*0.65/($A3/2),"")))